THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Michael Zilberstein

LAG function – practical use and comparison to old syntax

Recently I had to analyze huge trace – 46GB of trc files. Looping over files I loaded them into trace table using fn_trace_gettable function and filters I could use in order to filter out irrelevant data. I ended up with 6.5 million rows table, total of 7.4GB in size. It contained RowNum column which was defined as identity, primary key, clustered. One of the first things I detected was that although time difference between first and last events in the trace was 10 hours, total duration of all sql events was only 4 hours. So the next assumption to check was whether application got stuck - whether we have significant gap between two subsequent events. In the following query I’m looking for events that occurred at least 30 seconds after previous event’s completion.

SQL 2005/2008/2008R2 syntax:

curEvent.RowNum, prevEvent.EndTime AS PrevEventEndTime, curEvent.StartTime AS CurEventStartTime
DATEDIFF(SECOND, prevEvent.EndTime, curEvent.StartTime) AS
dbo.TraceData curEvent
INNER JOIN dbo.TraceData prevEvent ON curEvent.RowNum - 1 =
WHERE DATEDIFF(SECOND, prevEvent.EndTime, curEvent.StartTime) >=


SQL 2012 syntax:

SELECT RowNum, StartTime AS CurEventStartTime, LAG(EndTime, 1, NULL) OVER(ORDER BY RowNum ASC) AS
SELECT RowNum, PrevEventEndTime, CurEventStartTime, DATEDIFF(SECOND, PrevEventEndTime, CurEventStartTime) AS
WHERE DATEDIFF(SECOND, PrevEventEndTime, CurEventStartTime) >=


Here are results (pretty surprising at the first glance, I have to say):

  SQL 2012 SQL 2005/2008/2008R2
Duration 103 seconds 77 seconds
Reads 919,752 1,839,504
CPU 20 seconds 13 seconds

In the old SQL versions we see twice reads – that’s obvious from execution plan since we scan entire table twice and merge join on RowNum column. It seems that in this case Merge Join is so efficient that CPU overheard of segmentation, aggregation and spooling costs more than additional single ordered scan of the table that is already in memory anyway.

I started to write all this in order to demonstrate practical usage of the new function and how we can benefit from it compared to previous SQL Server versions. Results became a complete surprise for me.

Conclusion: always check and recheck yourself!

Published Wednesday, March 14, 2012 10:33 AM by Michael Zilberstein



ypercube said:

Very interesting! You don't mention though the table's indexes. Is (RowNum) the clustered index?

January 22, 2016 2:06 AM
New Comments to this post are disabled
Privacy Statement