SQL SERVER – Performance Counters from System Views – By Kevin Mckenna

I just love social media and all the new concepts of Web 2.0. There are bloggers who are overwhelmed by the new concepts of technology and are not able to keep pace with it. But I like taking such challenges. Twitter has acquired tremendous popularity nowadays and just like everybody else I am also fond of this latest vogue. You can follow me at Twitter here. Through twitter I am getting to meet people like me and it’s a great experience interacting with them. I met SQL and .NET expert Kevin Mckenna on twitter itself.

Kevin is originally from Liverpool, England, and moved to the US in December 2005. He is a DBA for a large warehousing and logistics company based in the US. He has been developing software with SQL backends for approximately 6-7 years, and has been administrating SQL and Oracle databases for the past 4 years.

He is married and has 2 children.  He is a huge football (soccer for the Americans) fan, and when he is not working, he likes spending his time either reading or playing on twitter.

He has recently sent me a very interesting note on Performance Counters from System Views. I really enjoyed reading it and with his permission I am publishing the content here. Let me have your opinion on this.

Many of you are not aware of this fact that access to performance information is readily available in SQL Server and that too without querying performance counters using a custom application or via perfmon. Till now, this fact has remained undisclosed but through this post I would like to explain you can easily access SQL Server performance counter information.

Without putting much effort you will come across the system view sys.dm_os_performance_counters. As the name suggests, this provides you easy access to the SQL Server performance counter information that is passed on to perfmon, but you can get at it via tsql.

You can start with a few simple steps:

SELECT* FROM sys.dm_os_performance_counters

This returns 701 rows of data on the test machine, with information from Buffer Manager, Buffer Partition, Buffer Node, General Statistics, Locks, and so forth.

I’ll try to make you all understand by making it a bit more simple. Here, we will concentrate on Buffer Manager.

Please note that if you have an instance name, the object name will be MSSQLlt;instance name>: instead of SQLServer:

The next step is

SELECT* FROM sys.dm_os_performance_counters WHERE OBJECT_NAME = 'SQLServer:Buffer Manager'

The following screen will be displayed.

XX

As you all can notice, what is displayed above via tsql is similar to what is available via perfmon

Let’s take a look at the columns returned:

field name data type Description
object_name nchar(128) Category to which this counter belongs
counter_name nchar(128) Name of the counter
instance_name nchar(128) Name of the specific instance of the counter. Often contains the database name
cntr_value bigint Current value of the counter
cntr_type int Type of counter as defined by the Windows performance architecture

Now, if you all are wondering what can be done with this information then let me tell you that you have a few things options. We can create a job that will sample a particular value on a timed basis and add it to a table, and view an average value from it. Another option is to just take a glance at it for peace of mind!

One of the most useful things we could derive from this however is trend information.  For example, let’s chart the number of Active Transactions during the work day.

We will start with creating a table, as given below.

CREATE TABLE ActiveTrans_sqlGenus (
obj_name NCHAR(128),
counter_name NCHAR(128),
instance_name NCHAR(128),
cntr_value bigint,
cntr_type INT,
datestamp datetime
)

We can then fill up this table on a timed basis:

INSERT INTO ActiveTrans_sqlGenus
SELECT
OBJECT_NAME, counter_name, instance_name, cntr_value,
cntr_type, GETDATE()
FROM
sys.dm_os_performance_counters
WHERE
OBJECT_NAME = 'SQLServer:Databases'
ANDcounter_name = 'Active Transactions'
ANDinstance_name = 'sqlGenus'

Here, let’s assume that we are inserting into this table on a consistent basis. We can then issue the following command:

SELECT* FROM ActiveTrans_sqlGenus
WHERE datestamp BETWEEN '06/02/2009 09:00' AND '06/02/2009 17:00'

After that, we can retrieve all the entries between 9am and 5pm, our fictitious work day.

We could manipulate this data even further, averaging out the values, pulling the MAX and MIN and so on.

Obviously, this is a very simple example, and certainly not the best way to store this information over a period of time. However, with some digging into just what you need, you can provide access to your SQL Server performance in a remarkably simple manner.

The biggest disappointment in obtaining this information from SQL Server’s system views is that it only provides SQL Server information, so we do not receive information like Processor, Network and Disk information.  These values still need to be pulled via Performance Counters.  But despite this negative aspect, this view will provide ample usable information to keep your database in an excellent shape.

You can find more information on this system view from MSDN:

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

Reference : Pinal Dave (https://blog.sqlauthority.com)