SQL Server: Dynamically Calculate Delta Values For Numeric And Date Columns From DMV’s Or Any Table

DMV’s are very useful. In fact, they are like glass windows that lets you look at a running engine. There are lots of them and they give us lots of numbers but

  • Most are cumulative in nature!  i.e., the numbers keep on increasing and never decrease.
  • A variation of above like space usage where the numbers mostly go up but seldom come down except on rare occasions (like DB shrink, tempdb resets on system restarts, etc).

In both cases above, we need “delta values” – the difference in value between the previous time and the current time. Let us say we collect the size of all the databases once a day. Over time, we want to see how much the databases grew by each day, not the line that goes from the floor to ceiling at an angle for the year when visualized in a BI tool. With the “each day” delta number, we may get to know that certain databases grow on weekends,  month-ends or quarter-ends more than other days. If we are collecting data more frequently, we may notice that we can get even more insights using delta values (like what I blogged before for backups).

The tedium of “Deltas”

As useful as DMV’s are, they are just as notorious when it comes to interpreting, visualizing and making sense of the ever-growing, cumulative numbers. I will give you an example to prove my point.

Let us look at wait statistics as explained by Paul Randall here (Thank you Paul) – SQL is pretty straight-forward and it ignores benign waits

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        -- These wait types are almost 100% never a problem and so they are
        -- filtered out to avoid them skewing the results. Click on the URL
        -- for more information.
        N'BROKER_EVENTHANDLER', -- https://www.sqlskills.com/help/waits/BROKER_EVENTHANDLER
        N'BROKER_RECEIVE_WAITFOR', -- https://www.sqlskills.com/help/waits/BROKER_RECEIVE_WAITFOR
        N'BROKER_TASK_STOP', -- https://www.sqlskills.com/help/waits/BROKER_TASK_STOP
        N'BROKER_TO_FLUSH', -- https://www.sqlskills.com/help/waits/BROKER_TO_FLUSH
        N'BROKER_TRANSMITTER', -- https://www.sqlskills.com/help/waits/BROKER_TRANSMITTER
        N'CHECKPOINT_QUEUE', -- https://www.sqlskills.com/help/waits/CHECKPOINT_QUEUE
        N'CHKPT', -- https://www.sqlskills.com/help/waits/CHKPT
        N'CLR_AUTO_EVENT', -- https://www.sqlskills.com/help/waits/CLR_AUTO_EVENT
        N'CLR_MANUAL_EVENT', -- https://www.sqlskills.com/help/waits/CLR_MANUAL_EVENT
        N'CLR_SEMAPHORE', -- https://www.sqlskills.com/help/waits/CLR_SEMAPHORE

        -- Maybe comment these four out if you have mirroring issues
        N'DBMIRROR_DBM_EVENT', -- https://www.sqlskills.com/help/waits/DBMIRROR_DBM_EVENT
        N'DBMIRROR_EVENTS_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_EVENTS_QUEUE
        N'DBMIRROR_WORKER_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_WORKER_QUEUE
        N'DBMIRRORING_CMD', -- https://www.sqlskills.com/help/waits/DBMIRRORING_CMD

        N'DIRTY_PAGE_POLL', -- https://www.sqlskills.com/help/waits/DIRTY_PAGE_POLL
        N'DISPATCHER_QUEUE_SEMAPHORE', -- https://www.sqlskills.com/help/waits/DISPATCHER_QUEUE_SEMAPHORE
        N'EXECSYNC', -- https://www.sqlskills.com/help/waits/EXECSYNC
        N'FSAGENT', -- https://www.sqlskills.com/help/waits/FSAGENT
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', -- https://www.sqlskills.com/help/waits/FT_IFTS_SCHEDULER_IDLE_WAIT
        N'FT_IFTSHC_MUTEX', -- https://www.sqlskills.com/help/waits/FT_IFTSHC_MUTEX

        -- Maybe comment these six out if you have AG issues
        N'HADR_CLUSAPI_CALL', -- https://www.sqlskills.com/help/waits/HADR_CLUSAPI_CALL
        N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', -- https://www.sqlskills.com/help/waits/HADR_FILESTREAM_IOMGR_IOCOMPLETION
        N'HADR_LOGCAPTURE_WAIT', -- https://www.sqlskills.com/help/waits/HADR_LOGCAPTURE_WAIT
        N'HADR_NOTIFICATION_DEQUEUE', -- https://www.sqlskills.com/help/waits/HADR_NOTIFICATION_DEQUEUE
        N'HADR_TIMER_TASK', -- https://www.sqlskills.com/help/waits/HADR_TIMER_TASK
        N'HADR_WORK_QUEUE', -- https://www.sqlskills.com/help/waits/HADR_WORK_QUEUE

        N'KSOURCE_WAKEUP', -- https://www.sqlskills.com/help/waits/KSOURCE_WAKEUP
        N'LAZYWRITER_SLEEP', -- https://www.sqlskills.com/help/waits/LAZYWRITER_SLEEP
        N'LOGMGR_QUEUE', -- https://www.sqlskills.com/help/waits/LOGMGR_QUEUE
        N'MEMORY_ALLOCATION_EXT', -- https://www.sqlskills.com/help/waits/MEMORY_ALLOCATION_EXT
        N'ONDEMAND_TASK_QUEUE', -- https://www.sqlskills.com/help/waits/ONDEMAND_TASK_QUEUE
        N'PREEMPTIVE_XE_GETTARGETSTATE', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_XE_GETTARGETSTATE
        N'PWAIT_ALL_COMPONENTS_INITIALIZED', -- https://www.sqlskills.com/help/waits/PWAIT_ALL_COMPONENTS_INITIALIZED
        N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', -- https://www.sqlskills.com/help/waits/PWAIT_DIRECTLOGCONSUMER_GETNEXT
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
        N'QDS_ASYNC_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_ASYNC_QUEUE
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
            -- https://www.sqlskills.com/help/waits/QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP
        N'QDS_SHUTDOWN_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_SHUTDOWN_QUEUE
        N'REDO_THREAD_PENDING_WORK', -- https://www.sqlskills.com/help/waits/REDO_THREAD_PENDING_WORK
        N'REQUEST_FOR_DEADLOCK_SEARCH', -- https://www.sqlskills.com/help/waits/REQUEST_FOR_DEADLOCK_SEARCH
        N'RESOURCE_QUEUE', -- https://www.sqlskills.com/help/waits/RESOURCE_QUEUE
        N'SERVER_IDLE_CHECK', -- https://www.sqlskills.com/help/waits/SERVER_IDLE_CHECK
        N'SLEEP_BPOOL_FLUSH', -- https://www.sqlskills.com/help/waits/SLEEP_BPOOL_FLUSH
        N'SLEEP_DBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DBSTARTUP
        N'SLEEP_DCOMSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DCOMSTARTUP
        N'SLEEP_MASTERDBREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERDBREADY
        N'SLEEP_MASTERMDREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERMDREADY
        N'SLEEP_MASTERUPGRADED', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERUPGRADED
        N'SLEEP_MSDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_MSDBSTARTUP
        N'SLEEP_SYSTEMTASK', -- https://www.sqlskills.com/help/waits/SLEEP_SYSTEMTASK
        N'SLEEP_TASK', -- https://www.sqlskills.com/help/waits/SLEEP_TASK
        N'SLEEP_TEMPDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_TEMPDBSTARTUP
        N'SNI_HTTP_ACCEPT', -- https://www.sqlskills.com/help/waits/SNI_HTTP_ACCEPT
        N'SP_SERVER_DIAGNOSTICS_SLEEP', -- https://www.sqlskills.com/help/waits/SP_SERVER_DIAGNOSTICS_SLEEP
        N'SQLTRACE_BUFFER_FLUSH', -- https://www.sqlskills.com/help/waits/SQLTRACE_BUFFER_FLUSH
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', -- https://www.sqlskills.com/help/waits/SQLTRACE_INCREMENTAL_FLUSH_SLEEP
        N'SQLTRACE_WAIT_ENTRIES', -- https://www.sqlskills.com/help/waits/SQLTRACE_WAIT_ENTRIES
        N'WAIT_FOR_RESULTS', -- https://www.sqlskills.com/help/waits/WAIT_FOR_RESULTS
        N'WAITFOR', -- https://www.sqlskills.com/help/waits/WAITFOR
        N'WAITFOR_TASKSHUTDOWN', -- https://www.sqlskills.com/help/waits/WAITFOR_TASKSHUTDOWN
        N'WAIT_XTP_RECOVERY', -- https://www.sqlskills.com/help/waits/WAIT_XTP_RECOVERY
        N'WAIT_XTP_HOST_WAIT', -- https://www.sqlskills.com/help/waits/WAIT_XTP_HOST_WAIT
        N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', -- https://www.sqlskills.com/help/waits/WAIT_XTP_OFFLINE_CKPT_NEW_LOG
        N'WAIT_XTP_CKPT_CLOSE', -- https://www.sqlskills.com/help/waits/WAIT_XTP_CKPT_CLOSE
        N'XE_DISPATCHER_JOIN', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_JOIN
        N'XE_DISPATCHER_WAIT', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_WAIT
        N'XE_TIMER_EVENT' -- https://www.sqlskills.com/help/waits/XE_TIMER_EVENT
        )
    AND [waiting_tasks_count] > 0
    )
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
    CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) <span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>&lt; 95; -- percentage threshold

In the view above the following values are "cumulative"

  • [Wait_S]
  • [Resource_S]
  • [Signal_S]
  • [WaitCount]

The current results looks like this

 WaitType             Wait_S    Resource_S   Signal_S    WaitCount  Percentage  AvgWait_S AvgRes_S AvgSig_S   Help/Info URL
------------------------------------------- ----------- ---------------------------------------------------- ------------------------------------------------------------
CXPACKET             32796.43  30367.47     2428.96     3464474    19.35       0.0095    0.0088   0.0007     https://www.sqlskills.com/help/waits/CXPACKET
SOS_SCHEDULER_YIELD  28522.83  104.53       28418.30    42182626   16.83       0.0007    0.0000   0.0007     https://www.sqlskills.com/help/waits/SOS_SCHEDULER_YIELD
BACKUPBUFFER         23754.61  22785.43     969.18      671743     14.02       0.0354    0.0339   0.0014     https://www.sqlskills.com/help/waits/BACKUPBUFFER
ASYNC_IO_COMPLETION  23368.86  23367.88     0.98        451        13.79       51.8156   51.8135  0.0022     https://www.sqlskills.com/help/waits/ASYNC_IO_COMPLETION
BACKUPIO             22491.44  22313.35     178.09      473663     13.27       0.0475    0.0471   0.0004     https://www.sqlskills.com/help/waits/BACKUPIO
PAGELATCH_EX         8779.09   5529.01      3250.08     5340424    5.18        0.0016    0.0010   0.0006     https://www.sqlskills.com/help/waits/PAGELATCH_EX
PAGEIOLATCH_SH       7054.89   6778.47      276.43      2260281    4.16        0.0031    0.0030   0.0001     https://www.sqlskills.com/help/waits/PAGEIOLATCH_SH
ASYNC_NETWORK_IO     4612.66   3143.56      1469.10     150094     2.72        0.0307    0.0209   0.0098     https://www.sqlskills.com/help/waits/ASYNC_NETWORK_IO
PAGEIOLATCH_EX       3752.04   3704.58      47.47       667718     2.21        0.0056    0.0055   0.0001     https://www.sqlskills.com/help/waits/PAGEIOLATCH_EX
LATCH_EX             3128.80   2618.06      510.74      1137880    1.85        0.0027    0.0023   0.0004     https://www.sqlskills.com/help/waits/LATCH_EX
LCK_M_IS             2796.12   2780.73      15.39       1096       1.65        2.5512    2.5372   0.0140     https://www.sqlskills.com/help/waits/LCK_M_IS                

(11 rows affected)

Let us say, I was recording the results every 5 seconds, which I do, this data is pretty useless (because of the cumulative nature)!

I scheduled the collection using the method I already blogged about.

https://sqljana.wordpress.com/2018/01/10/powershell-export-querytosqltable-export-all-of-glenn-berrys-dmvs-sp_whoisactive-sp_blitz-any-of-your-own-queries-to-sql-server-tables/

With a few additional collection related columns added, it looks like this:


CaptureSetID         CaptureSetLine CaptureInstance    CaptureDB  CaptureDate                 WaitType              Wait_S         Resource_S     Signal_S     WaitCount  Percentage  AvgWait_S  AvgRes_S   AvgSig_S  Help/Info URL
-------------------- -------------- ------------------ ---------- --------------------------- --------------------- -------------- -------------- ------------ ---------- ----------- ---------- ---------- -------------------------------------------------------------------
20180403080300       1              MyHost\MyInst01    master     2018-04-03 08:03:11.1929721 BACKUPIO              705006.46000   704915.23000   91.23000     2876876    37.86000    0.24510    0.24500    0.00000   https://www.sqlskills.com/help/waits/BACKUPIO
20180403080300       1              MyHost\MyInst02    master     2018-04-03 08:03:33.0743938 CXPACKET              2940080.46000  2886831.36000  53249.10000  50827248   49.50000    0.05780    0.05680    0.00100   https://www.sqlskills.com/help/waits/CXPACKET
20180403080300       1              MyHost\MyInst03    master     2018-04-03 08:03:55.1837616 BACKUPIO              681128.60000   681095.14000   33.47000     493116     42.70000    1.38130    1.38120    0.00010   https://www.sqlskills.com/help/waits/BACKUPIO
20180403080300       1              MyHost\MyInst04    master     2018-04-03 08:04:15.1897426 BACKUPIO              643736.30000   643731.51000   4.79000      62013      49.46000    10.38070   10.38060   0.00010   https://www.sqlskills.com/help/waits/BACKUPIO
20180403080300       2              MyHost\MyInst01    master     2018-04-03 08:03:11.1949855 BACKUPTHREAD          575383.30000   575382.97000   0.33000      2678       30.90000    214.85560  214.85550  0.00010   https://www.sqlskills.com/help/waits/BACKUPTHREAD
20180403080300       2              MyHost\MyInst02    master     2018-04-03 08:03:33.0754266 BACKUPIO              1266207.89000  1266154.55000  53.34000     479336     21.32000    2.64160    2.64150    0.00010   https://www.sqlskills.com/help/waits/BACKUPIO
20180403080300       2              MyHost\MyInst03    master     2018-04-03 08:03:55.1857702 BACKUPTHREAD          649904.40000   649903.48000   0.92000      6786       40.75000    95.77140   95.77120   0.00010   https://www.sqlskills.com/help/waits/BACKUPTHREAD
20180403080300       2              MyHost\MyInst04    master     2018-04-03 08:04:15.1907440 BACKUPTHREAD          641486.63000   641485.60000   1.03000      9000       49.29000    71.27630   71.27620   0.00010   https://www.sqlskills.com/help/waits/BACKUPTHREAD
20180403080300       3              MyHost\MyInst01    master     2018-04-03 08:03:11.1959796 BACKUPBUFFER          164609.00000   163585.17000   1023.83000   11992672   8.84000     0.01370    0.01360    0.00010   https://www.sqlskills.com/help/waits/BACKUPBUFFER
20180403080300       3              MyHost\MyInst02    master     2018-04-03 08:03:33.0764331 BACKUPTHREAD          1233437.60000  1233435.99000  1.61000      8938       20.77000    137.99930  137.99910  0.00020   https://www.sqlskills.com/help/waits/BACKUPTHREAD
20180403080300       3              MyHost\MyInst03    master     2018-04-03 08:03:55.1887777 CXPACKET              112756.87000   103714.22000   9042.66000   10464996   7.07000     0.01080    0.00990    0.00090   https://www.sqlskills.com/help/waits/CXPACKET
20180403080300       4              MyHost\MyInst01    master     2018-04-03 08:03:11.1959796 ASYNC_IO_COMPLETION   149335.30000   149335.27000   0.03000      133        8.02000     1122.82180 1122.82160 0.00020   https://www.sqlskills.com/help/waits/ASYNC_IO_COMPLETION
20180403080300       4              MyHost\MyInst02    master     2018-04-03 08:03:33.0774024 PAGEIOLATCH_SH        161344.74000   158575.77000   2768.97000   45762856   2.72000     0.00350    0.00350    0.00010   https://www.sqlskills.com/help/waits/PAGEIOLATCH_SH
20180403080300       4              MyHost\MyInst03    master     2018-04-03 08:03:55.1907830 BACKUPBUFFER          35434.30000    35042.90000    391.40000    1317652    2.22000     0.02690    0.02660    0.00030   https://www.sqlskills.com/help/waits/BACKUPBUFFER
20180403080300       5              MyHost\MyInst01    master     2018-04-03 08:03:11.1989926 CXPACKET              126486.27000   107031.54000   19454.73000  30498347   6.79000     0.00410    0.00350    0.00060   https://www.sqlskills.com/help/waits/CXPACKET
20180403080300       5              MyHost\MyInst02    master     2018-04-03 08:03:33.0774024 PAGEIOLATCH_EX        91936.24000    91458.77000    477.47000    29816143   1.55000     0.00310    0.00310    0.00000   https://www.sqlskills.com/help/waits/PAGEIOLATCH_EX
20180403080300       5              MyHost\MyInst03    master     2018-04-03 08:03:55.1917829 ASYNC_IO_COMPLETION   33942.18000    33942.08000    0.10000      231        2.13000     146.93580  146.93540  0.00040   https://www.sqlskills.com/help/waits/ASYNC_IO_COMPLETION
20180403080300       6              MyHost\MyInst01    master     2018-04-03 08:03:11.1999648 PAGEIOLATCH_SH        47167.55000    46897.61000    269.94000    12877141   2.53000     0.00370    0.00360    0.00000   https://www.sqlskills.com/help/waits/PAGEIOLATCH_SH
20180403080300       6              MyHost\MyInst03    master     2018-04-03 08:03:55.1937883 PAGEIOLATCH_SH        28045.20000    27511.73000    533.48000    4153090    1.76000     0.00680    0.00660    0.00010   https://www.sqlskills.com/help/waits/PAGEIOLATCH_SH
20180403080300       7              MyHost\MyInst01    master     2018-04-03 08:03:11.2009665 OLEDB                 29010.44000    29010.44000    0.00000      750259205  1.56000     0.00000    0.00000    0.00000   https://www.sqlskills.com/help/waits/OLEDB
20180403080826       1              MyHost\MyInst01    master     2018-04-03 08:08:37.0393672 BACKUPIO              705006.46000   704915.23000   91.23000     2876876    37.86000    0.24510    0.24500    0.00000   https://www.sqlskills.com/help/waits/BACKUPIO
20180403080826       2              MyHost\MyInst01    master     2018-04-03 08:08:37.0424049 BACKUPTHREAD          575383.30000   575382.97000   0.33000      2678       30.90000    214.85560  214.85550  0.00010   https://www.sqlskills.com/help/waits/BACKUPTHREAD
20180403080826       3              MyHost\MyInst01    master     2018-04-03 08:08:37.0434104 BACKUPBUFFER          164609.00000   163585.17000   1023.83000   11992672   8.84000     0.01370    0.01360    0.00010   https://www.sqlskills.com/help/waits/BACKUPBUFFER
20180403080826       4              MyHost\MyInst01    master     2018-04-03 08:08:37.0444077 ASYNC_IO_COMPLETION   149335.30000   149335.27000   0.03000      133        8.02000     1122.82180 1122.82160 0.00020   https://www.sqlskills.com/help/waits/ASYNC_IO_COMPLETION
20180403080826       5              MyHost\MyInst01    master     2018-04-03 08:08:37.0463854 CXPACKET              126486.27000   107031.54000   19454.73000  30498356   6.79000     0.00410    0.00350    0.00060   https://www.sqlskills.com/help/waits/CXPACKET
20180403080826       6              MyHost\MyInst01    master     2018-04-03 08:08:37.0483907 PAGEIOLATCH_SH        47197.13000    46927.15000    269.97000    12880473   2.53000     0.00370    0.00360    0.00000   https://www.sqlskills.com/help/waits/PAGEIOLATCH_SH
20180403080826       7              MyHost\MyInst01    master     2018-04-03 08:08:37.0493932 OLEDB                 29010.44000    29010.44000    0.00000      750259205  1.56000     0.00000    0.00000    0.00000   https://www.sqlskills.com/help/waits/OLEDB
20180403081326       1              MyHost\MyInst01    master     2018-04-03 08:13:38.8994288 BACKUPIO              705006.46000   704915.23000   91.23000     2876876    37.86000    0.24510    0.24500    0.00000   https://www.sqlskills.com/help/waits/BACKUPIO

 

Deltas using Windowing functions

What we need is the delta values for each capture compared to the previous capture. To do so, we have to write code such as this. Basically, we PARTITION BY CaptureInstance, CaptureDB, WaitType ORDER BY CaptureSetID to segment the data for comparison. Then we self-join on the previous row to current row and get the delta – (cur.[Wait_S]-prev.[Wait_S]) AS Wait_S_Delta

WITH prev
AS
(
	SELECT
		LEAD(CaptureSetID) OVER (PARTITION BY CaptureInstance, CaptureDB, WaitType ORDER BY CaptureSetID) NextCaptureSetID,
		prev.*
	FROM OS_Wait_Stats AS prev
)
SELECT cur.[CaptureSetID]
      ,cur.[CaptureSetLine]
      ,cur.[CaptureInstance]
      ,cur.[CaptureDB]
      ,cur.[CaptureDate]
	  ,prev.[CaptureDate] AS CaptureDate_Prev
      ,cur.[WaitType]
	  ,prev.[WaitType] AS WaitTypePrev
      ,cur.[Wait_S]
	  ,prev.[Wait_S] AS Wait_S_Prev
	  ,(cur.[Wait_S]-prev.[Wait_S]) AS Wait_S_Delta
      ,cur.[Resource_S]
	  ,prev.[Resource_S] AS Resource_S_Prev
	  ,(cur.[Resource_S]-prev.[Resource_S]) AS Resource_S_Delta
      ,cur.[Signal_S]
	  ,prev.[Signal_S] AS Signal_S_Prev
	  ,(cur.[Signal_S]-prev.[Signal_S]) AS Signal_S_Delta
      ,cur.[WaitCount]
	  ,prev.[WaitCount] AS WaitCount_Prev
	  ,(cur.[WaitCount]-prev.[WaitCount]) AS WaitCount_Delta
      ,cur.[Percentage]
	  ,prev.[Percentage] AS Percentage_Prev
	  ,(cur.[Percentage]-prev.[Percentage]) AS Percentage_Delta
      ,cur.[AvgWait_S]
	  ,prev.[AvgWait_S] AS AvgWait_S_Prev
	  ,(cur.[AvgWait_S]-prev.[AvgWait_S]) AS AvgWait_S_Delta
      ,cur.[AvgRes_S]
	  ,prev.[AvgRes_S] AS AvgRes_S_Prev
	  ,(cur.[AvgRes_S]-prev.[AvgRes_S]) AS AvgRes_S_Delta
      ,cur.[AvgSig_S]
	  ,prev.[AvgSig_S] AS AvgSig_S_Prev
	  ,(cur.[AvgSig_S]-prev.[AvgSig_S]) AS AvgSig_S_Delta
      ,cur.[Help/Info URL]
	  ,prev.[Help/Info URL] AS [Help/Info URL_Prev]
FROM OS_Wait_Stats AS cur
	INNER JOIN prev
		ON cur.CaptureInstance = prev.CaptureInstance
			AND cur.CaptureDB = prev.CaptureDB
			AND cur.WaitType = prev.WaitType
			AND cur.CaptureSetID =  prev.NextCaptureSetID

It is not a big deal to code it except that it becomes a tedious task if you have to do it over and over for various views. The concept is the same for all views however what we PARTITION BY and ORDER BY might change slightly. I saw the potential for automating the creation of the above SQL and went for it.

Delta SQL Generation – SQL Output Mode

What if I told you that the whole view could be generated by a simple call such as this:

DECLARE @SQLOut NVARCHAR(4000);
exec [dbo].[usp_Make_Delta_View]
            @SelectColumns='*',
            @PartitionByColumns='CaptureInstance, CaptureDB, WaitType',
            @PartitionOrderByColumns='CaptureSetId',
            @TableName='dbo.OS_Wait_Stats',
            @OutputResultsOrSQL='SQL',
            @SQLOutput = @SQLOut OUTPUT;
SELECT @SQLOut;

The above call generates the SQL which recognizes all numeric/date columns and performs deltas. The date column deltas are in minutes. I guess that can also be parameterized if you wish so. You can create a view if you want.

Delta Results Generation – Actual Output Mode

The code below actually generates the SQL delta columns and runs it to give you the results straight-away!

exec [dbo].[usp_Make_Delta_View]
            @SelectColumns='*',
            @PartitionByColumns='CaptureInstance, CaptureDB, WaitType',
            @PartitionOrderByColumns='CaptureSetId',
            @TableName='dbo.OS_Wait_Stats',
            @OutputResultsOrSQL='Results'

It took me a while to get it quite right but it is a very effective genericization (not an actual word!) of a simple concept.

Delta Results To Output Table:

--To return the SQL that inserts into a results table named OS_Wait_Stats_With_Deltas...Just EXEC the returned SQL!
DECLARE @SQLOutwInto NVARCHAR(4000);
exec [dbo].[usp_Make_Delta_View]
            @SelectColumns='*',
            @PartitionByColumns='CaptureInstance, CaptureDB, WaitType',
            @PartitionOrderByColumns='CaptureSetId',
            @TableName='dbo.OS_Wait_Stats',
            @OutputResultsOrSQL='SQL',
	    @OutputResultsIntoTableName = 'OS_Wait_Stats_With_Deltas',
            @SQLOutput = @SQLOutwInto OUTPUT;

EXECUTE sp_ExecuteSQL @SQLOutwInto;

SELECT * FROM OS_Wait_Stats_With_Deltas;

DROP TABLE OS_Wait_Stats_With_Deltas;

The TSQL code

The code is not very hard to understand. Please feel free to change it as you see fit

SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[usp_Make_Delta_View]
    (
        @SelectColumns VARCHAR(255),
        @PartitionByColumns VARCHAR(255),
        @PartitionOrderByColumns VARCHAR(255),
		@DatabaseName VARCHAR(128) = DB_NAME,		--For temporary tables, pass in - 'tempdb'
        @TableName VARCHAR(128),					--For temporary tables, pass in the format - 'tempdb.dbo.#temp'
        @OutputResultsOrSQL VARCHAR(10) = 'Results',
		@OutputResultsIntoTableName VARCHAR(100) = NULL,
        @SQLOutput VARCHAR(MAX) = NULL OUT
    )
AS
BEGIN

	/*
	v1.0  - Apr 05, 2018. Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com]

	----------
	--Usage Examples
	----------
	--To return the actual DELTA results!
    exec [dbo].[usp_Make_Delta_View]
                @SelectColumns='*',
                @PartitionByColumns='CaptureInstance, CaptureDB, WaitType',
                @PartitionOrderByColumns='CaptureSetId',
                @TableName='dbo.OS_Wait_Stats',
                @OutputResultsOrSQL='Results'

	--To return the DELTA results as a SQL statement!
	DECLARE @SQLOut NVARCHAR(4000);
    exec [dbo].[usp_Make_Delta_View]
                @SelectColumns='*',
                @PartitionByColumns='CaptureInstance, CaptureDB, WaitType',
                @PartitionOrderByColumns='CaptureSetId',
                @TableName='dbo.OS_Wait_Stats',
                @OutputResultsOrSQL='SQL',
                @SQLOutput = @SQLOut OUTPUT;
    SELECT @SQLOut;

	--To return the SQL that inserts into a results table named OS_Wait_Stats_With_Deltas...Just EXEC the returned SQL!
	DECLARE @SQLOutwInto NVARCHAR(4000);
	exec [dbo].[usp_Make_Delta_View]
				@SelectColumns='*',
				@PartitionByColumns='CaptureInstance, CaptureDB, WaitType',
				@PartitionOrderByColumns='CaptureSetId',
				@TableName='dbo.OS_Wait_Stats',
				@OutputResultsOrSQL='SQL',
				@OutputResultsIntoTableName = 'OS_Wait_Stats_With_Deltas',
				@SQLOutput = @SQLOutwInto OUTPUT;

	EXECUTE sp_ExecuteSQL @SQLOutwInto;
	SELECT * FROM OS_Wait_Stats_With_Deltas;
	DROP TABLE OS_Wait_Stats_With_Deltas;
	*/
<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
	--Below is the illustration of what the procedure does
	-- ..an example of the DELTA we want to generate (for all the numeric columns)

	/*
	--
	--This is the actual SQL that has columns which have cumulative values
	SELECT TOP (1000) [CaptureSetID]
      ,[CaptureSetLine]
      ,[CaptureInstance]
      ,[CaptureDB]
      ,[CaptureDate]
      ,[WaitType]
      ,[Wait_S]
      ,[Resource_S]
      ,[Signal_S]
      ,[WaitCount]
      ,[Percentage]
      ,[AvgWait_S]
      ,[AvgRes_S]
      ,[AvgSig_S]
      ,[Help/Info URL]
	FROM [PowerMon].[dbo].[OS_Wait_Stats]

	--It needs to be converted to add columns so that the current value can be compared to
	--	previous value to get a delta for the time period
	--The SQL to get the Delta looks like this
	--  (which is what we want to generate with the proc!)

	WITH ranked
	AS
	(
		SELECT
			DENSE_RANK() OVER (ORDER BY CaptureInstance, CaptureDB, WaitType) as grp,
			RANK() OVER (PARTITION BY CaptureInstance, CaptureDB, WaitType ORDER BY CaptureSetId) as grprnk,
			a.*
		FROM OS_Wait_Stats AS a
	)
	SELECT cur.[CaptureSetID]
		  ,cur.[CaptureSetLine]
		  ,cur.[CaptureInstance]
		  ,cur.[CaptureDB]
		  ,cur.[CaptureDate]
		  ,prev.[CaptureDate] AS CaptureDate_Prev
		  ,cur.[WaitType]
		  ,prev.[WaitType] AS WaitTypePrev
		  ,cur.[Wait_S]
		  ,prev.[Wait_S] AS Wait_S_Prev
		  ,(cur.[Wait_S]-prev.[Wait_S]) AS Wait_S_Delta
		  ,cur.[Resource_S]
		  ,prev.[Resource_S] AS Resource_S_Prev
		  ,(cur.[Resource_S]-prev.[Resource_S]) AS Resource_S_Delta
		  ,cur.[Signal_S]
		  ,prev.[Signal_S] AS Signal_S_Prev
		  ,(cur.[Signal_S]-prev.[Signal_S]) AS Signal_S_Delta
		  ,cur.[WaitCount]
		  ,prev.[WaitCount] AS WaitCount_Prev
		  ,(cur.[WaitCount]-prev.[WaitCount]) AS WaitCount_Delta
		  ,cur.[Percentage]
		  ,prev.[Percentage] AS Percentage_Prev
		  ,(cur.[Percentage]-prev.[Percentage]) AS Percentage_Delta
		  ,cur.[AvgWait_S]
		  ,prev.[AvgWait_S] AS AvgWait_S_Prev
		  ,(cur.[AvgWait_S]-prev.[AvgWait_S]) AS AvgWait_S_Delta
		  ,cur.[AvgRes_S]
		  ,prev.[AvgRes_S] AS AvgRes_S_Prev
		  ,(cur.[AvgRes_S]-prev.[AvgRes_S]) AS AvgRes_S_Delta
		  ,cur.[AvgSig_S]
		  ,prev.[AvgSig_S] AS AvgSig_S_Prev
		  ,(cur.[AvgSig_S]-prev.[AvgSig_S]) AS AvgSig_S_Delta
		  ,cur.[Help/Info URL]
		  ,prev.[Help/Info URL] AS [Help/Info URL_Prev]
	FROM ranked AS cur
		INNER JOIN ranked AS prev
			ON cur.grp = prev.grp
				AND cur.grprnk = (prev.grprnk + 1)
	*/

    DECLARE @DeltaSQL NVARCHAR(MAX),
            @SQL NVARCHAR(MAX),
			@ColumnsCSV NVARCHAR(MAX),
            @OutputResultsIntoTableSQL NVARCHAR(MAX);

    --Create the SQL that will produce the delta results
    SET @DeltaSQL = 'WITH ranked ' + CHAR(13) +
					'	AS ' + CHAR(13) +
					'	( ' + CHAR(13) +
					'		SELECT  ' + CHAR(13) +
					'			DENSE_RANK() OVER (ORDER BY ' + @PartitionByColumns + ') as grp, ' + CHAR(13) +
					'			RANK() OVER (PARTITION BY ' + @PartitionByColumns + ' ORDER BY ' + @PartitionOrderByColumns +') as grprnk, ' + CHAR(13) +
					'			a.* ' + CHAR(13) +
					'		FROM  ( ' + CHAR(13) +
					'				SELECT ' + @SelectColumns + ' FROM '+@TableName+' AS innr ' + CHAR(13) +
					'				) a ' + CHAR(13) +
					'	) ' + CHAR(13) +
					'SELECT  ' + CHAR(13) +
					' ' + CHAR(13) +
					'FROM ranked AS cur ' + CHAR(13) +
					'	INNER JOIN ranked AS prev ' + CHAR(13) +
					'		ON cur.grp = prev.grp ' + CHAR(13) +
					'			AND cur.grprnk = (prev.grprnk + 1) ' + CHAR(13) +
					'ORDER BY ' + @PartitionByColumns + ',' + @PartitionOrderByColumns 

    PRINT @DeltaSQL;
	PRINT '-------------------------------------------';

	SET @SQL = "
		--Get the list of columns with their datatypes for the table
		--	so that we can do deltas on the numeric columns +
		--	sensible deltas on the datetime columns
		SELECT
			/*
			c.column_id,
			c.name AS ColName,
			QUOTENAME(c.name + '_Prev') AS ColumnNamewSuffix,
			t.Name AS DataType,
			c.max_length AS MaxLength,
			c.precision ,
			c.scale ,
			c.is_nullable,
			ISNULL(i.is_primary_key, 0) AS PrimaryKey,
			CASE WHEN (c.precision &gt; 0
					AND t.name NOT LIKE 'datetime%'
					AND t.name NOT LIKE 'bit%')
				THEN 1
				ELSE 0
			END IsNumber,
			*/
			CASE
				--This is for numbers
				----------------------
				WHEN (c.precision &gt; 0
					AND t.name NOT LIKE 'datetime%'
					AND t.name NOT LIKE 'bit%')
				THEN
					'cur.' + QUOTENAME(c.name) + ', ' +
						'prev.' + QUOTENAME(c.name) + ' AS [' + c.name + '_Prev], ' +
						'(cur.' + QUOTENAME(c.name) + ' - prev.' + QUOTENAME(c.name) + ') AS [' + c.name + '_Delta]'
				--This is for dates
				----------------------
				WHEN (c.precision &gt; 0
					AND t.name LIKE 'datetime%')
				THEN
					'cur.' + QUOTENAME(c.name) + ', ' +
						'prev.' + QUOTENAME(c.name) + ' AS [' + c.name + '_Prev], ' +
						'DATEDIFF(MINUTE, prev.' + QUOTENAME(c.name) + ', cur.' + QUOTENAME(c.name) + ') AS [' + c.name + '_DeltaMins]'
				--This is for non-numbers/non-dates
				----------------------
				ELSE
					'cur.' + QUOTENAME(c.name)
			END AS Cols
		FROM
			sys.columns c
		INNER JOIN
			sys.types t ON c.user_type_id = t.user_type_id
		/*
		--Messes-up when table has indexes
		LEFT OUTER JOIN
			sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
		LEFT OUTER JOIN
			sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
		*/
		WHERE
			1=1
			AND c.object_id = OBJECT_ID('" + @TableName + "')
		ORDER BY
			c.column_id"

	PRINT @SQL
	PRINT '-------------------------------------------';

	CREATE TABLE #Temp(Cols NVARCHAR(1024));
    INSERT INTO #Temp EXEC SP_EXECUTESQL  @SQL;

	--https://www.sqlmatters.com/Articles/Converting%20row%20values%20in%20a%20table%20to%20a%20single%20concatenated%20string.aspx
	SELECT TOP 1 @ColumnsCSV = STUFF((SELECT ',' + Cols
            FROM #Temp
            FOR XML PATH('')) ,1,1,'')
    DROP TABLE #Temp;

	--Should the results be output to a table?
	SELECT @OutputResultsIntoTableSQL = CASE WHEN @OutputResultsIntoTableName IS NULL THEN '' ELSE (' INTO ' + @OutputResultsIntoTableName + ' ') END;

    --Create the SQL that will produce the results
    SET @DeltaSQL = REPLACE(@DeltaSQL, '', @ColumnsCSV);
	SET @DeltaSQL = REPLACE(@DeltaSQL, '', @OutputResultsIntoTableSQL);
    PRINT @DeltaSQL;

    --Return either the SQL or the actual results!
    IF (@OutputResultsOrSQL = 'SQL')
        SELECT @SQLOutput = @DeltaSQL;
    ELSE
        EXEC SP_EXECUTESQL @DeltaSQL;
END;
GO

SET QUOTED_IDENTIFIER ON
GO

PowerBI analysis:

I am not a PowerBI expert but the tool is easy to use. We just need to drag and drop a few things to get a meaningful output/visualization in minutes! Using the procedure above, I created a view which acted as the source of the PowerBI report.

With the delta columns created, my analysis of the data is a breeze with PowerBI:

OSWaitStats

I can clearly see what my instance was waiting on and when. This was based on a light-weight capture in 5 minute intervals.

I could do the same thing for other things like file read/writes using sys.dm_io_virtual_file_stats

FileReadWrits

The possibilities suddenly open up quite a bit without actually have to do the delta related work!

Leave a comment