Warm the cache with a table and its indexes
I have had to cache a table that undergoes daily cleanups, but had to go through the leg work of manually building the queries using all columns in all indexes as deletes were taking a long time in an overnight maintenance job.
The physical reads of the cleanup was increasing the duration of blocking.
Proc builds a set of queries for the chosen schema.table, designed to cause index scans with nolocks on Clustered, nonclustered, filtered ,columns store indexes, with no lookups to the table from those indexes to cache the table as quickly as possible without blocking other DML processes.
This should warm the cache with the table.
This proc can either script out the caching queries, or automatically execute the caching queries.
Usage : EXEC dbo.CacheTable @schemaName = 'HumanResources',@tableName = 'Employee'
-----------------------------------------------------------------
--Author : Doran Mackay
--Usage : EXEC dbo.CacheTable @schemaName = 'HumanResources',@tableName = 'Employee'
--
--Used to warm the cache of the above table.
--Use case may be to reduce the duration of a cleanup job by precaching the table to reduce the time taken for updates and deletes.
--Another use case will be if you find long running procs have a trend of having physical reads in an OLTP environment.
--
--Works with (NONCLUSTERED and CLUSTERED Row-Stored, CLUSTERED and NONCLUSTERED Column _Store, Filtered) indexes.
--Does not cache HEAPS, Spatial, XML indexes
--I have not made it work with inMemory tables as that would be redundant.
--
--Please let me know of any failures you may encounter.
Create procedure [dbo].[CacheTable] ( @schemaName as sysname, @tableName as sysname )
AS
BEGIN
set nocount on
declare @IndexKeys table (TableName sysname,indexName nvarchar(500) null,IndexKey nvarchar(2000),string nvarchar(max),RowNumber tinyint)
insert into @IndexKeys(TableName ,indexName ,IndexKey,string,RowNumber)
SELECT QUOTENAME(@schemaName)+'.'+QUOTENAME(@tableName) TableName, i.name indexName,coalesce(IndexKey,'') INDEXKey,
'select sum(checksum(' + IndexKey+ ')/1000000000) from '+ QUOTENAME(@schemaName)+'.'+QUOTENAME(@tableName) + 'with(nolock'
+case WHEN i.name is not null THEN ', index('+QUOTENAME(i.name)+'))' END
+case WHEN i.has_filter =1 THEN ' WHERE '+filter_definition ELSE '' END as string,
row_number() over(order by i.name) RowNumber
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas Sch ON Sch.schema_id = o.schema_id
LEFT JOIN (
SELECT object_id, index_id, name, LEFT(subwindow.includeKey, LEN(subwindow.includeKey) - 1) AS 'IndexKey'
FROM ( SELECT i.index_id, i.name, object_id,COALESCE(
( SELECT QUOTENAME(c.name) + ',' AS [text()]
FROM sys.indexes i2
INNER JOIN sys.index_columns ic ON ic.object_id = i2.object_id AND ic.index_id = i2.index_id
INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE
i.object_id = i2.object_id AND
i.index_id = i2.index_id
and i2.type_desc not in ('XML','HEAP','SPATIAL')
ORDER BY i2.name
FOR
XML PATH('') ),'') AS includeKey
FROM sys.indexes i
) subwindow ) window ON window.object_id = i.object_id AND window.index_id = i.index_id
WHERE sch.name = @schemaName AND o.name =@tablename and i.type_desc not in ('XML','HEAP','SPATIAL')
declare @temp table(columntotal int)
declare @loopMax tinyint = isnull((select max(RowNumber) from @IndexKeys),0),@loopcounter tinyint
if @loopMax=0
Select 'No Indexes to Cache. XML, spatial or Heaps are ignored.'
else
BEGIN --Cache and print each index in list.
declare @execString nvarchar(max)
set @loopcounter = 1
WHILE @loopMax>0 AND @loopcounter<@loopMax+1
BEGIN
set @execString = (select string from @IndexKeys where rownumber = @loopcounter)
print @execString
insert into @temp
exec (@execString)
set @loopcounter = @loopcounter+1
END
END
END
GO
