强制SQL Server将整个数据库预先缓存到内存中

Question

我们有一个客户网站，其中一台服务器上有一个50Gb的SQL 2012数据库和100+ Gb的RAM。

由于应用程序被使用时，SQL服务器会将数据库缓存到内存中，但性能提升发生在第二次运行查询时，而不是第一次。

为了尝试最大化第一次运行查询时的缓存命中率，我们编写了一个过程，遍历整个数据库中每个表的每个索引，运行以下代码：

SELECT * INTO #Cache 
FROM ' + @tablename + ' WITH (INDEX (' + @indexname + '))'

为了尽可能地强制读取尽可能多的数据，我们计划每15分钟运行一次，并且通常表现良好。

不讨论其他瓶颈、硬件规格、查询计划或查询优化，是否有人有更好的想法来完成同样的任务呢？

更新：
感谢提供的建议。已将“INTO #Cache”移除，并进行了测试，但并没有对填充缓冲区产生影响。
添加：我选择仅从索引中选择键，而不是选择所有内容。显然，这样更加直接，速度也更快。
添加：还要读取和缓存约束索引。

以下是当前的代码：（希望对其他人有用）

CREATE VIEW _IndexView
as
-- Easy way to access sysobject and sysindex data
SELECT 
so.name as tablename,
si.name as indexname,
CASE si.indid WHEN 1 THEN 1 ELSE 0 END as isClustered,
CASE WHEN (si.status & 2)<>0 then 1 else 0 end as isUnique,
dbo._GetIndexKeys(so.name, si.indid) as Keys,
    CONVERT(bit,CASE WHEN EXISTS (SELECT * FROM sysconstraints sc WHERE object_name(sc.constid) = si.name) THEN 1 ELSE 0 END) as IsConstraintIndex
FROM    sysobjects so
INNER JOIN sysindexes si ON so.id = si.id
WHERE   (so.xtype = 'U')--User Table
AND     ((si.status & 64) = 0) --Not statistics index
AND (   (si.indid = 0) AND (so.name <> si.name) --not a default clustered index
        OR
        (si.indid > 0)
    )
AND si.indid <> 255 --is not a system index placeholder

UNION
SELECT 
so.name as tablename,
si.name as indexname,
CASE si.indid WHEN 1 THEN 1 ELSE 0 END as isClustered,
CASE WHEN (si.status & 2)<>0 then 1 else 0 end as isUnique,
dbo._GetIndexKeys(so.name, si.indid) as Keys,
CONVERT(bit,0) as IsConstraintIndex
FROM    sysobjects so
INNER JOIN sysindexes si ON so.id = si.id
WHERE   (so.xtype = 'V')--View
AND     ((si.status & 64) = 0) --Not statistics index
GO


CREATE PROCEDURE _CacheTableToSQLMemory
@tablename varchar(100)
AS
BEGIN
DECLARE @indexname varchar(100)
DECLARE @xtype varchar(10)
DECLARE @SQL varchar(MAX)
DECLARE @keys varchar(1000)

DECLARE @cur CURSOR
SET @cur = CURSOR FOR
SELECT  v.IndexName, so.xtype, v.keys
FROM    _IndexView v
INNER JOIN sysobjects so ON so.name = v.tablename
WHERE   tablename = @tablename

PRINT 'Caching Table ' + @Tablename
OPEN @cur
FETCH NEXT FROM @cur INTO @indexname, @xtype, @keys
WHILE (@@FETCH_STATUS = 0)
BEGIN
        PRINT '    Index ' + @indexname
        --BEGIN TRAN
            IF @xtype = 'V'
                SET @SQL = 'SELECT ' + @keys + ' FROM ' + @tablename + ' WITH (noexpand, INDEX (' + @indexname + '))' --
            ELSE
                SET @SQL = 'SELECT ' + @keys + ' FROM ' + @tablename + ' WITH (INDEX (' + @indexname + '))' --

            EXEC(@SQL)
        --ROLLBACK TRAN
        FETCH NEXT FROM @cur INTO @indexname, @xtype, @keys
END
CLOSE @cur
DEALLOCATE @cur

END
GO

- El Mark

---

每15分钟？如果你的数据库大小为50GB，并且你给了SQL Server 100GB以上的内存，那么你只需要在启动时执行这一次操作。请注意，此处不提供解释或其他内容。 - Aaron Bertrand

你为什么要插入到临时表中？ - Martin Smith

@MartinSmith 我怀疑编写此代码的开发人员发现将所有数据选择到一个 #temp 表中比在 SSMS 中将输出呈现为一堆网格更快（可能是在非常慢的网络连接上）。 - Aaron Bertrand

sys.dm_db_index_physical_stats 带有大多数参数为 null，并且最后一个参数为 detailed 将读取所有页面。虽然不确定它是否会将所有内容保留在缓存中。有一些机制会不利于此，以避免淹没缓冲池。 - Martin Smith

GetIndexKeys？你忘记定义它了吗？我们也遇到了同样的问题。 - DJA

Answer 1

首先，有一个名为“最小服务器内存”的设置看起来很诱人。请忽略它。来自MSDN：

数据库引擎获取的内存量完全取决于实例所承载的工作负载。不处理许多请求的SQL Server实例可能永远无法达到最小服务器内存。

这告诉我们设置更大的最小内存不会强制或鼓励任何预缓存。您可能有其他原因设置此项, 但填充缓冲池不是其中之一。

那么，您该如何预加载数据？这很容易。只需设置代理作业以从每个表中选择select * 。您可以将其安排在“Sql Agent启动时自动启动”。换句话说，您已经接近处理此问题的标准方式。

但是，我需要建议进行三个更改：

1. 不要尝试使用临时表。直接从表中选择即可，你不需要对结果进行任何操作以使Sql Server加载缓冲池：你所需要做的就是选择。使用临时表可能会迫使Sql Server在加载后将数据从缓冲池复制...这样你最终会（短暂地）存储两次。
2. 不要每15分钟运行一次。只需在启动时运行一次，然后让它自己运行。一旦分配了内存，要让Sql Server释放内存需要付出很大的代价。没有必要一遍又一遍地重新运行此操作。
3. 不要尝试提示索引。提示只是提示而已。Sql Server可以忽略这些提示，并且对于没有明显用途的查询，它也会这样做。确保索引预加载的最佳方法是构造一个明显使用该索引的查询。这里的一个具体建议是按与索引相同的顺序排序结果。这通常有助于Sql Server使用该索引，因为它可以“遍历索引”来生成结果。

- Joel Coehoorn

---

或者创建一个系统存储过程，并将其标记为启动过程；这将防止在不重新启动引擎的情况下重新启动 SQL Server 代理时出现不必要的磨损。 - Aaron Bertrand

SQL Server有时会忽略仅锁定提示。如果无法生成计划，则这些索引提示将产生错误。 - Martin Smith

Answer 2

这不是一个答案，而是为了补充Joel Coehoorn的答案，您可以使用以下语句查看缓存中的表数据。 使用此操作确定所有页面是否按预期保留在缓存中：

USE DBMaint
GO
SELECT COUNT(1) AS cached_pages_count, SUM(s.used_page_count)/COUNT(1) AS total_page_count,
name AS BaseTableName, IndexName,
IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT s_obj.name, s_obj.index_id,
s_obj.allocation_unit_id, s_obj.OBJECT_ID,
i.name IndexName, i.type_desc IndexTypeDesc
FROM
(
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id ,allocation_unit_id, OBJECT_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_NAME(OBJECT_ID) AS name,
index_id, allocation_unit_id, OBJECT_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 s_obj
LEFT JOIN sys.indexes i ON i.index_id = s_obj.index_id
AND i.OBJECT_ID = s_obj.OBJECT_ID ) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
INNER JOIN sys.dm_db_partition_stats s ON s.index_id = obj.index_id AND s.object_id = obj.object_ID
WHERE database_id = DB_ID()
GROUP BY name, obj.index_id, IndexName, IndexTypeDesc
ORDER BY obj.name;
GO

- Dave.Gugg

---

请注意，如果在运行此查询时正在构建/重建索引，则查询可能要等到索引构建完成才能完成。 - marknuzz

Answer 3

使用此代码替换函数dbo._GetIndexKeys

(SELECT STRING_AGG(COL_NAME(ic.object_id,ic.column_id), ',') FROM sys.index_columns ic WHERE ic.object_id = so.id AND ic.index_id = si.indid) AS Keys,

--dbo._GetIndexKeys(so.name, si.indid) as Keys,

- Neo Zhang