title: Forcing SQL Server to pre-cache entire database into memory
body: <p>We have a client site with a 50Gb SQL 2012 database on a server with 100+ Gb of RAM.</p>
  
  <p>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.</p>
  
  <p>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: </p>
  
  <pre><code>SELECT * INTO #Cache 
  FROM ' + @tablename + ' WITH (INDEX (' + @indexname + '))'
  </code></pre>
  
  <p>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.</p>
  
  <p>Without debating other bottlenecks, hardware specs, query plans, or query optimization, does anybody have any better ideas about how to accomplish this same task?</p>
  
  <p><b>UPDATE</b><br/>
  Thanks for the suggestions. Removed the "INTO #Cache". Tested &amp; it didn't make a difference on filling the buffer.  <br/>
  Added: Instead of Select *, I'm selecting ONLY the keys from the Index. This (obviously) is more to-the-point and is much faster.<br/>
  Added: Read &amp; Cache Constraint Indexes also.</p>
  
  <p>Here's the current code: (hope it's useful for somebody else)</p>
  
  <pre><code>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 &amp; 2)&lt;&gt;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 &amp; 64) = 0) --Not statistics index
  AND (   (si.indid = 0) AND (so.name &lt;&gt; si.name) --not a default clustered index
          OR
          (si.indid &gt; 0)
      )
  AND si.indid &lt;&gt; 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 &amp; 2)&lt;&gt;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 &amp; 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
  </code></pre>