Systems Engineering and RDBMS

sys.dm_os_performance_counters

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):

http://msdn.microsoft.com/en-us/library/ms187743(SQL.90).aspx

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 –

Perfmon_1

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.

References:

  • The BOL post and Greg Larsen’s posts mentioned in this article.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: