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?
-
I can't think of a reason you would want to do this and I've never seen it done either. What are you trying to fix?– jvilaltaCommented Dec 6, 2009 at 23:17
-
10It could be useful for measuring the average time taken to execute a query when the cache cannot be used.– Mark ByersCommented Dec 6, 2009 at 23:18
-
13@Jvilata -> you would do this when u wish to fix up the raw performance for a stored procedure or query. The slowest time a query/sp is ran, is when it's first compiled because it's not cached ... and that's excluding whatever the query is suppose to do/return back. So if u can fix the performance of a query/sp when it hasn't been cached, then you know the worst case scenario for that query (more or less).– Pure.KromeCommented Dec 6, 2009 at 23:26
-
2Reading below, seems like the answer is "no, there is not a way to pass a query to SQL Server and tell it not to use the cache when executing that query." Is that right?– QuentinCommented Jun 7, 2016 at 18:28
Add a comment
|
4 Answers
If you want to force a query to not use the data cache, the best approach is to clear the cache before you run the query:
CHECKPOINT
DBCC DROPCLEANBUFFERS
Note that forcing a recompile will have no effect on the query's use of the data cache.
One reason you can't just mark an individual query to avoid the cache is the cache use is an integral part of executing the query. If the data is already in cache, then what would SQL Server do with the data if it was read a second time? Not to mention sync issues, etc.
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.
answered Dec 6, 2009 at 23:33
-
3You can remove an individual plan by using the following SQL. DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]– AfrozCommented Apr 21, 2013 at 22:18
Another more localized way to not use the MS-SQL Server Cache is to use the OPTION(RECOMPILE) keyword at the end of your statement.
E.g.
SELECT Columnname
FROM TableName
OPTION(RECOMPILE)
For more information about this and other similar query-cache clues to help identify problems with a query, Pinal Dave (no affiliation) has some helpful info about this.
answered Feb 11, 2021 at 3:10
use
WITH RECOMPILE
answered Dec 6, 2009 at 23:19
