Menu
Just a general question:
Is there a query/command I can pass to SQL Server not to use cache when executing a particularly query?
I am looking for a query/command that I can set rather than a configuration setting. Is there no need to do this?
276
If you want to disable the query cache code, set query_cache_size=0. By disabling the query cache code there is no noticeable overhead. (query cache can be excluded from code with help of configure option --without-query-cache)
The system automatically maintains a cache of prepared SQL statements (“queries”). This permits the re-execution of an SQL query without repeating the overhead of optimizing the query and developing a Query Plan. A cached query is created when certain SQL statements are prepared.
Cached queries provide the following benefits: Subsequent execution of frequently used queries is faster. More importantly, this performance boost is available automatically without having to code cumbersome stored procedures. Most relational database products recommend using only stored procedures for database access.
By cleaning the buffer pool before each test run SQL Server will have to re-read the data it needs from disk. To clean the buffer pool you execute the command: DBCC DROPCLEANBUFFERS. Next you should remove your execution plans from the procedure cache.
DBCC FREEPROCCACHE Will remove all cached procedures execution plans. This would cause all subsequent procedure calls to be recompiled.
Adding WITH RECOMPILE to a procedure definition would cause the procedure to be recompiled every time it was called.
I do not believe that (in SQL 2005 or earlier) there is any way to clear the procedrue cache of a single procedures execution plan, and I'd doubt you could do it in 2008 either.
165
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
