Mining Performance Data from SQL Sentry (Part 6)

jason.hall over 9 years ago 6 minute read time

Introduction

We covered a lot in Mining Performance Data from SQL Sentry (Part 5) , and at the end we were able to start working with SQL Server waits statistics stored in the SQL Sentry repository database.

In this post, since we don’t need to go over a lot of table definitions, I want to focus on the query itself, and go into a bit more detail on some of the things we’re going to be doing in it.

Background

Some time ago, I read an MSSQLTips article from Aaron Bertrand (b|t) on creating pivot columns dynamically in a query. Something that quickly struck me as a neat thing to use this for was on performance data in SQL Sentry.

I’ve used pivot queries quite a bit to pull data from the repository, and even though I find them very useful, they are generally sort of tedious to write, because I have to know ahead of time exactly what counters I want. If I can generate the pivot columns dynamically, I could build a query that would pivot on all the counters in a given category. That’s what we’re going to do here today.

Disk Performance

One of the coolest features of SQL Sentry is the Disk Activity feature. Some of the important information we can get from Disk Activity is really how long IO is taking and how much IO is happening. There are several measurements involved in providing this information, and they all have value in different ways. It’s not wise to single out one value, and ignore the others, so the query we’re going to look at today will show us the AVG for the last 30 minutes for the entire category of physical disk counters that SQL Sentry has collected.

Let’s break away from the format I’ve been using, and start with the query, then pick it apart to see what we’re doing.

DECLARE @cols NVARCHAR(MAX)
     , @sql NVARCHAR(MAX);

SET @cols = N'';

SELECT @cols += N', p.' + QUOTENAME(CounterResourceName)
FROM (
     SELECT p.CounterResourceName
     FROM PerformanceAnalysisCounter AS p
     INNER JOIN PerformanceAnalysisCounterCategory AS c
          ON p.PerformanceAnalysisCounterCategoryID = c.ID
     WHERE c.CategoryResourceName = 'PHYSICALDISK'
          AND p.PerformanceAnalysisSampleIntervalID <> 0
     GROUP BY p.CounterResourceName
     ) AS x;

SET @sql = N'
SELECT FullyQualifiedDomainName,' + STUFF(@cols, 1, 2, '') + '
FROM
(
  SELECT p.CounterResourceName, Device.FullyQualifiedDomainName, d.Value
   FROM dbo.PerformanceAnalysisCounter AS p with (nolock) 
   JOIN dbo.PerformanceAnalysisDataDiskCounter AS d with (nolock) 
   JOIN dbo.Device with (nolock) ON d.DeviceID = dbo.Device.ID
   ON p.ID = d.PerformanceAnalysiscounterID
   WHERE p.CounterResourceName IN (' + STUFF(REPLACE(REPLACE(@cols, ']', ''''), ', p.[', ','''), 1, 1, '') + ') AND dbo.fnConvertDateTimeToTimestamp(dateadd(MINUTE, -30, getdate())) < d.Timestamp
) AS j
PIVOT
(
  AVG(Value) FOR CounterResourceName IN (' + STUFF(REPLACE(@cols, ', p.[', ',['), 1, 1, '') + ')
) AS p;';

PRINT @sql;

EXEC sp_executesql @sql;

The first interesting part is this:

SELECT @cols += N', p.' + QUOTENAME(CounterResourceName)
FROM (
     SELECT p.CounterResourceName
     FROM PerformanceAnalysisCounter AS p
     INNER JOIN PerformanceAnalysisCounterCategory AS c
          ON p.PerformanceAnalysisCounterCategoryID = c.ID
     WHERE c.CategoryResourceName = 'PHYSICALDISK'
          AND p.PerformanceAnalysisSampleIntervalID <> 0
     GROUP BY p.CounterResourceName
     ) AS x;

In this statement we’re querying the counter category table and joining to the counter table so that we can use the counter resource name for our column names. I could also go ahead and involve the tables that let me get clean names for counters, but I’m actually not too worried about that in this case, and I can tell what I have from the resource name as well.

The output is really just setting up the columns variable into the format Aaron used in his dynamic pivot article, so that I can use it later in the creation of the pivot query.

Next, we’re building the query the dynamic SQL:

SET @sql = N'
SELECT FullyQualifiedDomainName,' + STUFF(@cols, 1, 2, '') + '
FROM
(
  SELECT p.CounterResourceName, Device.FullyQualifiedDomainName, d.Value
   FROM dbo.PerformanceAnalysisCounter AS p with (nolock) 
   JOIN dbo.PerformanceAnalysisDataDiskCounter AS d with (nolock) 
   JOIN dbo.Device with (nolock) ON d.DeviceID = dbo.Device.ID
   ON p.ID = d.PerformanceAnalysiscounterID
   WHERE p.CounterResourceName IN (' + STUFF(REPLACE(REPLACE(@cols, ']', ''''), ', p.[', ','''), 1, 1, '') + ') AND dbo.fnConvertDateTimeToTimestamp(dateadd(MINUTE, -30, getdate())) < d.Timestamp
) AS j
PIVOT
(
  AVG(Value) FOR CounterResourceName IN (' + STUFF(REPLACE(@cols, ', p.[', ',['), 1, 1, '') + ')
) AS p;';

I won’t recreate Aaron’s article, so you should read that if you want to know what the how and why of creating the dynamic SQL statement for this, but I will go over what we’re pulling in, and why.

We’re rolling this up by server, so we’re getting the FullyQualifiedDomainName from the Device table. We need the counterResourceName from the counter table, because that is actually what we’re going to pivot for, and of course we need our values from PerformanceAnalysisDataDiskCounter to pivot on.

Note: that we’re not using a data rollup table for this, and if you’ve been following this series, you might remember that I once mentioned disk related detail data being stored in a different table than non-disk related detail data. This means that for this query, our data values will be coming from the PerformanceAnalysisDataDiskCounter table.

Something interesting to point out is this bit right here:

dbo.fnConvertDateTimeToTimestamp(dateadd(MINUTE, -30, getdate()))

This is using a function included in the SQL Sentry database that converts datetime values to Performance Advisor timestamps. There is another function that does the reverse as well. We’re basically just using this to make sure we’re only looking at the values collected within the last 30 minutes.

Finally, I just do a little print, so I can read the query if I want, otherwise I can use sp_executesql to run it:

PRINT @sql;

EXEC sp_executesql @sql;

Results

In my results, I’m looking for the overall average for the last 30 minutes for each counter in the PHYSICALDISK category. This category should give me sec/read, sec/write, read bytes/sec, reads/sec, write bytes/sec, writes/sec.

Mining Performance Data : Results from my PIVOT query for PHYSICALDISK details Results from my PIVOT query for PHYSICALDISK details

I realize the image is a bit small due to the width of the results, but it’s easy enough to run this for yourself, and see what you get.

You should be able to do this for any counter category that has a non-null or non-0 value for PerformanceAnalysisSampleintervalID. Also, remember that if you are working with a counter category that is not related to disk, you will change the data table from PerformanceAnalysisDataDiskCounter to PerformanceAnalysisData or use one of the data rollup tables, which wouldn’t require the use of separate tables.

What's Next?

This is the last thing I already had in mind to show everyone from Performance Advisor, but there is certainly a lot more that we could do. I haven’t shown anything with Top SQL, or indexes, and I haven’t really touched on the Event Manager side at all.

For the next part of this series, I was hoping I might get some suggestions from folks that are reading these posts. What is something we haven’t gone over that you would like to know how to retrieve from the SQL Sentry repository? Just let me know in a comment, and I’ll see about getting that information into a post for you.

Mining Performance Data Series

Part 1: Mining Performance Data from SQL Sentry (Part 1)
Part 2: Mining Performance Data from SQL Sentry (Part 2)
Part 3: Mining Performance Data from SQL Sentry (Part 3)
Part 4: Mining Performance Data from SQL Sentry (Part 4)
Part 5: Mining Performance Data from SQL Sentry (Part 5)
Part 6: Mining Performance Data from SQL Sentry (Part 6) ← You are here.