sql-server - SQL Server 中的启动或预热缓存

Question

当我测试查询时，我通常会将以下几行放在我正在测试的内容前面，以确保每次运行查询时都从相同的基线开始。

CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
EXEC sp_MyProc 12345

在我今天运行的存储过程中，我注意到当我用这些行运行它时，每次大约需要 18 分钟。当我关闭这些行时，只花了 3 秒。看到清除的缓存与已填充的缓存所造成的巨大差异，我决定添加以下内容，看看我是否可以在运行我的进程之前手动填充缓存，看看它会做什么表现。

CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
SELECT top 1 '1' from Table1
EXEC sp_MyProc 12345

正如您可能已经猜到的，sp_MyProc 大量使用了Table1。我惊讶地发现这样做使我的运行时间持续减少到大约 6 分钟。虽然它确实提高了性能，但看起来有点老套，我很好奇 SQL Server 中是否有内置的东西可以实现这一点。

• 这样做是为了改善您的查询性能闻所未闻的？
• 我的假设是否正确？我看到的改进时间是“Primed”缓存的结果吗？

如果我对缓存的理解有点偏差，请随时分享您认为可能有帮助的任何链接或信息。

更新:好吧，我很尴尬地说，我今天尝试重现这种行为，但未能成功。我与工作中的一些人进行了交谈，看起来他们昨天在数据库上所做的一些事情可能使我在 proc 之前的选择看起来像是在提高性能，但事实上并非如此。我仍然有兴趣听听是否有人知道是否可以通过“启动”缓存。

Answer 1

提供一个“答案”以尝试解决这个问题，因为这是我特别感兴趣的事情。

我遇到了this MSDN 文章介绍如何查看 SQL Server 缓存中的内容。那里有一个查询将显示对象缓存了多少数据页 - 我对其进行了调整以包含索引名称，如下所示:

SELECT count(*) AS cached_pages_count, obj.name, index_id, i.name AS IndexName
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_id, object_name(object_id) AS name 
            ,index_id ,allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_id, object_name(object_id) AS name   
            ,index_id, allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.partition_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
    LEFT JOIN sysindexes i ON obj.object_id = i.id AND obj.index_id = i.indid
WHERE database_id = db_id()
GROUP BY obj.name, index_id, i.name
ORDER BY cached_pages_count DESC;

如果您尝试以下步骤，您应该能够看到缓存的情况。在您的数据库中执行这些操作(而不是例如主数据库):

1) 检查点+清除缓存
2) 运行上面的查询，您应该可能会返回 1 条记录(对于 sysobjvalues)，但 Table1 没有任何结果
3) 现在运行 SELECT TOP 1 '1' FROM MyTable 语句
4) 重新运行上述查询并查看现在结果中显示的内容 - 您可能会看到显示缓存页面的 MyTable 记录 - 记下该数字

这应该告诉您初始 SELECT 发生的数据缓存级别。如果您再次重复该过程，但不是执行 SELECT TOP 语句，而是执行您的存储过程，然后查看运行时在缓存中最终有多少内容 - 也许比较这些结果将表明由存储过程完成的相对缓存量与存储过程调用相比，选择 TOP 1 - 该相对量可以表明性能改进。

这就是“大声思考”的东西。我没想到 TOP 1 会真正为存储过程调用显着地准备好缓存，但这就是我对这个问题感兴趣的原因!

我最初认为这更多地与其他因素有关(例如服务器/磁盘负载)。您可以在这 2 个场景之间交替进行 3 或 4 次迭代，一次又一次，以仔细检查 SELECT TOP 方法实际上是否始终更好(有助于最大程度地降低其成为一次性事件的风险)

希望这有助于/让事情顺利进行。

更新:
现在您知道不是 SELECT TOP 启动了缓存，启动缓存的一个好方法是 AdrianBanks 所说的。至少现在您可以解释什么是意外/令人困惑的性能差异!将上述脚本保留在您的库中，这对于检查缓存的状态很有用。