Forcing SQL Server to pre-cache entire database into memory
caching, memory, sql-server
Solution
First of all, there is a setting called "Minumum Server Memory" that looks tempting. Ignore it. From 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.
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.
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.
- 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.
- 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.
Problem
We have a client site with a 50Gb SQL 2012 database on a server with 100+ Gb of 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. 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 + '))' ``` 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? 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 ```