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.