奇怪的慢查询问题（再次） [英] Bizarre slow query problem (again)
问题描述
我之前发布了一个问题，似乎更改了BY字样的
情况。在SELECT查询中导致它运行得更快
更快。
现在我再次遇到同样的事情，基本上几乎任何变化我
如何执行查询（以便它仍然执行相同的
函数）导致性能从令人沮丧的7或8秒跳转
即时。这是一个非常简单的表格查询：
SELECT Min（MyValue）FROM MyTable WHERE MyKey = @ 0
正在运行很久以前，当它突然开始时，b / b慢慢地无助地跑着。如果将查询中的任何内容更改为
小写（或Min为大写），则它再次运行正常。上次
有人建议缓解一个糟糕的计划，经过一次研究后我发现命令DBCC DROPCLEANBUFFERS和DBCC
FREEPROCCACHE。果然，在运行这些之后，查询开始了
再次正常运行。问题是
a）为什么会发生这种情况？这是我的代码或SQL服务器中的错误吗？
b）是否值得检测并自动修复？即，我应该将
中的一些代码放入通知中，查询运行得太慢，
然后运行DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE"？或者那个
会导致其他问题吗？
谢谢
I previously posted about a problem where it seemed that changing the
case of the word "BY" in a SELECT query was causing it to run much much
faster.
Now I''ve hit the same thing again, where basically almost any change I
make to how the query is executed (so that it still performs the same
function) causes the performance to jump from a dismal 7 or 8 seconds
to instantaneous. It''s a very simple query of the form:
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0
which was running fine until a moment ago, when it suddently started
running hopelessly slowly. If change anything in the query to
lowercase (or the Min to uppercase), it runs fine again. Last time
someone suggested something about a bad plan being cached, and after a
bit of research I found the commands DBCC DROPCLEANBUFFERS and DBCC
FREEPROCCACHE. Sure enough, after running these, the query started
running fine again. The question is
a) why is this happening? Is it a bug in my code, or in SQL server?
b) is it worth detecting it and fixing it automatically? I.e, should I
put some code in that notices that a query is running far too slowly,
then runs "DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE"? Or will that
cause other problems?
Thanks
推荐答案
您好
http： //blogs.msdn.com/khen1234/archi...02/424228.aspx
< wi ****** @ hotmail.comwrote in message
新闻：11 ********************** @ 77g2000hsv.googlegro ups.com ...
Hi
http://blogs.msdn.com/khen1234/archi...02/424228.aspx
<wi******@hotmail.comwrote in message
news:11**********************@77g2000hsv.googlegro ups.com...
>我之前发过一个问题，似乎更改了单词BY的
情况。在SELECT查询中导致它运行得更快
更快。
现在我再次遇到同样的事情，基本上几乎任何变化我
如何执行查询（以便它仍然执行相同的
函数）导致性能从令人沮丧的7或8秒跳转
即时。这是一个非常简单的表格查询：
SELECT Min（MyValue）FROM MyTable WHERE MyKey = @ 0
正在运行很久以前，当它突然开始时，b / b慢慢地无助地跑着。如果将查询中的任何内容更改为
小写（或Min为大写），则它再次运行正常。上次
有人建议缓解一个糟糕的计划，经过一次研究后我发现命令DBCC DROPCLEANBUFFERS和DBCC
FREEPROCCACHE。果然，在运行这些之后，查询开始了
再次正常运行。问题是
a）为什么会发生这种情况？这是我的代码或SQL服务器中的错误吗？
b）是否值得检测并自动修复？即，我应该将
中的一些代码放入通知中，查询运行得太慢，
然后运行DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE"？或者那个
会导致其他问题吗？
谢谢
>I previously posted about a problem where it seemed that changing the
case of the word "BY" in a SELECT query was causing it to run much much
faster.
Now I''ve hit the same thing again, where basically almost any change I
make to how the query is executed (so that it still performs the same
function) causes the performance to jump from a dismal 7 or 8 seconds
to instantaneous. It''s a very simple query of the form:
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0
which was running fine until a moment ago, when it suddently started
running hopelessly slowly. If change anything in the query to
lowercase (or the Min to uppercase), it runs fine again. Last time
someone suggested something about a bad plan being cached, and after a
bit of research I found the commands DBCC DROPCLEANBUFFERS and DBCC
FREEPROCCACHE. Sure enough, after running these, the query started
running fine again. The question is
a) why is this happening? Is it a bug in my code, or in SQL server?
b) is it worth detecting it and fixing it automatically? I.e, should I
put some code in that notices that a query is running far too slowly,
then runs "DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE"? Or will that
cause other problems?
Thanks
Uri Dimant写道：
Uri Dimant wrote:
嗨
http://blogs.msdn.com/khen1234/archi...02/424228.aspx
谢谢你......令人惊讶的是，它变成了我的问题，尽管我正在使用ad-hoc查询而不是存储过程。我b $ b做了一些更多的测试，结果是因为它是两次执行相同查询的
，第一次是非典型的
参数值，以及第二次使用更典型的参数值，
查询运行得如此之慢。也就是说，执行
SELECT Min（MyValue）FROM MyTable WHERE MyKey = @ 0，@ 0 = 999
后跟
SELECT Min（MyValue）FROM MyTable WHERE MyKey = @ 0，@ 0 = 123
导致第二个查询运行缓慢，因为在第一个
情况下表中只有极少数行有MyKey = 999而几乎每行都有两个MyKey = 123.在做了DBCC FREEPROCCACHE和
后交换查询他们都运行良好。
最后我最终为这个案例取消了参数查询，
但现在我很担心 - 我怎么能确定我的其他疑问不会因为同样的问题而遭受损失？我是否应该因为
这种可能性而不使用参数？
Thanks for that...amazingly enough it turned that that was exactly my
problem, although I''m using ad-hoc queries rather than stored procs. I
did some more testing, and it turned out that it was because it was
executing the same query twice, the first time with an atypical
parameter value, and the second time with a more typical one, that the
query was running so slowly. That is, executing
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0, with @0 = 999
followed by
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0, with @0 = 123
Caused the second query to run absurdly slowly, because in the first
case only very few rows in the table had MyKey = 999 whereas almost
every row had MyKey = 123. After doing a DBCC FREEPROCCACHE and
swapping the queries around, they both ran fine.
In the end I ended up de-parameterizing the query just for this case,
but now I''m worried - how can I be sure that my other queries won''t
suffer from the same problem? Should I never use parameters because of
this possibility?
最后我最终取消参数化查询这种情况下，
In the end I ended up de-parameterizing the query just for this case,
但现在我很担心 - 我怎么能确定我的其他查询不会因为
遭受一样的问题？我是否应该从不使用参数因为
这种可能性？
but now I''m worried - how can I be sure that my other queries won''t
suffer from the same problem? Should I never use parameters because of
this possibility?
使用参数的能力非常强大，不要害怕使用参数< br $> b $ b，只需仔细测试一下
< wi ****** @ hotmail.comwrote in message
新闻：11 ********************** @ i56g2000hsf.googlegr oups.com ...
An ability using parameters is very powerful , don''t afraid using parameters
, just test it carefuly
<wi******@hotmail.comwrote in message
news:11**********************@i56g2000hsf.googlegr oups.com...
Uri Dimant写道：
Uri Dimant wrote:
>嗨
http://blogs.msdn.com/khen1234/archi...02/424228.aspx
多亏了......令人惊讶的是，它变成了我的
问题，尽管我正在使用ad-hoc查询而不是存储过程。我b $ b做了一些更多的测试，结果是因为它是两次执行相同查询的
，第一次是非典型的
参数值，以及第二次使用更典型的参数值，
查询运行得如此之慢。也就是说，执行
SELECT Min（MyValue）FROM MyTable WHERE MyKey = @ 0，@ 0 = 999
后跟
SELECT Min（MyValue）FROM MyTable WHERE MyKey = @ 0，@ 0 = 123
导致第二个查询运行缓慢，因为在第一个
情况下表中只有极少数行有MyKey = 999而几乎每行都有两个MyKey = 123.在做了DBCC FREEPROCCACHE和
后交换查询他们都运行良好。
最后我最终为这个案例取消了参数查询，
但现在我很担心 - 我怎么能确定我的其他疑问不会因为同样的问题而遭受损失？我是否应该从不使用参数因为
这种可能性？
Thanks for that...amazingly enough it turned that that was exactly my
problem, although I''m using ad-hoc queries rather than stored procs. I
did some more testing, and it turned out that it was because it was
executing the same query twice, the first time with an atypical
parameter value, and the second time with a more typical one, that the
query was running so slowly. That is, executing
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0, with @0 = 999
followed by
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0, with @0 = 123
Caused the second query to run absurdly slowly, because in the first
case only very few rows in the table had MyKey = 999 whereas almost
every row had MyKey = 123. After doing a DBCC FREEPROCCACHE and
swapping the queries around, they both ran fine.
In the end I ended up de-parameterizing the query just for this case,
but now I''m worried - how can I be sure that my other queries won''t
suffer from the same problem? Should I never use parameters because of
this possibility?
这篇关于奇怪的慢查询问题（再次）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
