强制SQL Server将整个数据库预缓存到内存中 [英] Forcing SQL Server to pre-cache entire database into memory

问题描述

我们有一个客户端站点，该站点具有一台具有50Gb SQL 2012数据库的服务器，该服务器具有100 Gb以上的RAM.

We have a client site with a 50Gb SQL 2012 database on a server with 100+ Gb of RAM.

使用该应用程序时，SQL Server在将数据库缓存到内存方面做得很出色，但是缓存的性能提高发生在查询运行的第二次而不是第一次.

As the application is used, SQL server does a great job of caching the db into memory but the performance increase from the caching occurs the SECOND time a query is run, not the first.

为了尝试在首次运行查询时最大程度地提高缓存命中率，我们编写了一个proc，该proc遍历整个数据库中每个表的每个索引，并运行以下命令:

To try to maximize cache hits the first time queries are run, we wrote a proc that iterates through every index of every table within the entire DB, running this:

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

试图强制进行较大的，丑陋的，人为的读取操作，以获取尽可能多的数据. 我们将其安排为每15分钟运行一次，并且总体上做得很好.

In an attempt to force a big, ugly, contrived read for as much data as possible. We have it scheduled to run every 15 minutes, and it does a great job in general.

在不讨论其他瓶颈，硬件规格，查询计划或查询优化的情况下，有人对如何完成同一任务有更好的主意吗?

Without debating other bottlenecks, hardware specs, query plans, or query optimization, does anybody have any better ideas about how to accomplish this same task?

更新
感谢您的建议.删除了"INTO #Cache".经过测试它对填充缓冲区没有影响.
补充:我不是从选择*"中而是从索引"中选择键. (显然)这更切合实际，而且速度更快.
新增:阅读和阅读；缓存约束索引也是如此.

UPDATE
Thanks for the suggestions. Removed the "INTO #Cache". Tested & it didn't make a difference on filling the buffer.
Added: Instead of Select *, I'm selecting ONLY the keys from the Index. This (obviously) is more to-the-point and is much faster.
Added: Read & Cache Constraint Indexes also.

以下是当前代码:(希望它对其他人有用)

Here's the current code: (hope it's useful for somebody else)

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

推荐答案

首先，有一个名为"Minumum Server Memory"的设置看起来很诱人.忽略它. 从MSDN:

First of all, there is a setting called "Minumum Server Memory" that looks tempting. Ignore it. From MSDN:

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

The amount of memory acquired by the Database Engine is entirely dependent on the workload placed on the instance. A SQL Server instance that is not processing many requests may never reach min server memory.

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

This tells us that setting a larger minimum memory won't force or encourage any pre-caching. You may have other reasons to set this, but pre-filling the buffer pool isn't one of them.

那么您该怎么做才能预加载数据?这简单.只需设置一个代理作业即可在每个表中执行select *.您可以将其计划为"Sql Agent启动时自动启动".换句话说，您已经在做的工作非常接近处理此问题的标准方法.

So what can you do to pre-load data? It's easy. Just set up an agent job to do a select * from every table. You can schedule it to "Start automatically when Sql Agent Starts". In other words, what you're already doing is pretty close to the standard way to handle this.

但是，我确实需要提出三个更改:

However, I do need to suggest three changes:

1. 不要尝试使用临时表.只需从表中选择即可.您不需要对结果做任何事情即可让Sql Server加载缓冲池:您要做的就是选择.临时表可能会迫使sql server在加载后复制缓冲池中的数据...您最终(简短地)存储了两次.
2. 不要每15分钟运行一次.只需在启动时运行一次，然后不理会它.分配后，需要花费很多时间才能让Sql Server释放内存.只是不需要一遍又一遍地重新运行它.
3. 不要尝试暗示索引.提示仅仅是:提示. Sql Server可以随意忽略这些提示，对于没有明确使用索引的查询，它将这样做.确保索引已预加载的最佳方法是构造一个明显使用该索引的查询.这里的一个具体建议是按与索引相同的顺序对结果进行排序.这通常将有助于Sql Server使用该索引，因为这样它就可以遍历索引"以产生结果.

1. Don't try to use a temporary table. Just select from the table. You don't need to do anything with the results to get Sql Server to load your buffer pool: all you need to do is the select. A temporary table could force sql server to copy the data from the buffer pool after loading... you'd end up (briefly) storing things twice.
2. Don't run this every 15 minutes. Just run it once at startup, and then leave it alone. Once allocated, it takes a lot to get Sql Server to release memory. It's just not needed to re-run this over and over.
3. Don't try to hint an index. Hints are just that: hints. Sql Server is free to ignore those hints, and it will do so for queries that have no clear use for the index. The best way to make sure the index is pre-loaded is to construct a query that obviously uses that index. One specific suggestion here is to order the results in the same order as the index. This will often help Sql Server use that index, because then it can "walk the index" to produce the results.

这篇关于强制SQL Server将整个数据库预缓存到内存中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！