Home » Adding Data Collectors on SQL Server 2008/2008 R2/2012/2014/2016

Adding Data Collectors on SQL Server 2008/2008 R2/2012/2014/2016

sql server DBA 999

Adding SQL Server Data Collectors to SQL Server is very important for Troublshooting issues and Root cause analysis of many issues normally occuring on SQL Servers.

These counters are helpful for both Virtual or Physical SQL Servers. They are available in all versions like sql server 2008 data collector, sql server 2012 data collector, sql server 2014 data collector

    The Counters related to SQL Instance which are really important are:


SQLServer:Access MethodsFreeSpace Scans/sec
SQLServer:Access MethodsFull Scans/sec
SQLServer:Access MethodsIndex Searches/sec
SQLServer:Access MethodsPages Allocated/sec
SQLServer:Access MethodsWorkfiles Created/sec
SQLServer:Access MethodsWorktables Created/sec
SQLServer:Buffer ManagerBuffer cache hit ratio
SQLServer:Buffer ManagerCheckpoint pages/sec
SQLServer:Buffer ManagerDatabase pages
SQLServer:Buffer ManagerFree list stalls/sec
SQLServer:Buffer ManagerFree pages
SQLServer:Buffer ManagerLazy writes/sec
SQLServer:Buffer ManagerPage life expectancy
SQLServer:Buffer ManagerReadahead pages/sec
SQLServer:Buffer ManagerReserved pages
SQLServer:Buffer ManagerStolen pages
SQLServer:Buffer ManagerTarget pages
SQLServer:Buffer ManagerTotal pages
SQLServer:Buffer Node(000)Page life expectancy
SQLServer:Buffer Node(000)Stolen pages
SQLServer:Buffer Node(000)Target pages
SQLServer:Buffer Node(000)Total pages
SQLServer:Databases(_Total)Active Transactions
SQLServer:Databases(_Total)Backup/Restore Throughput/sec
SQLServer:Databases(_Total)Data File(s) Size (KB)
SQLServer:Databases(_Total)Log Cache Reads/sec
SQLServer:Databases(_Total)Log File(s) Size (KB)
SQLServer:Databases(_Total)Log Growths
SQLServer:Databases(_Total)Transactions/sec
SQLServer:General StatisticsActive Temp Tables
SQLServer:General StatisticsLogical Connections
SQLServer:General StatisticsLogins/sec
SQLServer:General StatisticsLogouts/sec
SQLServer:General StatisticsMars Deadlocks
SQLServer:General StatisticsProcesses blocked
SQLServer:General StatisticsTemp Tables Creation Rate
SQLServer:General StatisticsTemp Tables For Destruction
SQLServer:General StatisticsTransactions
SQLServer:General StatisticsUser Connections
SQLServer:LatchesLatch Waits/sec
SQLServer:LatchesTotal Latch Wait Time (ms)
SQLServer:Locks(_Total)Lock Requests/sec
SQLServer:Locks(_Total)Lock Waits/sec
SQLServer:Locks(_Total)Number of Deadlocks/sec
SQLServer:Memory ManagerConnection Memory (KB)
SQLServer:Memory ManagerGranted Workspace Memory (KB)
SQLServer:Memory ManagerLock Memory (KB)
SQLServer:Memory ManagerMaximum Workspace Memory (KB)
SQLServer:Memory ManagerMemory Grants Outstanding
SQLServer:Memory ManagerMemory Grants Pending
SQLServer:Memory ManagerOptimizer Memory (KB)
SQLServer:Memory ManagerSQL Cache Memory (KB)
SQLServer:Memory ManagerTarget Server Memory (KB)
SQLServer:Memory ManagerTotal Server Memory (KB)
SQLServer:Plan Cache(_Total)Cache Hit Ratio
SQLServer:Plan Cache(_Total)Cache Object Counts
SQLServer:Plan Cache(_Total)Cache Objects in use
SQLServer:SQL StatisticsBatch Requests/sec
SQLServer:SQL StatisticsSQL Compilations/sec
SQLServer:SQL StatisticsSQL Re-Compilations/sec
SQLServer:TransactionsFree Space in tempdb (KB)
SQLServer:TransactionsLongest Transaction Running Time
SQLServer:TransactionsTransactions
SQLServer:Wait Statistics(*)Lock waits
SQLServer:Wait Statistics(*)Memory grant queue waits
SQLServer:Wait Statistics(*)Network IO waits
SQLServer:Wait Statistics(*)Non-Page latch waits
SQLServer:Wait Statistics(*)Page IO latch waits
SQLServer:Wait Statistics(*)Page latch waits
SQLServer:Database Mirroring(_Total)Redo Queue KB
SQLServer:Database Mirroring(_Total)Log Send Queue KB
SQLServer:Locks(_Total)*
SQLServer:Replication Dist.*
SQLServer:Replication Logreader*
SQLServer:Access MethodsFreeSpace Scans/sec
SQLServer:Access MethodsFull Scans/sec
SQLServer:Access MethodsIndex Searches/sec
SQLServer:Access MethodsPages Allocated/sec
SQLServer:Access MethodsWorkfiles Created/sec
SQLServer:Access MethodsWorktables Created/sec
SQLServer:Buffer ManagerBuffer cache hit ratio
SQLServer:Buffer ManagerCheckpoint pages/sec
SQLServer:Buffer ManagerDatabase pages
SQLServer:Buffer ManagerFree list stalls/sec
SQLServer:Buffer ManagerFree pages
SQLServer:Buffer ManagerLazy writes/sec
SQLServer:Buffer ManagerPage life expectancy
SQLServer:Buffer ManagerReadahead pages/sec
SQLServer:Buffer ManagerReserved pages
SQLServer:Buffer ManagerStolen pages
SQLServer:Buffer ManagerTarget pages
SQLServer:Buffer ManagerTotal pages
SQLServer:Buffer Node(000)Page life expectancy
SQLServer:Buffer Node(000)Stolen pages
SQLServer:Buffer Node(000)Target pages
SQLServer:Buffer Node(000)Total pages
SQLServer:Databases(_Total)Active Transactions
SQLServer:Databases(_Total)Backup/Restore Throughput/sec
SQLServer:Databases(_Total)Data File(s) Size (KB)
SQLServer:Databases(_Total)Log Cache Reads/sec
SQLServer:Databases(_Total)Log File(s) Size (KB)
SQLServer:Databases(_Total)Log Growths
SQLServer:Databases(_Total)Transactions/sec
SQLServer:General StatisticsActive Temp Tables
SQLServer:General StatisticsLogical Connections
SQLServer:General StatisticsLogins/sec
SQLServer:General StatisticsLogouts/sec
SQLServer:General StatisticsMars Deadlocks
SQLServer:General StatisticsProcesses blocked
SQLServer:General StatisticsTemp Tables Creation Rate
SQLServer:General StatisticsTemp Tables For Destruction
SQLServer:General StatisticsTransactions
SQLServer:General StatisticsUser Connections
SQLServer:LatchesLatch Waits/sec
SQLServer:LatchesTotal Latch Wait Time (ms)
SQLServer:Locks(_Total)Lock Requests/sec
SQLServer:Locks(_Total)Lock Waits/sec
SQLServer:Locks(_Total)Number of Deadlocks/sec
SQLServer:Memory ManagerConnection Memory (KB)
SQLServer:Memory ManagerGranted Workspace Memory (KB)
SQLServer:Memory ManagerLock Memory (KB)
SQLServer:Memory ManagerMaximum Workspace Memory (KB)
SQLServer:Memory ManagerMemory Grants Outstanding
SQLServer:Memory ManagerMemory Grants Pending
SQLServer:Memory ManagerOptimizer Memory (KB)
SQLServer:Memory ManagerSQL Cache Memory (KB)
SQLServer:Memory ManagerTarget Server Memory (KB)
SQLServer:Memory ManagerTotal Server Memory (KB)
SQLServer:Plan Cache(_Total)Cache Hit Ratio
SQLServer:Plan Cache(_Total)Cache Object Counts
SQLServer:Plan Cache(_Total)Cache Objects in use
SQLServer:SQL StatisticsBatch Requests/sec
SQLServer:SQL StatisticsSQL Compilations/sec
SQLServer:SQL StatisticsSQL Re-Compilations/sec
SQLServer:TransactionsFree Space in tempdb (KB)
SQLServer:TransactionsLongest Transaction Running Time
SQLServer:TransactionsTransactions
SQLServer:Wait Statistics(*)Lock waits
SQLServer:Wait Statistics(*)Memory grant queue waits
SQLServer:Wait Statistics(*)Network IO waits
SQLServer:Wait Statistics(*)Non-Page latch waits
SQLServer:Wait Statistics(*)Page IO latch waits
SQLServer:Wait Statistics(*)Page latch waits
SQLServer:Database Mirroring(_Total)Redo Queue KB
SQLServer:Database Mirroring(_Total)Log Send Queue KB
SQLServer:Locks(_Total)*
SQLServer:Replication Dist.*
SQLServer:Replication Logreader*

For the Other Counters covering Processor, Disk, Memory add another Data Collector Like below screenshot
How to fix SQL server Data Collectors

The Counter should be set to run on Schedule every hours for 15 mins with 15 Second Sample rate into Csv Format.

For adding Schedule you can add it by saving a Data Collector Template and adding the Schedule in XML Format and re-importing back which will save a lot of time.

I have also added a SQL Server and Server Template in Zipped Folder if anyone would like to use it below

Hope this helps…..

Leave a Reply

Your email address will not be published. Required fields are marked *