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:

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

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!

Justin Randall

Justin (@jh_randall) leads our Professional Services Organization with the perspective of nearly 30 years of IT experience. Professional Services provides customers with mentoring, consulting and training services focused on configuring and tuning SentryOne software, and SQL Server performance troubleshooting and optimization.

Justin’s blog focuses on helping our users become highly valued DBAs who use our software to prevent and solve real-world SQL Server problems while making positive contributions to their organization’s success.