Posted by decipherinfosys on June 12, 2009
We had briefly covered this DMV in one of our blog posts before – here. This DMV can be used for tracking SQL Server related perfmon counters through plain simple T-SQL statements. This DMV is a replacement for the sysperfinfo view from releases prior to SQL Server 2005 and for every object name, we get the counter name, instance name, counter type and counter value – essentially the same things that we get through perfmon. Here is the BOL entry for this DMV which also covers what to do in case these counters are missing from your system (you can also read more on that here):
Now, the data is good but data without analysis is useless. So, how can we make use of this data? Practically the same way as we have been making use of perfmon – scheduling those traces and capturing the data for doing trend analysis and analyzing the load characteristics on the system. Pick the counters that you track in perfmon for SQL Server and schedule a job using SQL Server Agent which then takes the output based on this DMV and dumps it into a table. Put the conditions in that job also to check for the thresholds or patterns and send out automatic alerts – in the end, having a SSRS report based off that summary table will be pretty useful for the production support DB teams. Do remember that this only exposes the SQL Server counters and not the disk, network and other counters.
Before you jump into it, we would suggest to open up perfmon, go to that object name, the specific counter and hit Explain –
Once you have read it, play around with those counters for some time in perfmon to get a solid understanding of the different objects and their counters. Once you have a good understanding, then you should move forward with creating a data collection table in the DBA database and dump the data from this DMV into that table and do your performance analysis, trend analysis and alerts.
An excellent resource to read more on this DMV is Greg Larsen’s post here.
- The BOL post and Greg Larsen’s posts mentioned in this article.