Home » Blog » LAG-ging Behind [T-SQL Tuesday #029 – Let’s have a SQL Server 2012 party]

LAG-ging Behind [T-SQL Tuesday #029 – Let’s have a SQL Server 2012 party]

2012-04-10 - General, T-SQL Tuesday

T-SQL Tuesday #29 is hosted by Nigel P Sammy (blog|twitter).
This month's topic is "Let's have a SQL Server 2012 party".

Calculating incrementals with the LAG function

Introduction

With the title of this post I was not trying to imply that I am late in discovering SQL Server 2012 features.
Instead I was thinking of one small but very useful new feature that allows combining data from several result set rows into a new row. It is the new LAG function.

The LAG function is best explained with an example:

[sql] SELECT n
INTO #t1
FROM(VALUES(1),(2),(3),(4))X(n);

SELECT n,
LAG(n,1)OVER(ORDER BY n) [LAG(n,1)],
LAG(n,2)OVER(ORDER BY n) [LAG(n,2)] FROM #t1;
[/sql]

This returns the result shown below:

The LAG function takes 2 parameters. The first is the name of the column you are looking for. The second parameter specifies how many rows you want to go back.

Lag falls into the category of the window functions, so you also need to specify the order in which to process the rows. This is done with the OVER(ORDER BY n) clause.

As above result shows, LAG(n, 1) returns the value the column n had in the previous row. Similarly, LAG(n, 2) returns the value the n had two rows ago.

The first parameter can actually be any expression. It gets evaluated in the context of the actual row that is specified by the second parameter. There is also a third parameter that specifies the default value that is returned when the requested row does not exist. When the actual value in the context of the requested row is NULL, NULL is returned even if a default was specified.

Window Spool

In SQL Server versions before SQL 2012, to achieve this behavior you had to do a self-join for each row to go back to. To mimic the above query you have to write something like this:

[sql] WITH A AS
(
SELECT n, ROW_NUMBER() OVER(ORDER BY n) rn
FROM #t1
)
SELECT A0.n, A1.n [LAG(n,1)], A2.n [LAG(n,2)] FROM A A0
LEFT JOIN A A1 ON A0.rn = A1.rn + 1
LEFT JOIN A A2 ON A0.rn = A2.rn + 2;
[/sql]

This does not only look more complex, it also is a lot more work for SQL Server. Below is the execution plan for above JOIN query:

SQL Server has to scan and sort the table three times. It then joins the three streams together using two Hash Join operators. All of these are fairly expensive operations.

The execution plan of the LAG query on the other hand looks like this:

In this query SQL Server utilizes the new Window Spool operator. Each step size used in a LAG function requires its own Window Spool. Using LAG(n,1) and LAG(m,1) with two expressions n and m in the same query requires only one Window Spool operator. Using two different step sizes as in above example requires two Window Spool operators.

The Window Spool operator remembers the value of the expression that was specified in the first parameter of the LAG function for the last few rows – just enough rows to satisfy the LAG requirement.

Remembering a few values is clearly a lot less Work, than scanning and sorting the entire table again.

How long did you wait?

A great use case for this functionality is the analysis of wait times in SQL Server. The sys.dm_os_wait_stats DMV provides accumulated statistics of the time SQL Server spend waiting since its last restart,
broken down by wait type.

A good way to make sense of this information is to capture the wait stats in regular intervals and calculate the delta for each interval. This is now easy with the LAG function:

[sql] IF OBJECT_ID('dbo.WaitStatLog') IS NOT NULL
DROP TABLE dbo.WaitStatLog;

CREATE TABLE dbo.WaitStatLog
(
Id INT NOT NULL
IDENTITY(1, 1)
CONSTRAINT WaitStatLog_PK PRIMARY KEY CLUSTERED ,
CaptDTime DATETIME2
NOT NULL
CONSTRAINT WaitStatLog_CaptDTime_Dflt DEFAULT SYSDATETIME()
);

IF OBJECT_ID('dbo.WaitStatLogDtl') IS NOT NULL
DROP TABLE dbo.WaitStatLogDtl;

CREATE TABLE dbo.WaitStatLogDtl
(
WaitStatLogId INT NOT NULL ,
wait_type NVARCHAR(60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
waiting_tasks_count BIGINT NOT NULL ,
wait_time_ms BIGINT NOT NULL ,
max_wait_time_ms BIGINT NOT NULL ,
signal_wait_time_ms BIGINT NOT NULL ,
CONSTRAINT WaitStatLogDtl_PK PRIMARY KEY CLUSTERED
( WaitStatLogId, wait_type )
);

IF OBJECT_ID('dbo.CaptureWaitStats') IS NOT NULL
DROP PROCEDURE dbo.CaptureWaitStats;
GO
CREATE PROCEDURE dbo.CaptureWaitStats
AS
BEGIN
DECLARE @Id TABLE(Id INT);
INSERT INTO dbo.WaitStatLog OUTPUT(INSERTED.Id) INTO @Id(Id) DEFAULT VALUES;
INSERT INTO dbo.WaitStatLogDtl
SELECT (SELECT Id FROM @Id), *
FROM sys.dm_os_wait_stats;
END
GO
[/sql]

The above SQL script creates two tables and a procedure. The procedure (dbo.CaptureWaitStats) captures the current values from the sys.dm_os_wait_stats DMV into the two tables. You can either call it manually or setup a job to execute it in regular intervals.

After a few values have been collected you can use the following query to calculate the incremental values:

[sql] WITH WaitDelta AS
(
SELECT L.Id ,
L.CaptDTime ,
DATEDIFF(millisecond,LAG(L.CaptDTime, 1) OVER ( PARTITION BY D.wait_type ORDER BY L.Id ),L.CaptDTime) Interval,
D.wait_type ,
D.max_wait_time_ms ,
D.waiting_tasks_count - LAG(D.waiting_tasks_count, 1) OVER ( PARTITION BY D.wait_type ORDER BY L.Id ) waiting_tasks_count,
D.wait_time_ms - LAG(D.wait_time_ms, 1) OVER ( PARTITION BY D.wait_type ORDER BY L.Id ) wait_time_ms,
D.signal_wait_time_ms - LAG(D.signal_wait_time_ms, 1) OVER ( PARTITION BY D.wait_type ORDER BY L.Id ) signal_wait_time_ms
FROM dbo.WaitStatLog L
JOIN dbo.WaitStatLogDtl D ON L.Id = D.WaitStatLogId
),
WaitTop AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Id ORDER BY wait_time_ms DESC) rn
FROM WaitDelta
WHERE waiting_tasks_count > 0
AND wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','SLEEP_BPOOL_FLUSH',
'CXPACKET','DBMIRROR_EVENTS_QUEUE','DBMIRRORING_CMD')
)
SELECT *
FROM WaitTop
WHERE rn<6
ORDER BY Id DESC;
[/sql]

The query uses the LAG function to calculate the delta values. The PARTITION BY wait_type clause makes sure that only values of the same wait type are used to calculate a delta. Because all LAG invocations have the same partitioning and sort order and use the same step size, only a single Window Spool is required by this query.

The query returns only the top five wait types for each interval; this is done using the ROW_NUMBER function. It also filters out a few wait types that happen frequently in a healthy SQL Server installation (see Wait statistics, or please tell me where it hurts for one source of this exclusion list).

Conclusion

SQL Server's new LAG function provides an easy way to calculate incrementals. It makes the actual coding simpler and also significantly reduces the amount of work that SQL Server itself has to do, compared to the old JOIN method.

This is a great tool for analyzing SQL Server performance data that tends to be accessible only in accumulated values.

There is also a new LEAD function available that is basically doing the same thing but allows to access values from rows ahead instead of behind.