Posted by swaroop.atre on 01/11/07 20:20

be EXTREMELY wary when using DBCC DROPCLEANBUFFERS and DBCC
FREEPROCCACHE on a production machine. They clear all cached SPs,
queries and plans. The instance is bound to run under extreme stress
for a considerable amount of time.





wizof...@hotmail.com wrote:
> 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

[Back to original message]