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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
11752 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
11752 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  
 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