Pulling Top SQL from the SQL Sentry Database

My friend and colleague  (@SQLSaurus) has been cranking out a great blog series on Mining Performance Data from SQL Sentry (Part 1)  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 about Connecting SQL Sentry to a Different Domain . 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:

EventClass Description
-1 / 9 (Our activity - rollups, sp_trace_getdata, waitfor, etc.)
10 RPC:Completed
12 SQL:BatchCompleted
41 SQL:StmtCompleted
45 SP:StmtCompleted
92 Data File Auto Grow
93 Log File Auto Grow
94 Data File Auto Shrink
95 Log File Auto Shrink
148 Deadlock graph

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:

Column Name Data Type
ID bigint
EventSourceConnectionID smallint
EventClass int
HostName nvarchar(128)
ApplicationName nvarchar(128)
DatabaseID int
NormalizedTextMD5 binary(16)
NTUserName nvarchar(128)
LoginName nvarchar(128)
CPU int
Reads bigint
Writes bigint
Duration bigint
SPID int
StartTime datetime
EndTime datetime
NormalizedStartTime datetime
NormalizedEndTime datetime
TimeZoneFactorMinutes int
UtcOffset bigint
TextData nvarchar(max)
DatabaseName nvarchar(128)
IntegerData int
FileName nvarchar(512)
ParentID bigint
NestLevel int
IntegerData2 int
LineNumber int
TransactionID bigint
Offset int
ObjectID int
ObjectName nvarchar(512)
HasPlan bit
HasStatements bit
Error int
HostProcessID int
SessionMemoryKB bigint
TempdbUserKB bigint
TempdbUserKBDealloc bigint
TempdbInternalKB bigint
TempdbInternalKBDealloc bigint
GrantedQueryMemoryKB bigint
DegreeOfParallelism smallint
GrantTime datetime
RequestedMemoryKB bigint
GrantedMemoryKB bigint
RequiredMemoryKB bigint
GroupID int
PoolID int
IdealMemoryKB bigint
IsSmallSemaphore bit

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. Interested in learning more about SQL Sentry? Check out the interactive demo today.

THWACK - Symbolize TM, R, and C