How do you fix queries that only run slow until they're cached.

Question

I have some queries that are causing timeouts in our live environment. (>30 seconds)

If I run profiler and grab the exact SQL being run and run it from Management Studio then they take a long time to run the first time and then drop to a few hundred miliseconds each run after that.

This is obviously SQL caching the data and getting it all in memory.

I'm sure there are optimisations that can be made to the SQL that will make it run faster.

My question is, how can I "fix" these queries when the second time I run it the data has already been cached and is fast?

Answer 1

According to http://morten.lyhr.dk/2007/10/how-to-clear-sql-server-query-cache.html, you can run the following to clear the cache:

DBCC DROPCLEANBUFFERS   
DBCC FREEPROCCACHE

EDIT: I checked with the SQL Server documentation I have and this is at least true for SQL Server 2000.

Answer 2

Query optimisation is a large subject, there is no single answer to your question. The clues as to what to do are all in the query plan which should be the same regardless of whether the results are cached or not.

Look for the usual things such as table scans, indexes not being used when you expect them to be used, etc. etc. Ultimately you may have to revew your data model and perhaps implement a denormalisation strategy.

Answer 3

From MSDN:
"Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server."

Answer 4

Use can use

DBCC DROPCLEANBUFFERS   
DBCC FREEPROCCACHE

But only use this in your development environment whilst tuning the queries for deployment to a live server.

Answer 5

Hi,

May I suggest that you inspect the execution plan for the queries that are responsible for your poor performance issues.

You need to identify, within the execution plan, which steps have the highest cost and why. It could be that your queries are performing a table scan, or that an inappropriate index is being used for example.

There is a very detailed, free ebook available from the RedGate website that concentrates specifically on understanding the contents of execution plans.

https://www.red-gate.com/Dynamic/Downloads/DownloadForm.aspx?download=ebook1

You may find that there is a particular execution plan that you would like to be used for your query. You can force which execution plan is used for a query in SQL Server using query hints. This is quite an advanced concept however and should be used with discretion. See the following Microsoft White Paper for more details.

http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx

I would also not recommend that you clear the procedure cache on your production environment as this will be detrimental to the performance of all other queries on the platform that are not currently experience performance issues.

If you are executing a stored procedure for example you can ensure that a new execution plan is calculated for each execution of the procedure by using the WITH RECOMPILE command.

For overall performance tuning information, there are some excellent resources over at Brent Ozar’s blog.

http://www.brentozar.com/sql-server-performance-tuning/

Hope this helps. Cheers.

Answer 6

I think people are running off in the wrong direction. If I understand, you want the performance to be good all the time? Are they not running fast the 2nd (and subsequent executions) and are slow the first time?

The DBCC commands above clear out the cache, causing WORSE performance.

What you want, I think, is to prime the pump and cache the data. You can do this with some startup procedures that execute the queries and load data into memory.

Memory is a finite resource, so you can't load all data, likely, into memory, but you can find a balance. Brent has some good references above to help learn what you can do here.