Pulling Top SQL from the SQL Sentry database

My friend and colleague Jason Hall (@SQLSaurus) has been cranking out a great blog series on Mining Performance Data from SQL Sentry. Last week I received three separate requests about how to query Top SQL data that we’ve collected with Performance Advisor, so I thought I would pitch in and help Jason out. He can’t have all the fun, right?

You might wonder, why wouldn’t one just use the client to look at Top SQL? After all, it has very easy date range selection, filtering, sorting, aggregation, correlation to other contextual performance and event data, and one-click access to the graphical execution plan.

In two of those cases, they were having trouble getting exclusive access to “jump” machines in the data center where they could run the client, and even further trouble getting IT to approve both the local installation of the client as well as the necessary authentication that would have been required (though I have written another post about that, that should help if you have remote domain credentials but no trust). In the third case they just needed quick access to the data to pull into other formats and didn’t have a client readily available.

Anyway, the short answer is that you can get this information from dbo.PerformanceAnalysisTraceData.

The longer answer is that you can use a lot of different filters to narrow down what you pull, since if you have a very busy server, the amount of data can be overwhelming. Here are a few examples of filters:

DECLARE @EventSourceConnectionID INT, @start DATETIME, @end DATETIME;
 
SELECT @EventSourceConnectionID = ID, @start = 'yyyymmdd', @end = 'yyyymmdd'
  FROM dbo.EventSourceConnection
  WHERE ObjectName = N'SERVER\INSTANCE'; 
  -- may need more filters to get one row; see comments below
 
SELECT * FROM dbo.PerformanceAnalysisTraceData 
  WHERE 
  -- duration filter, in milliseconds
  Duration > 10000
 
  -- filter to a specific monitored instance
  AND EventSourceConnectionID = @EventSourceConnectionID 
 
  -- date range, in two potential flavors
 
    -- your time zone
    AND StartTime >= @start AND StartTime <= @end
    AND EndTime >= @start AND EndTime <= @end
 
    -- UTC
    AND NormalizedStartTime >= @start AND NormalizedStartTime <= @end
    AND NormalizedEndTime >= @start AND NormalizedEndTime <= @end
 
  -- host name
  AND HostName IN (N'WORKSTATION1', N'SERVER2')
  -- (similar filters for NTUserName, LoginName)
 
  -- app name
  AND ApplicationName NOT LIKE N'SQL Sentry%'
 
  -- database name / id
  AND DatabaseName IN (N'db1', N'db2', N'db3')
  AND DatabaseID IN (5,6,7)
 
  -- query pattern
  AND TextData LIKE N'%dbo.some_table%'
 
  -- other performance metrics
  AND (Reads > 1000 OR Writes > 100)
 
  -- queries that failed, were cancelled, or timed out:
  AND Error <> 0
 
  -- ignore our activity
  AND EventClass > 9
 
  -- only look at certain trace events, in this case
  -- 41 /* SQL:StmtCompleted */, 45 /* SP:StmtCompleted */
  AND EventClass IN (41, 45)
;

The trace events that you can filter on are:

There are other filters that you can run as well, such as ObjectID and ObjectName, though these aren’t always populated (e.g. for ad hoc SQL). There are also a host of memory-related columns that you can filter on. You probably also want to whittle down the output and not use SELECT * like I did in this example. Do as I say, not as I do. 🙂

Here are all of the columns and data types for dbo.PerformanceAnalysisTraceData, but I’ll confess I don’t have handy formal definitions for the entire set:

As an aside, I did not type that data by hand, nor did it come from memory or transcription. In SQL Server 2012 and above, this is quite simple (and I blogged about this technique back in 2010):

SELECT N'<tr><td>' + name + N'</td><td>' + system_type_name + N'</td></tr>' 
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * 
    FROM dbo.PerformanceAnalysisTraceData', N'', 0);

I hope that is helpful for any of those that are trying to find some Top SQL event but are stuck without client access for some reason. Over the coming months you will see more and more investment in our cloud service (cloud.geeksforgeeks.org), which will hopefully make manual digging and even the client itself less of a necessity in certain scenarios.

If you have any troubles or questions, please shoot me an e-mail at abertrand@geeksforgeeks.org.

Aaron Bertrand

Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com.

Aaron’s blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.