Database tunning实际问题 - 86秒的查询

flyspirit

刚说完Database tunning， 就碰到个实际性能问题， 我们系统中一个查询耗时86秒， 这是一个web app中用到的查询， 这个Response Time是不能被接受的， 头疼在这个问题不是每次都出现。Developers测试时2秒内完成， 但Marketing team测试就是这么久。

这86秒是用profiler抓出来的， 这是用的是.net sqlClient Data Provider.
同样的sql statement用SSMS运行就只花2秒
不像是lock的问题， 没有什么对表的写操作。

这种情况， 足迹各位高手有没有什么idea？ 会是什么原因造成的。

北风

看一下execution plan先
有其他什么同时运行吗？

flyspirit

由于是live system, 其他的数据库操作肯定有

teddymu

maybe re-build the index or re-create the stats

乱码

独立测试没问题，上prod有问题，有相当一部分是locking的问题，你怎么rule out lock的？

HappyRain

关键是定位，有一种可能是查询本身没有问题，问题出在调用查询的代码哪部分。也就是为什么Marketing team在做集成测试时，点击按钮后，响应时间太长。可以把那个查询单独抓出来，在Marketing team的机器上运行，如果时间正常那就是application的调用查询的代码占用时间长。可以把时间点写在LOG里，逐步缩小范围。GOOD LUCK!

典

Run a new Trace for 2~4 hours
Save the result to a table
From there you may get some useful information

flyspirit

原帖由 乱码 于 2011-1-28 10:17 发表
独立测试没问题，上prod有问题，有相当一部分是locking的问题，你怎么rule out lock的？

还没有完全rule out。只是和查询相关的table被modify的可能性很小。

混不到坑的萝卜

如果你的app每次访问数据库都打开关闭connection也可能导致很长时间。检查你的connection pool设定。尽量使用pooled connections，如果大量connection同时被占用，需要考虑从新设计，尽量快地释放connection。没有write操作可以考虑尽量用sqlDataReader而不是SqlDataAdapter etc etc

flyspirit

原帖由 HappyRain 于 2011-1-28 10:19 发表
关键是定位，有一种可能是查询本身没有问题，问题出在调用查询的代码哪部分。也就是为什么Marketing team在做集成测试时，点击按钮后，响应时间太长。可以把那个查询单独抓出来，在Marketing team的机器上运行，如果时间正常那就是application的调用查询的代码占用时间长。可以把时间点写在LOG里，逐步缩小范围。GOOD LUCK!

developer team也是做集成测试， 响应很快。 不过你的建议也对， 要去他们机器上看看。

北风

“Developers测试时2秒内完成”
在dev还是live?

典

What kinds of SQL version?
Are you using stored procs?

北风

我觉得profile得到的86秒，所以应该不是app server的问题
问题你还是在你的database方面
1. 确定没有lock
2. execution plan

乱码

如果不是很经常的应用，你找一个不忙的时段，在prod上作一下profiler,里马能定位statement级别的问题，原来我经常这么干，到这个公司就严格很多，连prod　pwd都没有　

flyspirit

原帖由 北风 于 2011-1-28 10:28 发表
“Developers测试时2秒内完成”
在dev还是live?

都是live system.

混不到坑的萝卜

Profile上看Query Duration? 还是看End Time - Start Time?

flyspirit

原帖由 典 于 2011-1-28 10:29 发表
What kinds of SQL version?
Are you using stored procs?

SQL Server 2005

We using store procedure, but the statement is created on the fly.

flyspirit

原帖由 北风 于 2011-1-28 10:31 发表
我觉得profile得到的86秒，所以应该不是app server的问题
问题你还是在你的database方面
1. 确定没有lock
2. execution plan

请教用什么手段可以排除lock？

混不到坑的萝卜

原帖由 flyspirit 于 2011-1-28 09:42 发表
We using store procedure, but the statement is created on the fly.

你是说Stored proc里面用了dynamic sql吧？

flyspirit

原帖由 混不到坑的萝卜 于 2011-1-28 10:41 发表
Profile上看Query Duration? 还是看End Time - Start Time?

profiler 里面的duration.

flyspirit

原帖由 混不到坑的萝卜 于 2011-1-28 10:43 发表

你是说Stored proc里面用了dynamic sql吧？

对的， Something like

exec sp_executesql N'SELECT pd.ProductID AS ProductID124_0_, ....................

exec sq_executesql 里面都是dynamic sql.

flyspirit

原帖由 乱码 于 2011-1-28 10:32 发表
如果不是很经常的应用，你找一个不忙的时段，在prod上作一下profiler,里马能定位statement级别的问题，原来我经常这么干，到这个公司就严格很多，连prod　pwd都没有　

感觉被捆绑住了吧

典

One thing you can try with the stored proc
Change the parametter
For example
Proc abc(p1 as varchar)

Change to:
Proc abc(temp_p1 as varchar)
declare p1 as varchar
set p1=temp_p1

-------

It's strange and hard to say why. Last time we fixed a similar problem by doing that.

----

But you say it's dynamic, it means a lot of re-compiling??
You can try this: Put some hints to avoid re-compiling

[ 本帖最后由 典 于 2011-1-28 09:51 编辑 ]

乱码

for determining locking issue, check out following articles:

http://www.simple-talk.com/sql/s ... -with-sql-profiler/
http://www.simple-talk.com/sql/l ... rver-2005-profiler/
http://stackoverflow.com/questio ... est-environment-why
http://www.mssqltips.com/tip.asp?tip=1359

HappyRain

解决这种在开发环境和product环境运行时间长短不一致问题的关键是：找出两种环境的不同点。在这种情况下，我们通常会认为两个环境完全一样。实际上必定有不同点存在，否则也不会每次在develop环境下运行时间正常，而每次在product环境下运行时间就很长了。大体可以从软件环境和硬件（物理）环境两方面着手，比如网络、防火墙，系统服务等等，两种环境都完全一样吗？能不能找第3台机器再测一下？ 列出一个软件和硬件的CHECK LIST，细心一点，一定会找到的。

这还有一个链接，希望能帮到你。Checklist for Analyzing Slow-Running Queries http://msdn.microsoft.com/en-us/library/ms177500.aspx

[ 本帖最后由 HappyRain 于 2011-1-28 10:58 编辑 ]

典

About lock,
No writing dosn't mean No Locking,

混不到坑的萝卜

原帖由 典 于 2011-1-28 09:53 发表
No writing dosn't mean No Locking,

是呀，先用sp_who, sp_who2看看有没有哪些deadlock，缩小范围

flyspirit

原帖由 HappyRain 于 2011-1-28 10:53 发表
解决这种在开发环境和product环境运行时间长短不一致问题的关键是：找出两种环境的不同点。在这种情况下，我们通常会认为两个环境完全一样。实际上必定有不同点存在，否则也不会每次在develop环境下运行时间正常，而每次在pr ...

谢谢连接，不过都是live system下测得啦。

Dan.and.Andy

原帖由 flyspirit 于 2011-1-28 11:05 发表

谢谢连接，不过都是live system下测得啦。

你的live system指的什么?仅仅是database吗? developer直接用VS和marking team测IIS中host的app结果可能是不一样的.

如果一个是vs,一个是IIS, 加一个WITH RECOMPILE option 试一下.

FYI - http://aspadvice.com/blogs/ssmit ... agement-Studio.aspx

flyspirit

都是live, 从Web server到 Database server