SQL Server has an advantage of caching results of executed queries, so when an expensive query is executed, the server only takes the heaviest hit during the first execution, any subsequent execution would take significantly less hit.

Such approach, however, could make SQL tuning job difficult, because it is hard to know if the tuning actually works faster or it is the caching that does the magic. However, with caching is disabled, the tuning may become apparent. This could be achieved with the following snippet:

DBCC DROPCLEANBUFFERS; 
GO

Note that CHECKPOINT could be “harmful” if abused in Production environment, so the above is only recommended in development environment.

Advertisements