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 (http://blog.SQLAuthority.com)

About these ads

10 thoughts on “SQL SERVER – Performance Counters from System Views – By Kevin Mckenna

  1. Hi,

    This is very helpful. Here we are only getting the counters related to the SQL instance.I have a new requirement, i need to retrive some system related/custom counters and should display in my application. As my application is fully configurable module i would like to do the same like the previous approach so that i no need to touch my application for this.

    Is there any way to get system related/custom counter details other then SQL Server counters from system views?

    Please guide me on this.

    Regards,
    Rousseau Arulsamy.

  2. Rousseau, as far as I’m aware, you’ll need to pull those via the performance counters in .NET – I don’t know of any way to pull that data via t-sql.

  3. Hi Pinal

    My question is that how do We Judge retrived (min,max or avg value ) value is good for sql server or not ?

    Is there any script for this type of judgement for sqlserver for perfermance counters value which collect report with 15 minutes interval for some Hours

    Regards
    jayant Dass

  4. These are only sql server counters but not windows counters did you noticed that.

    There is no objects in this catlog view processor: %processor time.

  5. Hi Dave,

    BTW great blogs as for thsi one extra info to complet some other counters like CPU usage by SQL and non SQL processing

    You can extract the info for processor , diskio from sys.dm_os_ring_buffers
    ex processor usage sql 2005

    declare @ts_now bigint
    select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info

    select record_id, dateadd(ms, -1 * (@ts_now – [timestamp]), GetDate()) as EventTime,
    SQLProcessUtilization,
    SystemIdle,
    100 – SystemIdle – SQLProcessUtilization as OtherProcessUtilization
    from (
    select
    record.value(‘(./Record/@id)[1]’, ‘int’) as record_id,
    record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) as SystemIdle,
    record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) as SQLProcessUtilization,
    timestamp
    from (
    select timestamp, convert(xml, record) as record
    from sys.dm_os_ring_buffers
    where ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
    and record like ‘%%’) as x
    ) as y
    order by record_id desc

    — SQL 2008 + the following: select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info

    select @ts_now = ms_ticks from sys.dm_os_sys_info

    Enjoy

    Paul

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

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