I think a lot of developers know the following scenario:
- You have a query that is "slow", e.g. causes timeouts in your application.
- You copy the query into SSMS and execute it. You just want to "see it" yourself dying before you touch it. But it runs within a second on the same server...
- You try it a bit later and you are "lucky" this time, it is very slow in SSMS as well. Maybe it is time to tune the query and see how the response time is changing?
- But first you press that F5 again just to make sure that you saw it correctly. And you are out of luck, query returns immediately...
Of course this must be caused by the caching mechanism (plan, data) of the SQL server, which is a good thing in general. However, it is still a bit annoying if you are about to tune the query and you can never know if you can trust the execution time that you see.
The two “magic commands” that can help:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
If you run these commands before executing the query you will be able to reproduce the slow response time in a more reliable way.
Just don't forget that while this is a good thing for you in that moment it might cause a serious performance hit for all the others at the same time. Definitely don't do this on a production server! And make sure that you know what you are doing: please read the documentation of the commands and decide wisely whether you may do this in your concrete environment.
No comments:
Post a Comment