|
|
Posted by Uri Dimant on 01/11/07 08:22
Hi
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
<wizofaus@hotmail.com> wrote in message
news:1168494297.719888.324130@77g2000hsv.googlegroups.com...
>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
>
Navigation:
[Reply to this message]
|