Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 SYSPERFINFO SQLServer:SQL Statistics

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-01-02 : 11:39:42
I have been looking at the results from the query below against the sysperfinfo on table on a server running SQL Server 2000 SP3 on Windows 2000 Server SP 4. I am wondering exactly how to interpret these results.

According to the documentation, these should represent the value over an interval, but it is fairly obvious that 1,693,549,443 Batch Requests/sec is not possible. I have been searching the web for an explanation of this, but haven’t found anything to make sense of it. I am guessing that I should calculate it this way: cntr_value*.0000001 to get the true value, but that is just a guess. It at least puts the numbers in the same range as what I see when I run Performance Monitor.

Another thing I have noticed is that these values do not change as much as what I am seeing when I run Performance Monitor to look at the same values. I have a feeling that they represent averages over a much longer interval that 1 second.

I see similar results when I run this against other SQL 2000 servers, so I think the query results are normal.

Does anyone have any good information or links about this?


select
cntr_value,
cntr_type,
left(rtrim(counter_name),30) as counter_name
from
master.dbo.sysperfinfo
where
object_name = 'SQLServer:SQL Statistics'
order by
counter_name


Results:
cntr_value cntr_type counter_name
----------- ----------- ------------------------------
13818246 272696320 Auto-Param Attempts/sec
1693549443 272696320 Batch Requests/sec
4968033 272696320 Failed Auto-Params/sec
8275400 272696320 Safe Auto-Params/sec
92517551 272696320 SQL Compilations/sec
158527 272696320 SQL Re-Compilations/sec
574810 272696320 Unsafe Auto-Params/sec

(7 row(s) affected)







CODO ERGO SUM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-02 : 13:58:57
all time based counters are aggregated values since sql server start:
http://support.microsoft.com/kb/555064



___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-01-02 : 18:35:40
Thanks for your help on this Mladen.

I was able to develop a workaround for the problem I was working on and I posted the script here:
Find Batch Requests/sec from SYSPERFINFO
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117102



CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-02 : 19:03:18
yeah .. aggregated counter data was/is a big problem in SQL Server 2000 and 2005. Fortunately 2008 fixes this with extended events.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
Go to Top of Page
   

- Advertisement -