SQL Server: Capturing a Query Plan in Real Time

As a DBA a common ticket item is "why is a store procedure running slow?" Usually when trying to figure out a slow proc, it will involve looking at the query plan. One method is to trace the sproc get an example where it's running slow, and execute in a test environment. Unfortunately query plans are heavily dependent on the conditions of the machine their running on and the environment of the data objects. Meaning is their fragmentation, are statistics off, has recompile just taken place ect, so going through motions laid out above might not give you the answer your looking for. Rather an easier way is to grab the actual plan being run on the box that's having the issue. This can easily be done using profiler and selecting the Event Performanceà Showplan xml. Doing this uses a fairly heavy monitoring criterion, and blindly applying without filtering will take you live box to its knees. In order to find procedure query plans in an efficient manner I like find the objectid of the procedure that I'm chasing, then use the criteria in "column filters " under ObjectID. Make sure the "Exclude rows that do not contain values" is check to avoid unneeded information gathering. In addition to the Showplan I'll also collect Stored Procedures àSp:Starting and Sp:Completed to give more detailed information such as amount of read, start and stop time, and also to see if the client timed out with a starting statement being issued without a completed statement.