We needed to capture physical reads over time from our SQL server. One method we decided to deploy was to check sys.dm_exec_query_stats periodically and capture the differences between each check. The way I am doing it below is not 100% accurate because sys.dm_exec_query_stats is based on cached plans and the server will let clear the plan data over time. This means that when the differences are calculated any plan that was cleared from cache would not be included in the current check, but would still be in the comparison record. This could be solved by storing and comparing at the plan level but for my purposes I prefer to store summary information and do not mind this issue because I am running my comparison every hour and then will be averaging the results over time, and the result does not need to be exactly precise, just close enough to give us a general understanding.
This is the query inside the job:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
IF NOT EXISTS (SELECT * FROM dba.sys.objects WHERE object_id = OBJECT_ID(N'dba.[dbo].[dm_exec_query_Stats_summary]') AND type in (N'U')) CREATE TABLE dba.dbo.dm_exec_query_Stats_summary ( SysDateTime DATETIME NOT NULL, PhysicalReads BIGINT NOT NULL, LogicalReads BIGINT NOT NULL , PhysicalReads_Delta BIGINT NULL, LogicalReads_Delta BIGINT NULL ) INSERT INTO dba.dbo.dm_exec_query_Stats_summary (SysDateTime, PhysicalReads, LogicalReads, PhysicalReads_Delta, LogicalReads_Delta) SELECT A.SYSDATETIME, A.PhysicalReads, A.LogicalReads ,A.PhysicalReads - B.PhysicalReads ,A.LogicalReads - B.LogicalReads FROM( SELECT SYSDATETIME() AS SYSDATETIME ,SUM(total_physical_reads) as PhysicalReads ,sum(total_logical_Reads) as LogicalReads FROM sys.dm_exec_query_Stats A )A OUTER APPLY ( SELECT TOP 1 B.PhysicalReads, B.LogicalReads FROM dba.dbo.dm_exec_query_Stats_summary B WHERE B.sysdatetime < A.SYSDATETIME ORDER BY B.SysDateTime DESC )B |
This is what the table looks like with a few records. Note that this is a result of running the job multiple times with a few seconds between each interval.