Caching SQL Server Tables After Restart

SQL Server Cached Pages and ways to bring data pages to memory
We noticed considerable latency in stored procedure execution after restarting SQL Server Instance which was triggered by accessing data from disk instead of memory. So we ended up writing a simple script to bring data pages to memory. This helped us to avoid first touch penalty after restart in production environment.

Populate SQL Server Cache
Database Level Cached Pages
Table Level Cached Pages

Populate SQL Server Cache

Populate SQL Server Cache

Populate Temporary Table with Base Table Data to avoid Physical Reads. This helps to avoid DISK Physical reads after Server or Instance Restart.

/******************************************************************************************************
** Project: Operations
** Auth: Raju Venkataraman
** Date: 2016-07-28 Created
** Description: Populate Temporary Table with Base Table Data to avoid Physical Reads
**************************
** Change History
**************************
** CR Date Author Description
** ----- ----------– ----------- ------------------------------------------------------------
** 1  2016-07-28 Raju Venkataraman Gather Details on Databases Level Buffer
********************************************************************************************************/

-----------BEGIN SQL SCRIPT HEADER-------------------
DECLARE @DB_NAME VARCHAR(128);
SET @DB_NAME = ( SELECT DB_NAME(dbid)
                 FROM   master..sysprocesses
                 WHERE  spid = @@SPID
               );
PRINT '-----------------------------------------------------------------------';
PRINT '-----START RAPID DEVELOPEMENT SQL SCRIPT--------';
PRINT '-----SCRIPT RAN ON DB: ' + @DB_NAME;
PRINT '-----SCRIPT START TIME: '
    + CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE()), 121);
PRINT '-----MachineName : ' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR);
PRINT '-----SQL Instance : ' + CAST(@@SERVERNAME AS VARCHAR);
PRINT '-----DB User : ' + CURRENT_USER;
PRINT '-----System User : ' + SYSTEM_USER;
PRINT '-----Host : ' + HOST_NAME();
PRINT '-----Application : ' + APP_NAME();
PRINT '-----TranCount : ' + CAST (@@trancount AS VARCHAR);
PRINT '-----------------------------------------------------------------------';
----------------END SQL SCRIPT HEADER---------------------

-- Script Specific Variables Declarations

DECLARE @StartTime DATETIME2 ,
    @EndTime DATETIME2;
SELECT  @StartTime = GETDATE();

-- Script Block Start


DECLARE @pTableName sysname , @pSchemaName sysname , @pdSQL NVARCHAR(4000)
DECLARE TName CURSOR FOR
SELECT TABLE_SCHEMA , TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE 1=1 -- TABLE_SCHEMA ='dbo'
-- AND TABLE_NAME IN ('SampleTable',)
AND TABLE_TYPE ='BASE TABLE'

OPEN TName
FETCH NEXT FROM TName INTO  @pSchemaName ,@pTableName

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @pdSQL = 'SELECT * INTO #'+@pTableName + ' FROM ' + @pSchemaName+'.'+ @pTableName
  PRINT @pdSQL
  EXEC(@pdSQL)

  FETCH NEXT FROM TName INTO  @pSchemaName ,@pTableName

END

CLOSE TName
DEALLOCATE TName

-- Script Block End

SELECT  @EndTime = GETDATE();

-----------BEGIN SQL SCRIPT FOOTER--------------------------------------
PRINT '----------------------------------------------------------------';
PRINT '---FINISHED SQL SCRIPT--';
PRINT '---COMPLETED TIME:' + CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE()), 121);
PRINT '---TranCount : ' + CAST (@@trancount AS VARCHAR);
PRINT 'Execution Time : ' + CONVERT(VARCHAR(255), ( DATEDIFF(MILLISECOND,
                                                             @StartTime,
                                                             @EndTime) ))
    + ' MilliSeconds';
PRINT '----------------------------------------------------------------';

-----------END SQL SCRIPT FOOTER-----------------------------------------

Database Level Cached Pages

Database Level Buffer Pages

/******************************************************************************************************
** Project: Operations
** Issue: Gather Details on Databases Level Buffer
** ShortDesc: Gather Details on Databases Level Buffer
** Auth: Raju Venkataraman
** Date: 2016-07-28 Created
** Description: Gather Details on Databases Level Buffer
**************************
** Change History
**************************
** CR Date Author Description
** ----- ----------– ----------- ------------------------------------------------------------
** 1  2016-07-28 Raju Venkataraman Gather Details on Databases Level Buffer
********************************************************************************************************/

-----------BEGIN SQL SCRIPT HEADER-------------------
DECLARE @DB_NAME VARCHAR(128);
SET @DB_NAME = ( SELECT DB_NAME(dbid)
                 FROM   master..sysprocesses
                 WHERE  spid = @@SPID
               );
PRINT '-----------------------------------------------------------------------';
PRINT '-----START RAPID DEVELOPEMENT SQL SCRIPT--------';
PRINT '-----SCRIPT RAN ON DB: ' + @DB_NAME;
PRINT '-----SCRIPT START TIME: '
    + CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE()), 121);
PRINT '-----MachineName : ' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR);
PRINT '-----SQL Instance : ' + CAST(@@SERVERNAME AS VARCHAR);
PRINT '-----DB User : ' + CURRENT_USER;
PRINT '-----System User : ' + SYSTEM_USER;
PRINT '-----Host : ' + HOST_NAME();
PRINT '-----Application : ' + APP_NAME();
PRINT '-----TranCount : ' + CAST (@@trancount AS VARCHAR);
PRINT '-----------------------------------------------------------------------';
----------------END SQL SCRIPT HEADER---------------------

-- Script Specific Variables Declarations

DECLARE @StartTime DATETIME2 ,
    @EndTime DATETIME2;
SELECT  @StartTime = GETDATE();

-- Script Block Start

SELECT  ( CASE WHEN ( bd.database_id = 32767 ) THEN 'Resource Database'
               ELSE DB_NAME(database_id)
          END ) AS 'Database Name' ,
        buffer_pages = COUNT_BIG(bd.page_id) ,
        buffer_mb = COUNT_BIG(bd.page_id) / 128
FROM    sys.dm_os_buffer_descriptors bd
GROUP BY bd.database_id
ORDER BY buffer_mb DESC;

-- Script Block End

SELECT  @EndTime = GETDATE();

-----------BEGIN SQL SCRIPT FOOTER--------------------------------------
PRINT '----------------------------------------------------------------';
PRINT '---FINISHED SQL SCRIPT--';
PRINT '---COMPLETED TIME:' + CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE()), 121);
PRINT '---TranCount : ' + CAST (@@trancount AS VARCHAR);
PRINT 'Execution Time : ' + CONVERT(VARCHAR(255), ( DATEDIFF(MILLISECOND,
                                                             @StartTime,
                                                             @EndTime) ))
    + ' MilliSeconds';
PRINT '----------------------------------------------------------------';

-----------END SQL SCRIPT FOOTER-----------------------------------------

Table Level Cached Pages

Object Level Buffer Pages for a Database

/******************************************************************************************************
** Project: Operations
** Issue: Gather Details on Table Level Buffer
** ShortDesc: Gather Details on Table Level Buffer
** Auth: Raju Venkataraman
** Date: 2016-07-28 Created
** Description: Gather Details on Databases Level Buffer
**************************
** Change History
**************************
** CR Date Author Description
** ----- ----------– ----------- ------------------------------------------------------------
** 1  2016-07-28 Raju Venkataraman Gather Details on Databases Level Buffer
********************************************************************************************************/

-----------BEGIN SQL SCRIPT HEADER-------------------
DECLARE @DB_NAME VARCHAR(128);
SET @DB_NAME = ( SELECT DB_NAME(dbid)
                 FROM   master..sysprocesses
                 WHERE  spid = @@SPID
               );
PRINT '-----------------------------------------------------------------------';
PRINT '-----START RAPID DEVELOPEMENT SQL SCRIPT--------';
PRINT '-----SCRIPT RAN ON DB: ' + @DB_NAME;
PRINT '-----SCRIPT START TIME: '
    + CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE()), 121);
PRINT '-----MachineName : ' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR);
PRINT '-----SQL Instance : ' + CAST(@@SERVERNAME AS VARCHAR);
PRINT '-----DB User : ' + CURRENT_USER;
PRINT '-----System User : ' + SYSTEM_USER;
PRINT '-----Host : ' + HOST_NAME();
PRINT '-----Application : ' + APP_NAME();
PRINT '-----TranCount : ' + CAST (@@trancount AS VARCHAR);
PRINT '-----------------------------------------------------------------------';
----------------END SQL SCRIPT HEADER---------------------

-- Script Specific Variables Declarations

DECLARE @StartTime DATETIME2 ,
    @EndTime DATETIME2;
SELECT  @StartTime = GETDATE();

-- Script Block Start

WITH    src
          AS ( SELECT   [SchemaName] = SCHEMA_NAME(o.schema_id) ,
					    [ObjectName] = o.name ,
                        [Type] = o.type_desc ,
                        [Index] = COALESCE(i.name, '') ,
                        [Index_Type] = i.type_desc ,
                        p.[object_id] ,
                        p.index_id ,
                        au.allocation_unit_id
               FROM     sys.partitions AS p
                        INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id
                        INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id]
                        INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id]
                                                       AND p.index_id = i.index_id
               WHERE    au.[type] IN ( 1, 2, 3 )

                        AND o.is_ms_shipped = 0
             )
    SELECT  src.SchemaName ,
			src.[ObjectName] ,
            src.[Type] ,
            src.[Index] ,
            src.Index_Type ,
            buffer_pages = COUNT_BIG(b.page_id) ,
            buffer_mb = COUNT_BIG(b.page_id) / 128
    FROM    src
            INNER JOIN sys.dm_os_buffer_descriptors AS b ON src.allocation_unit_id = b.allocation_unit_id
    WHERE   b.database_id = DB_ID()
    GROUP BY src.SchemaName,
			src.[ObjectName] ,
            src.[Type] ,
            src.[Index] ,
            src.Index_Type
    ORDER BY buffer_pages DESC;

-- Script Block End

SELECT  @EndTime = GETDATE();

-----------BEGIN SQL SCRIPT FOOTER--------------------------------------
PRINT '----------------------------------------------------------------';
PRINT '---FINISHED SQL SCRIPT--';
PRINT '---COMPLETED TIME:' + CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE()), 121);
PRINT '---TranCount : ' + CAST (@@trancount AS VARCHAR);
PRINT 'Execution Time : ' + CONVERT(VARCHAR(255), ( DATEDIFF(MILLISECOND,
                                                             @StartTime,
                                                             @EndTime) ))
    + ' MilliSeconds';
PRINT '----------------------------------------------------------------';

-----------END SQL SCRIPT FOOTER-----------------------------------------

Populate SQL Server Cache

Database Level Cached Pages

Table Level Cached Pages

You may also like...

Looking Back at 2016 !!

SQL Join Diagram and Query

Concurrency and Storage Needs

Leave a Reply Cancel reply