This article will completely guide you to clear the sql query cache in sql server. Consider you are running the same query one or more times in a row, the execution time may differ time to time. If your are only user on the database, then it is because of build in query cache.
DBCC DROPCLEANBUFFERS: Use this command to remove all the data from SQL Server’s data cache (buffer) between performance tests to ensure fair testing. Keep in mind that this command only removes clean buffers, not dirty buffers. Because of this, before running the DBCC DROPCLEANBUFFERS command, you may first want to run the CHECKPOINT command first. Running CHECKPOINT will write all dirty buffers to disk. And then when you run DBCC DROPCLEANBUFFERS, you can be assured that all data buffers are cleaned out, not just the clean ones.
DBCC FREEPROCCACHE: Used to clear out the stored procedure cache for all SQL Server databases. You may want to use this command before testing to ensure that previous stored procedure plans won’t negatively affect testing results.
The cache can be cleared with the following script:
GO
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
Related Posts
- Issue on Renaming SQL Server machine name
- Consistency Errors in MS SQL Server 2000
- Ad hoc Reporting using SQL Server Reporting Services 2005
- SQL Server 2000 Maintenance Jobs Failed with Error 22029
- Delete All tables in a SQL Server Database
Tags: CHECKPOINT, clear cache, DBCC, DROPCLEANBUFFERS, FREEPROCCACHE, Sql Server DBCC commands
[...] Clearing the query cache in sql server is pretty simple. It helps you at the time of checking the query performance in sql server. I think the following article will help you to clear query cache in SQL Server. How to Clear Query Cache in SQL Server [...]