September 18, 2012 at 7:32 am
Hi All
How accurate are the counters in sys.dm_os_performance counters
When I run this query
select * from master.sys.dm_os_performance_counters
where counter_name='Batch Requests/sec'
I get a cntr_value value of +- 10630
When I run the same performance counter using Windows Performance Monitor, it doesn't go over 10
What am I missing here?
Thanks
September 18, 2012 at 7:35 am
how are you calculating that number of 10630, is that how it is when you do the select or have you done a comparision between the value at two different points in time?
September 18, 2012 at 7:38 am
anthony.green (9/18/2012)
how are you calculating that number of 10630, is that how it is when you do the select or have you done a comparision between the value at two different points in time?
That's the +- value returned when I run the select
select * from master.sys.dm_os_performance_counters
where counter_name='Batch Requests/sec'
September 18, 2012 at 7:41 am
http://technet.microsoft.com/en-us/library/ms187743.aspx
For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used.
As this counter is a per second counter it is cumulative, so you will either need to do many samples and divide by the sample time in seconds or calculate the system uptime and devide the value by that, but it is only an average, if you want it second by second, you will need to something like this
declare @v1 bigint, @v2 bigint
select @v1 = cntr_value from master.sys.dm_os_performance_counters
where counter_name='Batch Requests/sec'
waitfor delay '00:00:01'
select @v2 = cntr_value from master.sys.dm_os_performance_counters
where counter_name='Batch Requests/sec'
select @v2 - @v1
September 18, 2012 at 7:52 am
anthony.green (9/18/2012)
http://technet.microsoft.com/en-us/library/ms187743.aspxFor per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used.
As this counter is a per second counter it is cumulative, so you will either need to do many samples and divide by the sample time in seconds or calculate the system uptime and devide the value by that, but it is only an average, if you want it second by second, you will need to something like this
declare @v1 bigint, @v2 bigint
select @v1 = cntr_value from master.sys.dm_os_performance_counters
where counter_name='Batch Requests/sec'
waitfor delay '00:00:01'
select @v2 = cntr_value from master.sys.dm_os_performance_counters
where counter_name='Batch Requests/sec'
select @v2 - @v1
Thanks
Basically, what I'm trying to do is get an understanding on my plan cache re-use
This is one query I found and I'm not sure whether it's completely accurate or not
select t1.cntr_value As [Batch Requests/sec],
t2.cntr_value As [SQL Compilations/sec],
plan_reuse =
convert(decimal(15,2),
(t1.cntr_value*1.0-t2.cntr_value*1.0)/t1.cntr_value*100)
from
master.sys.dm_os_performance_counters t1,
master.sys.dm_os_performance_counters t2
where
t1.counter_name='Batch Requests/sec' and
t2.counter_name='SQL Compilations/sec'
Another one I've got is
select * from sys.dm_os_performance_counters where counter_name like '%cache hit ratio%'
and object_name like '%plan cache%' and instance_name like '%SQL%'
Thanks
September 18, 2012 at 8:29 am
Basic formula is
((Cache Hit Ratio / Cache Hit Ratio Base) * 100)
September 18, 2012 at 8:35 am
Just remember that sys.dm_os_performance_counters is just querying against the Performance Monitor counters. They're as accurate or as inaccurate as anything else within Performance Monitor because they're both from the same source.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 18, 2012 at 8:35 am
anthony.green (9/18/2012)
Basic formula is((Cache Hit Ratio / Cache Hit Ratio Base) * 100)
Thanks
And that would give me an accurate % of plan-reuse in my instance?
What is the Cache Hit Ratio Base based on?
Thanks
September 18, 2012 at 9:00 am
anthony.green (9/18/2012)
Basic formula is((Cache Hit Ratio / Cache Hit Ratio Base) * 100)
Will something like this do the trick
declare @cachehitratio decimal(18,2)
declare @cachehitratiobase decimal(18,2)
declare @ratio decimal(18,2)
select @cachehitratio = cntr_value from sys.dm_os_performance_counters
where object_name = 'SQLServer:Plan Cache'
and counter_name = 'Cache Hit Ratio'
and instance_name = 'SQL Plans'
select @cachehitratiobase = cntr_value from sys.dm_os_performance_counters
where object_name = 'SQLServer:Plan Cache'
and counter_name = 'Cache Hit Ratio Base'
and instance_name = 'SQL Plans'
select @ratio = (@cachehitratio/@cachehitratiobase)*100
print @cachehitratio
print @cachehitratiobase
print @ratio
Thanks
September 18, 2012 at 12:56 pm
SQLSACT (9/18/2012)
anthony.green (9/18/2012)
Basic formula is((Cache Hit Ratio / Cache Hit Ratio Base) * 100)
Thanks
And that would give me an accurate % of plan-reuse in my instance?
What is the Cache Hit Ratio Base based on?
Thanks
No.That's the percentage of times that a requested data page was in buffer.
for plan reuse consider this:
Initial Compilations = SQL Compilations/Sec – SQL Recompilation/Sec
Plan Reuse = (Batch Req/sec - Initial Compilations) / Batch Req/sec
Pooyan
September 19, 2012 at 12:28 am
pooyan_pdm (9/18/2012)
SQLSACT (9/18/2012)
anthony.green (9/18/2012)
Basic formula is((Cache Hit Ratio / Cache Hit Ratio Base) * 100)
Thanks
And that would give me an accurate % of plan-reuse in my instance?
What is the Cache Hit Ratio Base based on?
Thanks
No.That's the percentage of times that a requested data page was in buffer.
for plan reuse consider this:
Initial Compilations = SQL Compilations/Sec – SQL Recompilation/Sec
Plan Reuse = (Batch Req/sec - Initial Compilations) / Batch Req/sec
No.That's the percentage of times that a requested data page was in buffer
I think he was referring to the SQLServer:Plan Cache hit ratio
How can I incorporate you suggestion into a script?
Thanks
September 19, 2012 at 12:34 am
Grant Fritchey (9/18/2012)
Just remember that sys.dm_os_performance_counters is just querying against the Performance Monitor counters. They're as accurate or as inaccurate as anything else within Performance Monitor because they're both from the same source.
Thanks
If I query a per/second performance counter, is my result the same as what the performance counter would be at that time?
Thanks
September 19, 2012 at 4:18 am
SQLSACT (9/19/2012)
Grant Fritchey (9/18/2012)
Just remember that sys.dm_os_performance_counters is just querying against the Performance Monitor counters. They're as accurate or as inaccurate as anything else within Performance Monitor because they're both from the same source.Thanks
If I query a per/second performance counter, is my result the same as what the performance counter would be at that time?
Thanks
It just depends on how that counter is being collected and displayed by SQL Server. You'd have to read the documentation for it to be sure. But they are absolutely the same source.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 19, 2012 at 4:23 am
Grant Fritchey (9/19/2012)
SQLSACT (9/19/2012)
Grant Fritchey (9/18/2012)
Just remember that sys.dm_os_performance_counters is just querying against the Performance Monitor counters. They're as accurate or as inaccurate as anything else within Performance Monitor because they're both from the same source.Thanks
If I query a per/second performance counter, is my result the same as what the performance counter would be at that time?
Thanks
It just depends on how that counter is being collected and displayed by SQL Server. You'd have to read the documentation for it to be sure. But they are absolutely the same source.
Thanks
September 19, 2012 at 7:28 am
Grant Fritchey (9/19/2012)
SQLSACT (9/19/2012)
Grant Fritchey (9/18/2012)
Just remember that sys.dm_os_performance_counters is just querying against the Performance Monitor counters. They're as accurate or as inaccurate as anything else within Performance Monitor because they're both from the same source.Thanks
If I query a per/second performance counter, is my result the same as what the performance counter would be at that time?
Thanks
It just depends on how that counter is being collected and displayed by SQL Server. You'd have to read the documentation for it to be sure. But they are absolutely the same source.
Thanks Grant
A question reagarding performance in General, may be out of the scope of this thread
How do I know if my plan cache is the root of a performance issue on my instance?
At which point of troubleshooting do I say, ok, now let's look at the plan cache
Is it usually on the top of the list of things to check when troubleshooting performance?
Also, is Plan cache performance related more to memory or CPU?
Thanks
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply