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.
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 (
We can then fill up this table on a timed basis:
INSERT INTO ActiveTrans_sqlGenus
OBJECT_NAME, counter_name, instance_name, cntr_value,
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:
Reference : Pinal Dave (https://blog.sqlauthority.com)
This really helpfull. Thanks for you both.
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.
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.
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
Hi Kevin Mckenna,
Could you please update on my question.
Thanks in advance
Can you please post more about SQL?
Hope you will do your best.
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.
Can you get performance information for a single table?
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,
100 – SystemIdle – SQLProcessUtilization as OtherProcessUtilization
record.value(‘(./Record/@id)’, ‘int’) as record_id,
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)’, ‘int’) as SystemIdle,
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)’, ‘int’) as SQLProcessUtilization,
select timestamp, convert(xml, record) as record
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