We have a client site with a 50Gb SQL 2012 database on a server with 100+ Gb of RAM.
我们有一个客户端站点，在服务器上有50Gb的SQL 2012数据库，有100+ Gb的RAM。
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.
随着应用程序的使用，SQL server在将db缓存到内存中方面做得很好，但是缓存带来的性能提升发生在查询第二次运行时，而不是第一次运行时。
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:
为了在第一次运行查询时最大化缓存命中，我们编写了一个proc，在整个DB中遍历每个表的每个索引，运行如下:
SELECT * INTO #Cache
FROM ' + @tablename + ' WITH (INDEX (' + @indexname + '))'
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.
试图迫使一个又大又丑的人为尽可能多的数据阅读。我们计划每15分钟运行一次，总的来说它做得很好。
Without debating other bottlenecks, hardware specs, query plans, or query optimization, does anybody have any better ideas about how to accomplish this same task?
在不讨论其他瓶颈、硬件规格、查询计划或查询优化的情况下，有人对如何完成相同的任务有更好的想法吗?
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
2 个解决方案
#1
16
First of all, there is a setting called "Minumum Server Memory" that looks tempting. Ignore it. From MSDN:
首先，有一个名为“Minumum服务器内存”的设置看起来很诱人。忽略它。从MSDN:
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.
数据库引擎获得的内存数量完全取决于放在实例上的工作负载。没有处理许多请求的SQL Server实例可能永远不会到达最小服务器内存。
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.
这告诉我们，设置更大的最小内存不会强制或鼓励任何预缓存。您可能有其他原因来设置它，但是预填充缓冲池不是其中之一。
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.
那么，如何预加载数据呢?很容易。只需设置一个代理作业，从每个表中执行select *。您可以将它安排为“Sql代理启动时自动启动”。换句话说，你已经在做的事情非常接近处理这个问题的标准方式。
However, I do need to suggest three changes:
不过，我确实需要提出三点建议:
- 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.
- 不要尝试使用临时表。从表格中选择。您不需要对结果做任何操作就可以让Sql Server加载您的缓冲池:您需要做的就是选择。临时表可以强制sql server在加载后从缓冲池中复制数据…最后你会(简单地)把东西储存两次。
- 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.
- 不要每15分钟跑一次。只要在启动时运行一次，然后单独运行。一旦被分配，要让Sql服务器释放内存需要花费很多。它只是不需要反复地运行。
- 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可以*地忽略这些提示，并且它将对那些对索引没有明确用途的查询这样做。确保预加载索引的最佳方法是构造一个显然使用该索引的查询。这里的一个具体建议是将结果按照与索引相同的顺序排序。这通常有助于Sql Server使用该索引，因为这样它就可以“遍历索引”来生成结果。
#2
1
This is not an answer, but to supplement Joel Coehoorn's answer, you can look at the table data in the cache using this statement. Use this to determine whether all the pages are staying in the cache as you'd expect:
这不是一个答案，但是为了补充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
#1
16
First of all, there is a setting called "Minumum Server Memory" that looks tempting. Ignore it. From MSDN:
首先，有一个名为“Minumum服务器内存”的设置看起来很诱人。忽略它。从MSDN:
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.
数据库引擎获得的内存数量完全取决于放在实例上的工作负载。没有处理许多请求的SQL Server实例可能永远不会到达最小服务器内存。
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.
这告诉我们，设置更大的最小内存不会强制或鼓励任何预缓存。您可能有其他原因来设置它，但是预填充缓冲池不是其中之一。
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.
那么，如何预加载数据呢?很容易。只需设置一个代理作业，从每个表中执行select *。您可以将它安排为“Sql代理启动时自动启动”。换句话说，你已经在做的事情非常接近处理这个问题的标准方式。
However, I do need to suggest three changes:
不过，我确实需要提出三点建议:
- 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.
- 不要尝试使用临时表。从表格中选择。您不需要对结果做任何操作就可以让Sql Server加载您的缓冲池:您需要做的就是选择。临时表可以强制sql server在加载后从缓冲池中复制数据…最后你会(简单地)把东西储存两次。
- 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.
- 不要每15分钟跑一次。只要在启动时运行一次，然后单独运行。一旦被分配，要让Sql服务器释放内存需要花费很多。它只是不需要反复地运行。
- 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可以*地忽略这些提示，并且它将对那些对索引没有明确用途的查询这样做。确保预加载索引的最佳方法是构造一个显然使用该索引的查询。这里的一个具体建议是将结果按照与索引相同的顺序排序。这通常有助于Sql Server使用该索引，因为这样它就可以“遍历索引”来生成结果。
#2
1
This is not an answer, but to supplement Joel Coehoorn's answer, you can look at the table data in the cache using this statement. Use this to determine whether all the pages are staying in the cache as you'd expect:
这不是一个答案，但是为了补充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