Why different values returned in SQL?

MrSampsonite

Has anyone else noticed this? I think the differences are there perhaps because of what it is measuring is different but perhaps also a flaw?

If you have a SQL server (in my case SQL 2008), monitor the following values:

 

SQL page reads/sec (the built in monitor with their template) - this one is determined via a SQL query SW provides.

SQL page reads/sec (the perfmon one that's under SQL: Buffer Manager)

Page reads/sec (the perfmon one that's under Memory)

SQL page writes/sec (the built in monitor with their template) - this one is determined via a SQL query SW provides.

SQL page writes/sec (the perfmon one that's under SQL: Buffer Manager)

Page writes/sec (the perfmon one that's under Memory)

 

One my servers I've noticed the following results:

Page Reads/sec (perfmon)	N/A	636.68	N/A
Page reads/sec (SQL builtin query)	N/A	1562.00	5126 ms
Page reads/sec (SQL:BM)	N/A	616.94	N/A
Page Writes/sec (perfmon)	N/A	0.00	N/A
Page writes/sec (SQL builtin query)	N/A	307.00	7430 ms
Page writes/sec (SQL:BM)	N/A	312.91	N/A

 

What I find interesting is the following:

1. page reads/sec from perfmon memory is pretty much equal with perfmon sql buffer manager

2. page writes/sec from perfmon memory is 0

3. page reads/sec from builtin sql query SW gave us is so off compared to perfmon,

4. page reads/sec from builtin sql query SW gave us takes over 5 seconds to run (I verified if I run in Management Studio directly on the server I'm monitoring). I think this has to do with our server's performance, but I'm curious if others who have humming servers find this query taking a long time too.

5. page writes/sec from the built sql query SW gave us and from the perfmon buffer manager stat are pretty much the same.

 

This is true more than just this point in time, but pretty much always true across all 14 of our SQL servers we're monitoring, though some take longer or less time to run the built-in queries.

 

Besides understanding these things I noticed, I'm basically curious which I should use. The primary purpose we're monitoring these stats are for performance trending and analysis so we can see what memory issues there are (combined with other perfmon stats). Besides finding out which is more accurate, I'm curious if using the SQL query is not good for our server because of the time it takes for the server to respond with the information.

THanks

VáclavSáblík

Hello,
I have few notes regarding to your question.  Firstly, there is a difference between counters for memory and SQL server.

Namely:

SQL Server: Buffer Manager - Page reads/sec counter is number of physical database page reads issued.

Memory: Page Reads/sec counter is the rate at which the disk was read to resolve hard page faults. Hard page faults occur when a process references a page in virtual memory that is not in working set or elsewhere in physical memory, and must be retrieved from disk.

For writes it is similar and I believe it makes no sense to compare these values. SQL server template is monitoring the same counter under SQL: Buffer Manager you mentioned. Values should be the same only if execution covers the same timeframe.

Example:

Performance counter is storing actual value at the time of poll. When there are spikes and you poll in the time when spike takes place then counter will be the actual value of spike’s top.

SQL template is using SQL query that counts number of reads/writes in time interval (5 seconds is template default). After five seconds counted number is divided by total number of seconds elapsed and this will give you rate per second. So, in fact you will get average rate/sec for 5 seconds interval. This value can be different if your server is not loaded evenly through time.

From my point of view both values are correct but what matter is what you need to measure. I would personally use the counters because it is not running queries on server you are measuring performance on and math for computing average is done by APM's statistical charting.

Note: Query last for 5 second indeed by there is a wait method used that just holds on a thread and is not consuming any CPU resources. Therefore there will be no impact on SQL server performance except open connection.

Thanks,

Vaclav Sablik
SolarWinds Developer