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
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-----------------------------------------