如何清除SQL Server查询缓存?

I've got a simple query running against SQL Server 2005

我有一个针对SQL Server 2005的简单查询。

SELECT * 
FROM Table 
WHERE Col = 'someval'

The first time I execute the query can take > 15 secs. Subsequent executes are back in < 1 sec.

第一次执行查询时可以使用>15秒。后续执行将在< 1秒内返回。

How can I get SQL Server 2005 not to use any cached results? I've tried running

如何使SQL Server 2005不使用任何缓存的结果?我试着运行

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

But this seems to have no effect on the query speed (still < 1 sec).

但这似乎对查询速度没有影响(仍然< 1秒)。

5 个解决方案

#1

Here is some good explaination. check out it.

这里有一些很好的解释。看看它。

http://www.mssqltips.com/tip.asp?tip=1360

http://www.mssqltips.com/tip.asp?tip=1360

CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; 
GO

From the linked article:

从链接的文章:

If all of the performance testing is conducted in SQL Server the best approach may be to issue a CHECKPOINT and then issue the DBCC DROPCLEANBUFFERS command. Although the CHECKPOINT process is an automatic internal system process in SQL Server and occurs on a regular basis, it is important to issue this command to write all of the dirty pages for the current database to disk and clean the buffers. Then the DBCC DROPCLEANBUFFERS command can be executed to remove all buffers from the buffer pool.

如果所有的性能测试都在SQL Server中进行，那么最好的方法可能是发出一个检查点，然后发出DBCC DROPCLEANBUFFERS命令。尽管检查点过程是SQL Server中的一个自动内部系统进程，并且经常发生，但是发出这个命令将当前数据库的所有脏页写到磁盘并清除缓冲区是很重要的。然后可以执行DBCC DROPCLEANBUFFERS buffer命令，从缓冲池中删除所有缓冲区。

#2

While the question is just a bit old, this might still help. I'm running into similar issues and using the option below has helped me. Not sure if this is a permanent solution, but it's fixing it for now.

虽然这个问题有点过时，但这可能仍然有用。我遇到了类似的问题，使用下面的选项帮助了我。不确定这是否是一个永久性的解决方案，但它现在正在修复它。

OPTION (OPTIMIZE FOR UNKNOWN)

Then your query will be like this

那么您的查询将是这样的

select * from Table where Col = 'someval' OPTION (OPTIMIZE FOR UNKNOWN)

#3

EXEC sys.sp_configure N'max server memory (MB)', N'2147483646'
GO
RECONFIGURE WITH OVERRIDE
GO

What value you specify for the server memory is not important, as long as it differs from the current one.

您为服务器内存指定的值并不重要，只要它与当前的内存不同。

Btw, the thing that causes the speedup is not the query cache, but the data cache.

顺便说一句，导致加速的不是查询缓存，而是数据缓存。

#4

Eight different ways to clear the plan cache

1. Remove all elements from the plan cache for the entire instance

DBCC FREEPROCCACHE;

Use this to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.

使用此命令仔细清除计划缓存。释放计划缓存会导致重新编译存储过程，而不是从缓存中重用。这可能导致查询性能的突然、临时下降。

2. Flush the plan cache for the entire instance and suppress the regular completion message

"DBCC execution completed. If DBCC printed error messages, contact your system administrator."

“DBCC执行完成。如果DBCC打印了错误消息，请与系统管理员联系。

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

3. Flush the ad hoc and prepared plan cache for the entire instance

DBCC FREESYSTEMCACHE ('SQL Plans');

4. Flush the ad hoc and prepared plan cache for one resource pool

DBCC FREESYSTEMCACHE ('SQL Plans', 'LimitedIOPool');

5. Flush the entire plan cache for one resource pool

DBCC FREEPROCCACHE ('LimitedIOPool');

6. Remove all elements from the plan cache for one database (does not work in SQL Azure)

-- Get DBID from one database name first
DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid] 
                FROM master.dbo.sysdatabases 
                WHERE name = N'AdventureWorks2014');

DBCC FLUSHPROCINDB (@intDBID);

7. Clear plan cache for the current database

USE AdventureWorks2014;
GO
-- New in SQL Server 2016 and SQL Azure
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

8. Remove one query plan from the cache

USE AdventureWorks2014;
GO

-- Run a stored procedure or query
EXEC dbo.uspGetEmployeeManagers 9;

-- Find the plan handle for that query 
-- OPTION (RECOMPILE) keeps this query from going into the plan cache
SELECT cp.plan_handle, cp.objtype, cp.usecounts, 
DB_NAME(st.dbid) AS [DatabaseName]
FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st 
WHERE OBJECT_NAME (st.objectid)
LIKE N'%uspGetEmployeeManagers%' OPTION (RECOMPILE); 

-- Remove the specific query plan from the cache using the plan handle from the above query 
DBCC FREEPROCCACHE (0x050011007A2CC30E204991F30200000001000000000000000000000000000000000000000000000000000000);

Source 1 2 3

源1 2 3

#5

Note that neither DBCC DROPCLEANBUFFERS; nor DBCC FREEPROCCACHE; is supported in SQL Azure / SQL Data Warehouse.

注意，DBCC dropcleanbuffer都不是;也不是DBCC FREEPROCCACHE;支持SQL Azure / SQL数据仓库。

However, if you need to reset the plan cache in SQL Azure, you can alter one of the tables in the query (for instance, just add then remove a column), this will have the side-effect of removing the plan from the cache.

但是，如果您需要在SQL Azure中重置计划缓存，您可以更改查询中的一个表(例如，添加然后删除一个列)，这将产生从缓存中删除计划的副作用。

I personally do this as a way of testing query performance without having to deal with cached plans.

我个人将此作为一种测试查询性能的方法，而不必处理缓存的计划。

More details about SQL Azure Procedure Cache here

更多关于SQL Azure过程缓存的细节在这里