Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SQL Performance Statistics Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, September 18, 2012 7:32 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 8:22 AM Points: 1,258, Visits: 2,232
 Hi AllHow accurate are the counters in sys.dm_os_performance countersWhen I run this query`select * from master.sys.dm_os_performance_counterswhere counter_name='Batch Requests/sec'`I get a cntr_value value of +- 10630When I run the same performance counter using Windows Performance Monitor, it doesn't go over 10What am I missing here?Thanks
Post #1360780
 Posted Tuesday, September 18, 2012 7:35 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, June 05, 2013 2:40 AM Points: 5,075, Visits: 4,833
 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?
Post #1360782
 Posted Tuesday, September 18, 2012 7:38 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 8:22 AM Points: 1,258, Visits: 2,232
 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_counterswhere counter_name='Batch Requests/sec'`
Post #1360786
 Posted Tuesday, September 18, 2012 7:41 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, June 05, 2013 2:40 AM Points: 5,075, Visits: 4,833
 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 bigintselect @v1 = cntr_value from master.sys.dm_os_performance_counterswhere counter_name='Batch Requests/sec'waitfor delay '00:00:01'select @v2 = cntr_value from master.sys.dm_os_performance_counterswhere counter_name='Batch Requests/sec'select @v2 - @v1`
Post #1360788
 Posted Tuesday, September 18, 2012 7:52 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 8:22 AM Points: 1,258, Visits: 2,232
 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 bigintselect @v1 = cntr_value from master.sys.dm_os_performance_counterswhere counter_name='Batch Requests/sec'waitfor delay '00:00:01'select @v2 = cntr_value from master.sys.dm_os_performance_counterswhere counter_name='Batch Requests/sec'select @v2 - @v1`ThanksBasically, what I'm trying to do is get an understanding on my plan cache re-useThis 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 t2where 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
Post #1360798
 Posted Tuesday, September 18, 2012 8:29 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, June 05, 2013 2:40 AM Points: 5,075, Visits: 4,833
Post #1360826
 Posted Tuesday, September 18, 2012 8:35 AM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 9:49 AM Points: 13,436, Visits: 25,281
 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 RooseveltThe Scary DBAAuthor of: SQL Server 2012 Query Performance TuningSQL Server 2008 Query Performance Tuning Distilled and SQL Server Execution PlansProduct Evangelist for Red Gate Software
Post #1360830
 Posted Tuesday, September 18, 2012 8:35 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 8:22 AM Points: 1,258, Visits: 2,232
 anthony.green (9/18/2012)Basic formula is((Cache Hit Ratio / Cache Hit Ratio Base) * 100)ThanksAnd that would give me an accurate % of plan-reuse in my instance?What is the Cache Hit Ratio Base based on?Thanks
Post #1360831
 Posted Tuesday, September 18, 2012 9:00 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 8:22 AM Points: 1,258, Visits: 2,232
 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 @cachehitratioprint @cachehitratiobaseprint @ratio`Thanks
Post #1360848
 Posted Tuesday, September 18, 2012 12:56 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, June 07, 2013 2:47 PM Points: 108, Visits: 362
 SQLSACT (9/18/2012)anthony.green (9/18/2012)Basic formula is((Cache Hit Ratio / Cache Hit Ratio Base) * 100)ThanksAnd that would give me an accurate % of plan-reuse in my instance?What is the Cache Hit Ratio Base based on?ThanksNo.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/SecPlan Reuse = (Batch Req/sec - Initial Compilations) / Batch Req/sec Cheers ,Pooyan D________________________________________________Microsoft Certified Technology Specialist : SQL Server 2008
Post #1360964

 Permissions