Correlating Data in SQL Trace, Part 2 – in which Johnny learns a valuable lesson

In last week's "Correlating Data in SQL Trace, Part 1", we took a trace to diagnose lock timeouts, and developed a query to correlate lines in the trace: this let us see what code was generating what lock timeouts.

The trick for the query is to use a correlated subquery to find the statement rownumber, thusly (simplified):

SELECT
        [Timeout].* ,
        Statement.*
FROM    Trace AS [Timeout]
        INNER JOIN Trace AS [Statement] ON Statement.spid = [Timeout].spid
               AND Statement.DatabaseID = [Timeout].DatabaseID
               AND statement.rownumber = ( SELECT TOP 1 rownumber
                                  FROM trace
                                  WHERE spid = [Timeout].spid
                                  AND rownumber < [Timeout].rownumber
                                  AND eventclass = 11
                                  ORDER BY rownumber DESC
                                  )
WHERE   [Timeout].EventClass = 189

A little translation: "WHERE EventClass = 189" gives us all our timeouts.  "statement.rownumber = (subquery)" finds the closest (to the timeout) previous RPC:Starting row with the same spid…in other words, the timeout's associated RPC call.

Another use for this same type of correlation query is to find all "RPC starting" with no associated "RPC:complete" in a given timeframe.  Okay, it's a pretty specific need, but it certainly illustrates that this can be a very versatile query.

Instead of correlating timeouts to queries, we want to correlate RPC start with RPC complete, and then display only those RPC:Starting rows with no associated RPC:Complete.

To achieve this, we have to make some changes to our query.

  • For one thing, we need a left outer join; this lets us return rows with NULL matches on the right side (meaning, no RPC:Complete)
  • In the rownumber=(subquery), we change the less than to greater than…before, we found timeouts and then went back in time to find their origin. Here, we're finding RPC:Starting and going forward in time to find RPC:Complete.  
  • We change the subquery's ORDER BY to ASC for the same reason.
  • And of course, change the subquery eventclass = 10, as we're looking to return RPC:Completed.
  • Introducing an outer join to the query adds a new element: the possibility of matching up rows that don't actually go together.  SPIDs get reused…if we keep our current criteria – an RPC:Completed with the same SPID and DBID with a greater rownumber – we're very likely to get false matches.  The way to fix this is to match start times in the subquery.

So our final query looks like this:

SELECT TOP 1
        [RPCStart].rownumber ,
        [RPCStart].objectname ,
        [RPCStart].textdata,
        [RPCStart].loginname ,
        [RPCStart].spid ,
        [RPCStart].starttime ,
        [RPCStart].endtime ,
       
'COMPLETE:  ' AS spacer,
        [RPCComplete].duration ,
        [RPCComplete].rownumber ,
        [RPCComplete].objectname ,
        [RPCComplete].textdata,
        [RPCComplete].loginname ,
        [RPCComplete].spid ,
        [RPCComplete].starttime ,
        [RPCComplete].endtime
FROM    Trace AS [RPCStart]
        LEFT OUTER JOIN Trace AS [RPCComplete]
                  ON [RPCComplete].spid = [RPCStart].spid
                     AND [RPCComplete].DatabaseID = [RPCStart].DatabaseID
                     AND [RPCComplete].rownumber = ( SELECT TOP 1
                     rownumber
                     FROM Trace
                     WHERE spid = [RPCStart].spid
                     AND rownumber > [RPCStart].rownumber
                     AND startTime > [RPCStart]. startTime
                     AND eventclass = 10 — rpc completed
                     ORDER BY rownumber ASC
                     )
WHERE   [RPCStart].EventClass = 11   — rpc starting

What have we learned?  That it's always best to tell the truth, little Johnny.  Oh, and with a little careful restructuring, a good query can be recycled for another, equally cool use.

-Jen McCown, who is really posting this at midnight-thirty local time.

http://www.MidnightDBA.com 

Win a MS Lifecam this month!

Cause we are living in a material world, and you are a material girl!  Free stuff is awesome, and we only have, like, two people who actually read the blog, so odds are good!

Your odds are good this month on winning a Microsoft LifeCam Show! Just email jen@jennifermccown.com with an answer to: What does Sean say about moving schema, and why? There's no minimum wordcount, and I'll even let you know if your answer is off the mark.  How nice am I?  So, so totally nice.

Edit: I should have made clearer that this is a drawing. A drawing!  To make it up to the people who don't get picked out of the hat for their very clever and very good-looking entries, I will have to devise some sort of reward. How about, your choice: a customized MidnighDBA cheat sheet wallpaper, or a feature segment on the MidnightDBA blogcast?  Those are the new consolation prizes that I've just made up, just now.

Speaking of free and awesome, we have two new podcasts in the MidnightDBA Blogcast this week:

Happy days and free stuff,

Jen McCown

http://www.MidnightDBA.com

Book Review: ASP.NET Data Presentation Controls Essentials

This review was written by Sean McCown.

  • Author: Joydip Kanjilal

  • Pages:  256 pages

  • Publisher: Packt Publishing (December 31, 2007)  

This is one of those books that I wish I could use without reading.  What I mean by that is there is absolutely no personality in it at all.  It explains the technology, but doesn’t attempt to be entertaining in the least.  That said, it’s an excellent reference book.  I’ve worked with ASP.NET quite a bit and I’ve even worked with these controls quite a bit and I learned plenty from this book.  So while it’s for those who are beginners with these controls, it’s actually written for experienced programmers.  It never bothers to explain basic programming constructs.  So don’t get this thinking you’ll get to learn how to program.   This book stays on topic as it methodically works its way through each of the controls.  It not only goes through all of the controls, it goes from the basics to relatively advanced applications.  That’s actually one of the things I like best about this title.  For each controls it gives you several applications so it not only teaches you about the different uses of the controls themselves, it also shows you different coding methods you need to make each one of them happen. 

I wouldn’t want to sit down and just read this book from cover to cover.  It is a really good reference though and if I had to get up to speed on any of the ASP.NET controls, this is the book I’d want to have on my shelf.  In fact, I didn’t really know much about a couple of the controls discussed and after going through those chapters I think I’m ready to give them a shot.  This book even covers quite a bit of the material better than MSDN.  I have to qualify that though.  I’m sure MSDN actually covers some of the material just as well if not better, but it can be so hard to sift through everything on the MSDN site that it may as well not be documented at all.  So this is a really good reference that will stay on my shelf, but I won’t be taking it on the plane with me.

Ch.1  Introduction to Data Binding in ASP.NET

As the title says, here you’re going to talk about data binding.  You’ll learn what it is and what it means to you.  It even shows you in simple coding terms what it takes to data bind to a control.  Frankly, it’s a lot easier than I thought.  It also defines the different types of data binding and then shows you how to use the data binding expressions.  There’s extensive source code to show you exactly how and what you need to do.  It then goes into the new data source controls in ASP.NET 2.0 and discusses them each.  There are even full-color pictures to walk you through the process of working with these controls.

Ch. 2  Working with List Controls in ASP.NET

Of course you have to start by defining a list control.  After that it jumps right into working with each of them.  ListBox, DropDownList, CheckBoxList, BulletedList, RadioButtonList are all discussed.  And again, there’s no shortage of code or full-color pictures.  This is a very large chapter though.

Ch. 3  Working with the Repeater Control

Of course this chapter is all about the repeater.  Personally I love this control so I love this chapter.  I’m not going to give a blow by blow of this chapter though.  Instead I’m going to say that I recently had to use a repeater control and couldn’t figure out how to do something I needed to do and after reading through this chapter I was able to do what I needed.  This chapter is full of code and examples that there’s just no way to avoid knowing how to work with a repeater after reading it.  It’s good stuff.  And again, this chapter is pretty big.

Ch. 4  Working with the DataList Control

There just doesn’t seem to be a need for me to give details of all the chapters anymore.  This one is clearly about the DataList and it has all the same features as the other ones:  lots of code samples and pictures.

Ch. 5  Working with the DataGrid Control in ASP.NET

Same as above, right.

Ch. 6  Displaying Views of Data (Part 1)

Ok, so this one isn’t nearly as self-explanatory as the others have been.  Here we’re talking about all things GridView.  It shows you how to do tons of things with the GridView as well as exporting data to Excel or Word.

Ch. 7 Displaying Views of Data (Part 2)

Here you talk about the DetailsView, FormView, and TreeView. 

Ch. 8  Working with LINQ

LINQ basics are covered.  Everything from architecture to data binding, to using it with different controls can be learned.  If you don’t know anything about LINQ then this is where you need to start.

-Sean McCownhttp://www.MidnightDBA.com