CODEWITHSUNDEEP
sqlsql-server-2008optimization
stas
stas

Reputation: 329

SQL Server Caching

I have a query which when run initially takes approximately nine seconds to return and on subsequent reruns about four seconds. After reading about this I believe this a manifestation of plan caching (though I could be wrong). I am trying to optimize this query and this effect is making that quite difficult.
Am I correct in my analysis of this being a plan caching issue or are there other options? If so, is there a way to disable this caching locally (ie: for one query, not for the server).

Finally, how does option(recompile) fit into this discussion if at all.

I'm using SQL server 2008 by the way,

Upvotes: 1

Views: 216

Answers (3)

PatFromCanada
PatFromCanada

Reputation: 2788

It is likely not plan caching but data caching that is the culprit. There are two ways around this. on a development server you can run DROPCLEANBUFFERS to clear the data cach. You should also be using the query plan and statistics to do the optimizing. In this case you can SET STATISTICS IO On and concentrate on logical reads rather than physical ones. If you run the query twice after clearing the data cache you will see the physical reads drop dramatically but the logical reads willstay the same.

`OPTION RECOMPILE`

Will only affect plan caching and will only make a difference in situations where the query plan itself will change due to passing in different parameters, for instance when the size of the result set changes dramatically.

To reduce logical reads? Speaking generally, indexes and covering indexes come to mind. If you look at the execution plan, SQL server might be suggesting indexes if it thinks they are needed. It will actually give you the appropriate create index syntax (though the new index does not always help). Also in the Actual Execution Plan if the number of rows returned is drastically different than the number of rows expected you may want to look at your statistics. SQL server maintains stats on indexed columns but if auto stats is not on then if the where statment includes an unindexed column then you might want to generate stats for that (or index it or turn auto stats on).

Upvotes: 1

Neil
Neil

Reputation: 831

You can clear the plan cache using 'FREEPROCCACHE'

See this MSDN page for and example of how to clear a specific plan from the cache.

That said unless it is a very long SQL statement, compiling the plan will not take 4 seconds so it is most likely data caching that is the causing your query to run faster after the first execution.

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

There are many factors at play here, but one way to get around effects of plan/data caching, other load on db, etc., when doing query optimization is to focus on logical reads. This is how many reads would need to be done - whether they come from the cache or not for a given run is not relevant.

Example:

USE AdventureWorks2012;
GO       

SET STATISTICS IO ON;
GO
SELECT * 
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
GO
SET STATISTICS IO OFF;
GO

Results:

Table 'ProductCostHistory'. Scan count 1, logical reads 5, physical 
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, 
lob read-ahead reads 0.

When tuning the query, focus on getting the logical reads as low as you can.

Upvotes: 0

Related Questions