SQL Server Performance Counters Script

–============================================
–==    PERFORMANCE COUNTERS
–==            AND
–==    PERFORMANCE ANALYSIS
–============================================

select ‘–where b.CounterName = ”’ + CounterName + ”” from CounterDetails
–copy and paste result int oquery below
GO

select
a.CounterDateTime,
a.CounterValue,
b.CounterName,
b.InstanceName
from CounterData a inner join CounterDetails b
on a.CounterID = b.CounterID
–where b.CounterName = ‘Buffer cache hit ratio’
–where b.CounterName = ‘Page life expectancy’
–where b.CounterName = ‘Full Scans/sec’
–where b.CounterName = ‘Active Temp Tables’
–where b.CounterName = ‘SQL Compilations/sec’
–where b.CounterName = ‘SQL Re-Compilations/sec’
–where b.CounterName = ‘Average Wait Time (ms)’
–where b.CounterName = ‘Number of Deadlocks/sec’
–where b.CounterName = ‘Avg. Disk Bytes/Read’
–where b.CounterName = ‘Avg. Disk Bytes/Transfer’
–where b.CounterName = ‘Avg. Disk Bytes/Write’
–where b.CounterName = ‘Avg. Disk Queue Length’
–where b.CounterName = ‘Avg. Disk Read Queue Length’
–where b.CounterName = ‘Avg. Disk sec/Read’
–where b.CounterName = ‘Avg. Disk sec/Transfer’
–where b.CounterName = ‘Avg. Disk sec/Write’
–where b.CounterName = ‘Avg. Disk Write Queue Length’
–where b.CounterName = ‘Current Disk Queue Length’
–where b.CounterName = ‘Disk Read Bytes/sec’
–where b.CounterName = ‘Disk Reads/sec’
–where b.CounterName = ‘Disk Transfers/sec’
–where b.CounterName = ‘Disk Write Bytes/sec’
–where b.CounterName = ‘Available MBytes’
where b.CounterName = ‘% Processor Time’
–where b.CounterName = ‘Lazy writes/sec’ — shouold not be more that 20 per second
–order by a.CounterDateTime desc
–order by a.CounterValue desc
order by a.CounterValue asc