This is the second part in a series of posts about the data collector in SQL Server 2008. In this post, I will list the measurements that are available from the data collector. This may seem like a strange post, but I could not find this information anywhere on the Internet.
Many DBAs and developers have favorite sets of counters, and a suggestion to use the data collector is often met with the question, "Does it capture x?" For example, does the data collector capture the same counters as Codeplex’s Performance Analysis of Logs (PAL) tool? With the following list, you can both answer that question and advocate for using the data collector instead of other older tools because data accessibility is much better.
A standard management data warehouse database yielded the following list. The data collector is extensible; you can add additional counters as needed. The great thing is that this is entirely canned. You don't have to spend hours adding counters to the Windows Performance Monitor or fiddle with getting them out later for charting. (They are sitting in a database table that you can easily query.)
The data collector captures the following measurements every minute, which is ideal for production monitoring.
· \LogicalDisk(_Total)
· \Memory
· \PhysicalDisk(_Total)
· \SQLServer:Buffer Manager
· \System
· \Network Interface(- card type -)\
· \Process($(TARGETPROCESS))
· \Server Work Queues(?)
· \SQLServer:Databases(_Total)
· \SQLServer:General Statistics
· \SQLServer:Memory Manager
· \SQLServer:Plan Cache(Object Plans)
· \SQLServer:Plan Cache(SL Plans)
· \SQLServer:Plan Cache(Temporary Tables & Table Variables)
· \SQLServer:SQL Statistics
· \SQLServer:Transactions
· \SQLServer:Workload Group Stats(default)
· \SQLServer:Workload Group Stats(internal)
· \Server Work Queues(Blocking Queue)
· \SQLServer:Databases(tempdb)
· \SQLServer:Plan Cache(_Total)
The following three measurements are available for each wait type:
The wait types are as follows:
· Backup
· Buffer I/O
· Buffer Latch
· Compilation
· CPU
· Full Text Search
· Idle
· Latch
· Lock
· Logging
· Memory
· Network I/O
· Other
· Parallelism
· SQLCLR
· Transaction
· User Waits
That's the list. You have more counters than you can recite from memory! If you have an evil streak, the next time that you interview a job candidate, ask them, "If the wait type LCK_M_RIn_X has a high count, what does it signify?"
Ken Lassesen is part of the original team that created Dr. GUI of MSDN and specializes in new and resurrected commercial product architecture. He developed architecture for several Microsoft websites, including the original MSDN site and the current Microsoft Partner Network site. He's equally at home with SQL Server, XHTML, Section 508 accessibility standards, globalization, Security Content Automation Protocol (SCAP) security, C#, and ASP.NET server controls. When he is not having fun with technology, he enjoys taking lunch-break hikes in the North Cascades.