I Didn't Know it Could Do That! : Enabling Additional Performance Counters
A question customers frequently ask our support and professional services teams is: "How do I get Performance Advisor to collect performance counter <X>?"
This is something that's quite easy to do, although there are a few things a DBA needs to consider before starting down this path:
- Why do you want this new counter – or put another way, what information does this counter provide that can't be derived from counters SQL Sentry already collects?
- Any alerting you wish to put in place for this new counter will require use of custom conditions.
- The new counter(s) cannot be displayed on the Performance Advisor dashboard.
- Enabling additional counters is a global change, which will add to the size of the SQL Sentry database.
Additionally, the customization I am showing you in this post is not officially supported, meaning troubleshooting and resolving any issues you may encounter is outside the scope of your SQL Sentry support agreement, and future product upgrades could reset your change back to default settings.
With those caveats in mind, let's move ahead with some background and an example.
Enabling collection of additional performance counters is accomplished by making a change directly to the SQL Sentry database, rather than through the SQL Sentry client. The primary tables that define performance counters and manage performance counter data collection are:
As of SQL Sentry v8.4, the PerformanceAnalysisCounter table contains 1837 counters that can be collected from Windows, SQL Server, and Analysis Services. Run this query to see the full list, ordered by counter category and counter name:
SELECT pacc.CategoryResourceName AS Category, pac.CounterResourceName AS Counter FROM dbo.PerformanceAnalysisCounter AS pac INNER JOIN dbo.PerformanceAnalysisCounterCategory AS pacc ON pac.PerformanceAnalysisCounterCategoryID = pacc.ID ORDER BY pacc.CategoryResourceName, pac.CounterResourceName;
As Jason explained in his post, Waits are counters in SQL Sentry. This query returns the set of non-wait counters collected by default:
SELECT pacc.CategoryResourceName AS Category, pac.CounterResourceName AS Counter, pac.ID, pasi.Name AS SampleInterval, pasi.IntervalInTicks/10000000 AS IntervalInSeconds FROM dbo.PerformanceAnalysisCounter AS pac INNER JOIN dbo.PerformanceAnalysisCounterCategory AS pacc ON pac.PerformanceAnalysisCounterCategoryID = pacc.ID LEFT OUTER JOIN dbo.PerformanceAnalysisSampleInterval AS pasi ON pac.PerformanceAnalysisSampleIntervalID = pasi.ID WHERE pac.PerformanceAnalysisSampleIntervalID > 0 AND pacc.ID <> 29 --SQLPERF:WAITSTATS Category ID AND pacc.CategoryResourceName NOT LIKE N'%SSAS%' ORDER BY pacc.CategoryResourceName, pac.CounterResourceName;
Counters enabled for collection must have a valid non-zero value in the PerformanceAnalysisSampleIntervalID column of the PerformanceAnalysisCounter table. (A value of 0 means collection is disabled for a counter.)
Valid values are defined in the PerformanceAnalysisSampleInterval table. This query returns the sample interval id, name, and collection interval value in seconds:
SELECT ID, Name, IntervalInTicks/10000000 AS IntervalInSeconds FROM dbo.PerformanceAnalysisSampleInterval ORDER BY ID;
With the above background info to guide us, we are going to enable a commonly requested performance counter – Page Splits/sec.
Note: For most SQL Servers, this counter is of limited value as a performance monitoring and troubleshooting metric, which is why we don't collect it by default. There are cases where it can be useful however. Bradley Ball wrote an excellent blog post describing page splits and when collecting them can be important, entitled How to Find Bad Page Splits. Jonathan Kehayias also shows a different approach in his post, Tracking Problematic Pages Splits in SQL Server 2012 Extended Events – No Really This Time!
First, we need the ID for this counter:
SELECT ID, CounterResourceName FROM dbo.PerformanceAnalysisCounter WHERE CounterResourceName = N'PAGE_SPLITS_PER_SEC'; -- Result: ID CounterResourceName ------ ------------------- 119 PAGE_SPLITS_PER_SEC
Enabling this counter is simple:
-- CounterID = 119 UPDATE dbo.PerformanceAnalysisCounter SET PerformanceAnalysisSampleIntervalID = 1 WHERE PerformanceAnalysisCounter.ID = 119;
I chose a sample interval of 10 seconds because page splits/sec is a volatile metric. For metrics that are less volatile, choose a different sample interval, using the PerformanceAnalysisSampleInterval as your guide.
To start collecting this counter, restart your SQL Sentry Monitoring Service(s), then verify that collection is occurring by executing this query:
SELECT * FROM dbo.PerformanceAnalysisData WITH (NOLOCK) WHERE PerformanceAnalysisCounterID = 119; -- use the same CounterID as in your UPDATE statement
So, now that SQL Sentry is collecting this new counter, how can you make use of it? Two possibilities come to mind:
- Query the SQL Sentry database directly. Collected performance data is found in the PerformanceAnalysisData table and the related Rollup tables. Again, I refer you to Jason's data mining series for a detailed explanation of how to query these tables, as well as a sample query.
- The Performance Counter History Report and the Performance Counter Date Range Comparison Report allow you to create your own reports using collected performance counters over any date range. Find them in the Reports menu of the SQL Sentry client.