Collecting performance counter values from a SQL Azure database


Performance Monitor is a well-known tool for SQL Server diagnostics and troubleshooting. However, when working with SQL Azure, it is obviously not available. That said, in the latest SQL Azure update (V12), the sys.dm_os_performance_counters DMV is now available. This DMV provides the values for all SQL Server (but not Windows) performance counters, and can replace PerfMon in many scenarios.

Below is a script that collects performance counter values for a SQL Azure database and stores them in a table in the same database. I selected a set of counters that are in my view most useful for SQL Azure diagnostics, however the script can be easily modified to collect any other counters, as needed.

I am thankful to Jason Strate (blog) who previously posted a description of different counter types available in sys.dm_os_performance_counters. It was very helpful in writing the script below.


/*
Dimitri Furman (dfurman@microsoft.com)
2015-03-30

DESCRIPTION:
This script provides SQL performance counter values based on data in sys.dm_os_performance_counters DMV.
It is intended to be used for SQL Azure performance monitoring and diagnostic data collection,
where the traditional methods of collecting PerfMon data are not available. Azure SQL V12 is required.

The script will execute for the specified number of iterations, which can be set to a large number for
a quasi-indefinite capture similar to default PerfMon behavior. At the beginning of each iteration, a
snapshot of cumulative counters is taken, followed by a wait interval, and then by a second snapshot.
At that time, counter values are recorded in the dbo.perf_counter_log table, and a new iteration starts.

This script is partially based on http://www.jasonstrate.com/2012/11/querying-performance-counters-in-sql-server/,
which provides an overview of different counter types available in sys.dm_os_performance_counters,
and a description of the approach to collect and interpret performance counter data provided by that DMV.

USAGE:
1. Use Results To Grid option in SSMS.
2. Set the @SnapshotIntervalSeconds and @MaxIterationCount parameters. Optionally, edit the script to define the counters to be collected.
3. Run the script.
4. Collected data will be found in dbo.perf_counter_log table in the current database.

*/

-- External parameters
DECLARE @SnapshotIntervalSeconds int = 10; -- The interval between first and second snapshot during each iteration
DECLARE @MaxIterationCount int = 100000; -- The number of iterations. Use a large number for indefinite capture duration.
DECLARE @SourceCounter table (
                            
object_name nvarchar(128) not null,
                            
counter_name nvarchar(128) not null,
                            
instance_name nvarchar(128) not null,
                            
base_counter_name nvarchar(128) null,
                            
PRIMARY KEY (object_name, counter_name, instance_name)
                             );
-- The set of collected counters, to be defined below

-- Internal variables
DECLARE @Delay char(8);
DECLARE @IterationNumber int = 1;
DECLARE @FirstSnapshotCounter table (
                                    
object_name nchar(128) not null,
                                    
counter_name nchar(128) not null,
                                    
instance_name nchar(128) not null,
                                    
cntr_value bigint not null,
                                    
base_cntr_value bigint null
                                    );

IF NOT (@SnapshotIntervalSeconds BETWEEN 1 AND 86399) -- 1 second to 23h 59m 59s
    
THROW 50001, 'Snapshot interval duration is outside of supported range.', 1;

SET NOCOUNT ON;

-- Define the counters to be collected. Edit the statement below to add/remove counters as needed.
-- Three special cases for instance_name:
-- 1. <guid> matches any instance name that starts with a GUID
-- 2. <Slo*> matches any instance name that starts with "Slo" (Service Level Objective, SLO)
-- 3. <* !_Total> matches any instance name other than "_Total"
INSERT INTO @SourceCounter (object_name, counter_name, instance_name, base_counter_name)
SELECT 'Access Methods' AS object_name, 'Forwarded Records/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Access Methods' AS object_name, 'Full Scans/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Access Methods' AS object_name, 'Page Splits/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Access Methods' AS object_name, 'Pages Allocated/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Access Methods' AS object_name, 'Table Lock Escalations/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000000ms & <000001ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000000ms & <000001ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000000ms & <000001ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000000ms & <000001ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000001ms & <000002ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000001ms & <000002ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000001ms & <000002ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000001ms & <000002ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000002ms & <000005ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000002ms & <000005ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000002ms & <000005ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000002ms & <000005ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000005ms & <000010ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000005ms & <000010ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000005ms & <000010ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000005ms & <000010ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000010ms & <000020ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000010ms & <000020ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000010ms & <000020ms' AS counter_name, 'Elappsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000010ms & <000020ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000020ms & <000050ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000020ms & <000050ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000020ms & <000050ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000020ms & <000050ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000050ms & <000100ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000050ms & <000100ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000050ms & <000100ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000050ms & <000100ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000100ms & <000200ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000100ms & <000200ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000100ms & <000200ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000100ms & <000200ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000200ms & <000500ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000200ms & <000500ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000200ms & <000500ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000200ms & <000500ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000500ms & <001000ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000500ms & <001000ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000500ms & <001000ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=000500ms & <001000ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=001000ms & <002000ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=001000ms & <002000ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=001000ms & <002000ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=001000ms & <002000ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=002000ms & <005000ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=002000ms & <005000ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=002000ms & <005000ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=002000ms & <005000ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=005000ms & <010000ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=005000ms & <010000ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=005000ms & <010000ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=005000ms & <010000ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=010000ms & <020000ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=010000ms & <020000ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=010000ms & <020000ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=010000ms & <020000ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=020000ms & <050000ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=020000ms & <050000ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=020000ms & <050000ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=020000ms & <050000ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=050000ms & <100000ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=050000ms & <100000ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=050000ms & <100000ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=050000ms & <100000ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=100000ms' AS counter_name, 'CPU Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=100000ms' AS counter_name, 'CPU Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=100000ms' AS counter_name, 'Elapsed Time:Requests' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Batch Resp Statistics' AS object_name, 'Batches >=100000ms' AS counter_name, 'Elapsed Time:Total(ms)' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Buffer Manager' AS object_name, 'Background writer pages/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Buffer Manager' AS object_name, 'Buffer cache hit ratio' AS counter_name, '' AS instance_name, 'Buffer cache hit ratio base' AS base_counter_name UNION ALL
SELECT 'Buffer Manager' AS object_name, 'Buffer cache hit ratio base' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Buffer Manager' AS object_name, 'Checkpoint pages/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Buffer Manager' AS object_name, 'Lazy writes/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Buffer Node' AS object_name, 'Page life expectancy' AS counter_name, '000' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Database Replica' AS object_name, 'File Bytes Received/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Database Replica' AS object_name, 'Log Bytes Received/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Database Replica' AS object_name, 'Log remaining for undo' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Database Replica' AS object_name, 'Log Send Queue' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Database Replica' AS object_name, 'Mirrored Write Transactions/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Database Replica' AS object_name, 'Recovery Queue' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Database Replica' AS object_name, 'Redo blocked/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Database Replica' AS object_name, 'Redo Bytes Remaining' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Database Replica' AS object_name, 'Redone Bytes/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Database Replica' AS object_name, 'Total Log requiring undo' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Database Replica' AS object_name, 'Transaction Delay' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Checkpoint duration' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Checkpoint duration' AS counter_name, 'tempdb' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Group Commit Time/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Group Commit Time/sec' AS counter_name, 'tempdb' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Log Bytes Flushed/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Log Bytes Flushed/sec' AS counter_name, 'tempdb' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Log Flush Waits/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Log Flush Waits/sec' AS counter_name, 'tempdb' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Log Flushes/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Log Flushes/sec' AS counter_name, 'tempdb' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Log Growths' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Log Growths' AS counter_name, 'tempdb' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Percent Log Used' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Percent Log Used' AS counter_name, 'tempdb' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Transactions/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Transactions/sec' AS counter_name, 'tempdb' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Write Transactions/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Databases' AS object_name, 'Write Transactions/sec' AS counter_namme, 'tempdb' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'General Statistics' AS object_name, 'Active Temp Tables' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'General Statistics' AS object_name, 'Logical Connections' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'General Statistics' AS object_name, 'Logins/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'General Statistics' AS object_name, 'Logouts/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'General Statistics' AS object_name, 'Processes blocked' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'General Statistics' AS object_name, 'User Connections' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Locks' AS object_name, 'Average Wait Time (ms)' AS counter_name, '_Total' AS instance_name, 'Average Wait Time Base' AS base_counter_name UNION ALL
SELECT 'Locks' AS object_name, 'Average Wait Time Base' AS counter_name, '_Total' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Locks' AS object_name, 'Lock Timeouts (timeout > 0)/sec' AS counter_name, '_Total' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Locks' AS object_name, 'Number of Deadlocks/sec' AS counter_name, '_Total' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Memory Manager' AS object_name, 'Memory Grants Outstanding' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Memory Manager' AS object_name, 'Memory Grants Pending' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Memory Manager' AS object_name, 'SQL Cache Memory (KB)' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Memory Manager' AS object_name, 'Stolen Server Memory (KB)' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Memory Manager' AS object_name, 'Target Server Memory (KB)' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Memory Manager' AS object_name, 'Total Server Memory (KB)' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Plan Cache' AS object_name, 'Cache Hit Ratio' AS counter_name, '_Total' AS instance_name, 'Cache Hit Ratio Base' AS base_counter_name UNION ALL
SELECT 'Plan Cache' AS object_name, 'Cache Hit Ratio Base' AS counter_name, '_Total' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Plan Cache' AS object_name, 'Cache Object Counts' AS counter_name, '_Total' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Active memory grant amount (KB)' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Active memory grants count' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Avg Disk Read IO (ms)' AS counter_name, '<Slo*>' AS instance_name, 'Avg Disk Read IO (ms) Base' AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Avg Disk Read IO (ms) Base' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Avg Disk Write IO (ms)' AS counter_name, '<Slo*>' AS instance_name, 'Avg Disk Write IO (ms) Base' AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Avg Disk Write IO (ms) Base' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Cache memory target (KB)' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Compile memory target (KB)' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'CPU control effect %' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'CPU delayed %' AS counter_name, '<Slo*>' AS instance_name, 'CPU delayed % base' AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'CPU delayed % base' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'CPU effective %' AS counter_name, '<Slo*>' AS instance_name, 'CPU effective % base' AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'CPU effective % base' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'CPU usage %' AS counter_name, '<Slo*>' AS instance_name, 'CPU usage % base' AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'CPU usage % base' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'CPU usage target %' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'CPU violated %' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Disk Read Bytes/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Disk Read IO Throttled/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Disk Read IO/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Disk Write Bytes/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Disk Write IO Throttled/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Disk Write IO/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Max memory (KB)' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Memory grant timeouts/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Memory grants/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Pending memory grants count' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Query exec memory target (KB)' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Target memory (KB)' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Resource Pool Stats' AS object_name, 'Used memory (KB)' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'SQL Errors' AS object_name, 'Errors/sec' AS counter_name, 'DB Offline Errors' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'SQL Errors' AS object_name, 'Errors/sec' AS counter_name, 'Kill Connection Errors' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'SQL Errors' AS object_name, 'Errors/sec' AS counter_name, 'User Errors' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'SQL Statistics' AS object_name, 'Batch Requests/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'SQL Statistics' AS object_name, 'Failed Auto-Params/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'SQL Statistics' AS object_name, 'SQL Attention rate' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'SQL Statistics' AS object_name, 'SQL Compilations/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'SQL Statistics' AS object_name, 'SQL Re-Compilations/sec' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Transactions' AS object_name, 'Longest Transaction Running Time' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Transactions' AS object_name, 'Version Cleanup rate (KB/s)' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Transactions' AS object_name, 'Version Generation rate (KB/s)' AS counter_name, '' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Wait Statistics' AS object_name, 'Lock waits' AS counter_name, 'Cumulative wait time (ms) per second' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Wait Statistics' AS object_name, 'Memory grant queue waits' AS counter_name, 'Cumulative wait time (ms) per second' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Wait Statistics' AS object_name, 'Network IO waits' AS counter_name, 'Cumulative wait time (ms) per second' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Wait Statistics' AS object_name, 'Non-Page latch waits' AS counter_name, 'Cumulative wait time (ms) per second' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Wait Statistics' AS object_name, 'Page IO latch waits' AS counter_name, 'Cumulative wait time (ms) per second' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Wait Statistics' AS object_name, 'Page latch waits' AS counter_name, 'Cumulative wait time (ms) per second' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Wait Statistics' AS object_name, 'Wait for the worker' AS counter_name, 'Cumulative wait time (ms) per second' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Active parallel threads' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Active requests' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Avg Disk msec/Read' AS counter_name, '<Slo*>' AS instance_name, 'Disk msec/Read Base' AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Avg Disk msec/Write' AS counter_name, '<Slo*>' AS instance_name, 'Disk msec/Write Base' AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Blocked tasks' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'CPU delayed %' AS counter_name, '<Slo*>' AS instance_name, 'CPU delayed % base' AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'CPU delayed % base' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'CPU effective %' AS counter_name, '<Slo*>' AS instance_name, 'CPU effective % base' AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'CPU effective % base' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'CPU usage %' AS counter_name, '<Slo*>' AS instance_name, 'CPU usage % base' AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'CPU usage % base' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'CPU violated %' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Disk Read Bytes/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Disk Reads/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Disk Violations/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Disk Write Bytes/sec' AS coounter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Disk Writes/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Max request cpu time (ms)' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Max request memory grant (KB)' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Query optimizations/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Queued requests' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Reduced memory grants/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Requests completed/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Suboptimal plans/sec' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Disk msec/Read Base' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Workload Group Stats' AS object_name, 'Disk msec/Write Base' AS counter_name, '<Slo*>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Availability Replica' AS object_name, 'Bytes Received from Replica/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'Availability Replica' AS object_name, 'Bytes Sent to Replica/sec' AS counter_name, '<guid>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. Bytes/Read' AS counter_name, '<* !_Total>' AS instance_name, 'Avg. Bytes/Read BASE' AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. Bytes/Read BASE' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. Bytes/Transfer' AS counter_name, '<* !_Total>' AS instance_name, 'Avg. Bytes/Transfer BASE' AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. Bytes/Transfer BASE' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. Bytes/Write' AS counter_name, '<* !_Total>' AS instance_name, 'Avg. Bytes/Write BASE' AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. Bytes/Write BASE' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. microsec/Read' AS counter_name, '<* !_Total>' AS instance_name, 'Avg. microsec/Read BASE' AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. microsec/Read BASE' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. microsec/Read Comp' AS counter_name, '<* !_Total>' AS instance_name, 'Avg. microsec/Read Comp BASE' AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. microsec/Read Comp BASE' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. microsec/Transfer' AS counter_name, '<* !_Total>' AS instance_name, 'Avg. microsec/Transfer BASE' AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. microsec/Transfer BASE' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. microsec/Write' AS counter_name, '<* !_Total>' AS instance_name, 'Avg. microsec/Write BASE' AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. microsec/Write BASE' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. microsec/Write Comp' AS counter_name, '<* !_Total>' AS instance_name, 'Avg. microsec/Write Comp BASE' AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Avg. microsec/Write Comp BASE' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'HTTP Storage IO failed/sec' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'HTTP Storage IO retry/sec' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Outstanding HTTP Storage IO' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Read Bytes/Sec' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Reads/Sec' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Total Bytes/Sec' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Transfers/Sec' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Write Bytes/Sec' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name UNION ALL
SELECT 'HTTP Storage' AS object_name, 'Writes/Sec' AS counter_name, '<* !_Total>' AS instance_name, NULL AS base_counter_name
;

IF NOT EXISTS (
              
SELECT 1
              
FROM sys.tables AS t
              
WHERE t.name = 'perf_counter_log'
                    
AND
                    
SCHEMA_NAME(t.schema_id) = 'dbo'
              
)
BEGIN
    CREATE TABLE
dbo.perf_counter_log
    
(
    
iteration_number int NOT NULL,
    
collection_time datetimeoffset NOT NULL CONSTRAINT df_perf_counter_log_collection_time DEFAULT (SYSDATETIMEOFFSET()),
    
object_name nvarchar(128) NOT NULL,
    
counter_name nvarchar(128) NOT NULL,
    
instance_name nvarchar(128) NOT NULL,
    
counter_value float NOT NULL,
    
CONSTRAINT pk_perf_counter_log PRIMARY KEY (collection_time, object_name, counter_name, instance_name)
    );
END;

WHILE @IterationNumber <= @MaxIterationCount
BEGIN
    PRINT
CAST(SYSDATETIMEOFFSET() AS nvarchar(40)) + ': Starting iteration ' + CAST(@IterationNumber AS varchar(11));

    -- Get the first snapshot of cumulative counters
    
INSERT INTO @FirstSnapshotCounter (object_name, counter_name, instance_name, cntr_value, base_cntr_value)
    
SELECT pc.object_name, pc.counter_name, pc.instance_name, pc.cntr_value, bc.cntr_value AS base_cntr_value
    
FROM sys.dm_os_performance_counters AS pc
    
INNER JOIN @SourceCounter AS sc
    
ON UPPER(RTRIM(pc.object_name)) LIKE '%' + UPPER(sc.object_name)
       AND
      
UPPER(RTRIM(pc.counter_name)) = UPPER(sc.counter_name)
       AND
       (
      
UPPER(RTRIM(pc.instance_name)) = UPPER(sc.instance_name)
       OR
       (
sc.instance_name = '<guid>' AND TRY_CONVERT(uniqueidentifier, pc.instance_name) IS NOT NULL)
       OR
       (
sc.instance_name = '<Slo*>' AND pc.instance_name LIKE 'Slo%')
       OR
       (
sc.instance_name = '<* !_Total>' AND pc.instance_name <> '_Total')
       )
    OUTER
APPLY (
                
SELECT pc2.cntr_value
                
FROM sys.dm_os_performance_counters AS pc2
                
WHERE pc2.cntr_type = 1073939712
                      
AND
                      
pc2.object_name = pc.object_name
                      
AND
                      
pc2.instance_name = pc.instance_name
                      
AND
                      
UPPER(RTRIM(pc2.counter_name)) = UPPER(sc.base_counter_name)
                )
AS bc
    
WHERE pc.cntr_type IN (272696576,1073874176)
          OR
          
sc.object_name = 'Batch Resp Statistics'
    
OPTION (RECOMPILE)
    ;

    -- Wait for specified interval
    
SELECT @Delay = CONVERT(char(8), DATEADD(second, @SnapshotIntervalSeconds, 0), 114);
    
WAITFOR DELAY @Delay;

    -- Get the second snapshot and record it in the log,
    -- using point-in-time counters as is,
    -- and calculating the values for other counter types
    -- based on the first snapshot and current base counter values.
    
INSERT INTO dbo.perf_counter_log
    
(
    
iteration_number,
    
object_name,
    
counter_name,
    
instance_name,
    
counter_value
    
)
    
SELECT @IterationNumber AS iteration_number,
          
pc.object_name,
          
pc.counter_name,
          
ISNULL(pc.instance_name, '') AS instance_name,
          
ROUND(
                
CASE WHEN sc.object_name = 'Batch Resp Statistics' THEN CAST((pc.cntr_value - fsc.cntr_value) AS float) -- Delta absolute
                    
WHEN pc.cntr_type = 65792 THEN pc.cntr_value -- Point-in-time
                    
WHEN pc.cntr_type = 272696576 THEN (pc.cntr_value - fsc.cntr_value) / CAST(@SnapshotIntervalSeconds AS float) -- Delta rate
                    
WHEN pc.cntr_type = 537003264 THEN CAST(100 AS float) * pc.cntr_value / NULLIF(bc.cntr_value, 0) -- Ratio
                    
WHEN pc.cntr_type = 1073874176 THEN ISNULL((pc.cntr_value - fsc.cntr_value) / NULLIF(bc.cntr_value - fsc.base_cntr_value, 0) / CAST(@SnapshotIntervalSeconds AS float), 0) -- Delta ratio
                
END, 3)
                
AS cntr_value
    
FROM sys.dm_os_performance_counters AS pc
    
INNER JOIN @SourceCounter AS sc
    
ON UPPER(RTRIM(pc.object_name)) LIKE '%' + UPPER(sc.object_name)
       AND
      
UPPER(RTRIM(pc.counter_name)) = UPPER(sc.counter_name)
       AND
       (
      
UPPER(RTRIM(pc.instance_name)) = UPPER(sc.instance_name)
       OR
       (
sc.instance_name = '<guid>' AND TRY_CONVERT(uniqueidentifier, pc.instance_name) IS NOT NULL)
       OR
       (
sc.instance_name = '<Slo*>' AND pc.instance_name LIKE 'Slo%')
       OR
       (
sc.instance_name = '<* !_Total>' AND pc.instance_name <> '_Total')
       )
    OUTER
APPLY (
                
SELECT TOP (1) fsc.cntr_value,
                              
fsc.base_cntr_value
                
FROM @FirstSnapshotCounter AS fsc
                
WHERE fsc.object_name = pc.object_name
                      
AND
                      
fsc.counter_name = pc.counter_name
                      
AND
                      
fsc.instance_name = pc.instance_name
                
) AS fsc
    
OUTER APPLY (
                
SELECT TOP (1) pc2.cntr_value
                
FROM sys.dm_os_performance_counters AS pc2
                
WHERE pc2.cntr_type = 1073939712
                      
AND
                      
pc2.object_name = pc.object_name
                      
AND
                      
pc2.instance_name = pc.instance_name
                      
AND
                      
UPPER(RTRIM(pc2.counter_name)) = UPPER(sc.base_counter_name)
                )
AS bc
    
WHERE -- Exclude base counters
          
pc.cntr_type IN (65792,272696576,537003264,1073874176)
    
OPTION (RECOMPILE)
    ;

    -- Reset for next iteration
    
DELETE
    FROM
@FirstSnapshotCounter;

    SELECT @IterationNumber += 1;
END;

Comments (4)

  1. I got the below error, (I am trying to get around the error myself)

    {

    Msg 195, Level 15, State 10, Line 299

    'TRY_CONVERT' is not a recognized built-in function name.

    Msg 156, Level 15, State 1, Line 315

    Incorrect syntax near the keyword 'AS'.

    Msg 195, Level 15, State 10, Line 359

    'TRY_CONVERT' is not a recognized built-in function name.

    Msg 156, Level 15, State 1, Line 374

    Incorrect syntax near the keyword 'AS'.

    Msg 156, Level 15, State 1, Line 385

    Incorrect syntax near the keyword 'AS'.

    }

    Apart from that, an attempt itself on SQL Azure SQL Perf counters is much appreciated. Way to go!

  2. You are probably running this on a V11 server where this script is not supported (sys.dm_os_performance_counters is not available in V11). This script only supports V12 servers. What is the output from SELECT @@VERSION?

    1. Bewin Samuel says:

      Even I got the same error…

      @@Version gives me below…
      Microsoft SQL Azure (RTM) – 12.0.2000.8
      Jul 12 2016 20:01:21
      Copyright (c) Microsoft Corporation

      1. This can also happen if the compatibility level of the database is below 110.

Skip to main content