CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
pencilCake
pencilCake

Reputation: 53243

Can I enforce SQL Server explicitly NOT to CACHE my query result set?

Is there an explicit command or so to enforce SQL Server not to 'CACHE' my query results?

I am working on a performance issue and when a long query is finished on the 2nd call it takes too fast, in order to compare query performances when I make modifications, I want this behavior to not to take place.

Is this possible?

Upvotes: 2

Views: 307

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294237

You are doing it wrong. Use precise measurements instead: enable SET STATISTICS IO ON and SET STATISTICS TIME ON and then compare the queries. Look at the number of logical reads your queries have, this would be the driving factor to improve performance.

If you force a cold cache (which is possible) you are optimizing for the wrong case, since most times your production server will be warmed up and serving data form the buffer pool.

Also the fact that you are seeing significant IO driven by the query first time is a clear indication of table scans. Query rewrite may help, but your real problem much more likely to be is missing indexes and that's what you should focus on.

Upvotes: 3

Gibron
Gibron

Reputation: 1369

Are you sure that you are referring to the caching of your query result vs. the execution plan?

Electing not to cache execution plans can be done at an object level using:

WITH RECOMPILE

Worth checking out: The Use and Abuse of RECOMPILE in SQL Server (Video).

Upvotes: 1

Sam Grondahl
Sam Grondahl

Reputation: 2467

Yes. You will want to check this out: http://www.mssqltips.com/sqlservertip/1360/clearing-cache-for-sql-server-performance-testing/.

Upvotes: 2

Related Questions