SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 SYSPERFINFO SQLServer:SQL Statistics
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/02/2009 :  11:39:42  Show Profile  Reply with Quote
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

Slovenia
11750 Posts

Posted - 01/02/2009 :  13:58:57  Show Profile  Visit spirit1's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 01/02/2009 :  18:35:40  Show Profile  Reply with Quote
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

Slovenia
11750 Posts

Posted - 01/02/2009 :  19:03:18  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000