Pulling Top SQL from the SQL Sentry database

Aaron Bertrand

Published On: April 3, 2015

Categories: SQL Sentry, Data Mining, Performance Analysis 4

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
  -- may need more filters to get one row; see comments below
SELECT * FROM dbo.PerformanceAnalysisTraceData 
  -- 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
  -- (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. Over the coming months you will see more and more investment in our cloud service (cloud.sentryone.com), 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@sentryone.com.

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.