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
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:
|-1 / 9||(Our activity - rollups, sp_trace_getdata, waitfor, etc.)|
|92||Data File Auto Grow|
|93||Log File Auto Grow|
|94||Data File Auto Shrink|
|95||Log File Auto Shrink|
There are other filters that you can run as well, such as
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|
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 email@example.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, and serves as a community moderator for the Database Administrators Stack Exchange. 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.