SQL Server: Access Methods -- Page Splits/sec

Question

Post reply

SQL Server: Access Methods -- Page Splits/sec

Doug Blystone

Ten Centuries

Points: 1144
More actions
January 7, 2005 at 1:39 pm

#152329

Hello:
I am learning about all the information I can get from sysperfinfo and find it a very useful to set up some robust monitoring.

However, I am getting some strange results. Can anyone explain whY i get these values (which do not match performance monitor)

SQLServer:SQL Statistics                                                                                                        Batch Requests/sec                                                                                                              26488.0
SQLServer:SQL Statistics                                                                                                        SQL Compilations/sec                                                                                                            31162.0
SQLServer:Buffer Manager                                                                                                        Checkpoint pages/sec                                                                                                            470649.0
SQLServer:Access Methods                                                                                                        Page Splits/sec                                                                                                                 512756.0
SQLServer:Access Methods                                                                                                        Full Scans/sec                                                                                                                  8561.0
SQLServer:Buffer Manager                                                                                                        Lazy writes/sec                                                                                                                 329943.0

These values seem way off base. In addition, This particular DB Server wouldn't be writing a lot of stuff....it would write some trace data (would that explain the numbers?)

Any help would be greatly appreciated in my search for answers!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Doug Blystone Ten Centuries Points: 1144 More actions · Answer 1

I think I found my own answer.

Its a Microsoft bug. Does anyone have a good work around to get these values reflected properly?

See MS Article: http://support.microsoft.com/default.aspx?scid=kb;en-us;555064

mpinnow SSChasing Mays Points: 612 More actions · Answer 2

Not sure if there is a more elegant solution, but here is my solution. I gather perfcounters every 10 minutes from my 25 production servers and store them in a non production box for analysis. In the table that I collect all the counters, I put a column for the time the results were inserted and a column for the last time the sql had restarted, since the counters reset each time sql restarts.

To figure out the accurate counter/sec value not the raw count you just need to do some math. Use any two columns to figure out the counter/sec value for that time period by using a formula like this:

(counterValueRow 2 - counterValueRow1) /(DATEDIFF(ss,TimeInsertedRow2, TimeInsertedRow1) )

The closer the two dates are, the closer you can get to a real time counter. If you do something like trans/sec over a 2 day period, it will be difficult to truly know what your trans/sec were at any given moment. Hope this helps.

Doug Blystone Ten Centuries Points: 1144 More actions · Answer 3

Very nice.

I just updated the pe nformance counters as I inserted them so that they would already have the correct value.

To get server uptime, I just used tempdb creation date/time. Is that the best way for this information?

SELECT @UptimeSeconds = DATEDIFF(ss, crdate, getdate()) FROM master.dbo.sysdatabases where name = 'TempDB'

Does everyone agree that this will reflect an accurate value?

Thanks again.

mpinnow SSChasing Mays Points: 612 More actions · Answer 4

I use use the login_time from sysprocesses for spid 1 to get the restart time. Either should work though. The only thing that may or may not affect you is that the two result in slightly different times. In my test box here are the results

SELECT crdate FROM master..sysdatabases WHERE name = 'tempdb'

-- 2005-01-10 16:14:59.890

SELECT login_time FROM master..sysprocesses WHERE spid = 1

-- 2005-01-10 16:15:09.820

So the question is, does 10 seconds really matter to your collection?