SQL warm up script

In a perfect world your SQL server has enough memory to store all data in buffer memory. If that is the case, your server will respond fast and your users will expect the server to response fast all the time. But then you need to reboot the server (or service), resulting in an empty buffer memory. All initial page reads will have to come from disk, which is a lot slower then what the users expect.
The solution is to use a warm up script, to load as many pages in memory as possible. For this there are several possibilities.
The first I used was to run a SELECT * FROM every table in the database. That did a large part of the trick, but did not include any indexes. Also it was not that generic.
The second, and current, solution I used involves sys.dm_db_index_physical_stats. A little experiment showed that running this dmv with ‘DETAILED’ as mode parameter will load all involved data and index pages in memory. Allthough this was not the case in all tested scenarios, it’s good enough for this purpose. To make life easy I wrapped this in a stored procedure, making it easily possible to load a table in memory or an entire database. Also will it show how much of the tables is currently in the buffer memory (code orginally from http://www.sqldev.org/sql-server-database-engine/help-understanding-what-is-using-all-my-memory–64bit-ee-85617.shtml).
This is provided as is with no guarantee in any way. Run your own tests before using this on production servers.

Create Procedure spWarmUp
(@mode varchar(10)=’read’
,@dbname nvarchar(128)
,@tname nvarchar(128)=NULL)

AS
DECLARE @obid bigint
DECLARE @cmd nvarchar(max)
DECLARE @cmd2 nvarchar(max)
DECLARE @cmd3 nvarchar(max)
SET @cmd=’SELECT
st.name TableName,
sum(a.page_id)*8 AS MemorySpaceKB,
SUM(au.used_pages)*8 AS StorageSpaceKB,
CASE WHEN SUM(au.used_pages) <> 0 THEN CAST((SUM(a.page_id)/CAST(SUM(au.used_pages) AS FLOAT)) * 100 AS NUMERIC(20,1)) END AS [Percent In Memory]
FROM
(SELECT database_id, allocation_unit_id, COUNT(page_id) page_id
FROM
sys.dm_os_buffer_descriptors GROUP BY database_id, allocation_unit_id) a
left JOIN sys.allocation_units au ON a.allocation_unit_id =au.allocation_unit_id
JOIN sys.partitions sp ON (au.type IN (1,3) AND au.container_id = sp.hobt_id)
OR (au.type = 2 AND au.container_id= sp.partition_id)
JOIN sys.tables st ON sp.object_id = st.object_id
AND st.is_ms_shipped = 0
WHERE a.database_id = DB_ID() and ISNULL(‘ + ISNULL(”” + @tname+ ””, ‘NULL’)+ ‘, st.name)=st.name
GROUP BY st.object_id, st.name
ORDER BY st.name’

SET @CMD2=’declare @cmd nvarchar(max)’
SET @CMD2=@CMD2 + ‘SET @cmd =”’ + REPLACE(@CMD,””,”””) + ”” + char(13) + char(10)
SET @CMD2=@CMD2 + ‘Execute
‘+@dbname+’.dbo.sp_executesql @cmd’
exec (@cmd2)

IF @mode=’load’ AND @tname IS NULL
BEGIN
select * FROM sys.dm_db_index_physical_stats(db_id(@dbname), NULL, NULL, NULL, ‘DETAILED’)
END
IF @mode=’load’ AND NOT @tname IS NULL
BEGIN
set @cmd3=’Declare @dbid int, @obid bigint
select @obid=object_id(
”’+@tname+”’), @dbid=db_id(”’+@dbname+”’)
select * FROM sys.dm_db_index_physical_stats(@dbid, @obid, NULL, NULL, ”DETAILED”)’
SET @CMD2=’declare @cmd nvarchar(max)’
SET @CMD2=@CMD2 + ‘SET @cmd =”’ + REPLACE(@CMD3,””,”””) + ”” + char(13) + char(10)
SET @CMD2=@CMD2 + ‘Execute
‘+@dbname+’.dbo.sp_executesql @cmd’
print @cmd2
exec (@cmd2)
END
IF @mode=’load’
BEGIN
SET @CMD2=’declare @cmd nvarchar(max)’
SET @CMD2=@CMD2 + ‘SET @cmd =”’ + REPLACE(@CMD,””,”””) + ”” + char(13) + char(10)
SET @CMD2=@CMD2 + ‘Execute
‘+@dbname+’.dbo.sp_executesql @cmd’
exec (@cmd2)
END
GO

Advertisements

About sqlsmurf
MS SQL Administrator

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: