Finding overlapping times with the LAG Function

— by Jeffry Schwartz

Some functions in SQL are a little difficult to use and understand. I was recently using the LAG function to shed some light on a performance issue, so I thought I would show how I used it and the results it produced in a way that is easy to understand.  At one of our clients after evaluating SQL trace data, I noticed that the attention records (caused by problems with the application, connections, etc.) not only occurred around some non-yielding scheduler messages that had been appearing along with timeouts, but also that the attention records were overlapping, i.e., sometimes one began and then others occurred before the first one ended.  I’ve looked at the attention data from other clients and found that not only do they not overlap like this, they don’t last long enough to overlap, meaning that this is a special case.

To show clearly which ones overlap, I decided to use the lag function to compare the end time of an older one with the start time of the current one.  If the start time of the current record occurs before the end time of a previous record, the flag is set.  This way I don’t have to scrutinize the timestamps and compare them manually.  I partition by ServerName because I want to compare all those from the same instance and I want to order them by StartTime so when I compare adjacent records, they are in time order.  You can partition and order by anything you want.

Here is the query, which is generic enough to be understood easily.  I’ve included the timestamps themselves so you can see how it works.

SELECT StartTime, EndTime,

CASE WHEN StartTime < LAG(EndTime,1,0) OVER (PARTITION BY ServerName ORDER BY StartTime) THEN 1 ELSE 0 END AS [Overlap Lag 1],

CASE WHEN StartTime < LAG(EndTime,2,0) OVER (PARTITION BY ServerName ORDER BY StartTime) THEN 1 ELSE 0 END AS [Overlap Lag 2],

CASE WHEN StartTime < LAG(EndTime,3,0) OVER (PARTITION BY ServerName ORDER BY StartTime) THEN 1 ELSE 0 END AS [Overlap Lag 3],

CASE WHEN StartTime < LAG(EndTime,4,0) OVER (PARTITION BY ServerName ORDER BY StartTime) THEN 1 ELSE 0 END AS [Overlap Lag 4],

CASE WHEN StartTime < LAG(EndTime,5,0) OVER (PARTITION BY ServerName ORDER BY StartTime) THEN 1 ELSE 0 END AS [Overlap Lag 5],

LAG(EndTime,1,0) OVER (PARTITION BY ServerName ORDER BY StartTime) AS [lag EndTime1],

LAG(EndTime,2,0) OVER (PARTITION BY ServerName ORDER BY StartTime) AS [lag EndTime2],

LAG(EndTime,3,0) OVER (PARTITION BY ServerName ORDER BY StartTime) AS [lag EndTime3],

LAG(EndTime,4,0) OVER (PARTITION BY ServerName ORDER BY StartTime) AS [lag EndTime4],

LAG(EndTime,5,0) OVER (PARTITION BY ServerName ORDER BY StartTime) AS [lag EndTime5],

Duration / 1000.0 AS [Duration Secs], Duration / 60000.0 AS [Duration Minutes]

–, SPID, ClientProcessID, RowNumber,

–ServerName AS [Instance], LTRIM(ApplicationName) AS [Application],

–DBName AS [Database], HostName, NTUserName, LoginName AS [Login Name]

FROM dbo.SQLTraceReceiver WITH (NOLOCK)

INNER JOIN SQLStaticDatabaseInfo ON DatabaseID = [DBID]

WHERE SQLTraceReceiver.EventClass = 16 — Attention

 

Here is the output:

Blog_20160526_1

A timestamp of 12:00:00 AM really means NULL because the lag is invalid.  I highlighted the overlaps to make them easier to see.  Obviously, you may not need to use lag for time evaluations as I do, but this shows you how to compare values of adjacent records and report something about the comparison without having to display the actual lag values.  I left the [LAG EndTime<n>] columns in the query so you could see it in action, but for the final real query, I actually would not include them and would add the descriptive columns that are commented out back in.

Lag takes a little getting used to, but you once you play with it a bit as in this example, it makes perfect sense.  These functions came out on 2012, so if you are using this version or higher, you are good to go.  Note:  I could have used LEAD to go the other way, i.e., to compare future records using the current record.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Both comments and trackbacks are currently closed.
%d bloggers like this: