My friend and colleague @jason.hall (@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'' + name + N'' + system_type_name + N''
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.
