I Didn't Know it Could Do That : Extra Performance Counters

I Didn't Know it Could Do That! : Enabling Additional Performance Counters

Introduction

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:

  1. 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?
  2. Any alerting you wish to put in place for this new counter will require use of custom conditions.
  3. The new counter(s) cannot be displayed on the Performance Advisor dashboard.
  4. 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.

The Backstory

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:

  • PerformanceAnalysisCounter
  • PerformanceAnalysisCounterCategory
  • PerformanceAnalysisSampleInterval

Note: For an in-depth explanation of these and other relevant tables, read Jason Hall's (b|t) post: Mining Performance Data from SQL Sentry : Part 5.

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;

An Example

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

Final Thoughts

So, now that SQL Sentry is collecting this new counter, how can you make use of it? Two possibilities come to mind:

  1. 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.
  2. 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.

Happy Monitoring!

Comments ( 0 )

    Leave A Comment

    Your email address will not be published.

    This site uses Akismet to reduce spam. Learn how your comment data is processed.