March 10, 2016 at 8:17 am
I thought that when you have high values og the metric "sqlserver:buffer manager\page reads/sec" you also would have high "Memory: Pages/sec", but I do not. Can someone explain this to me?
Regards Tom
March 10, 2016 at 11:01 am
From PAL analysis:
SQLServer:Buffer Manager Page reads/sec
Description: SQLServer_Buffer Manager Page Reads_sec
Description: Number of physical database page reads issued per second. Number of physical database page reads issued. 80 to 90 per second is normal, anything that is above indicates indexing or memory constraint.
Threshold:
Yellow: Page Reads/sec > 90
Next Steps: Attempt to tune the application so that fewer I/O operations are required. For example, perhaps I/O would be reduced if there were appropriate indexes or if the database design were denormalized. If the applications cannot be tuned, you will need to acquire disk devices with more capacity. Compare to the Memory: Pages/sec counter to see if there is paging while the SQL Server:Buffer Manager\Page reads/sec is high. Note: Before adjusting the fill factor, at a database level compare the SQL Server:Buffer Manager\Page reads/sec counter to the SQL Server:Buffer Manager\Page writes/sec counter, and use the fill factor option only if writes are a substantial fraction of reads (greater than 30 percent).
Reference:
SQL Server, Buffer Manager Object
March 11, 2016 at 2:18 am
Thanks for the reply, but I still don't get this. If "buffer manager: page reads/sec" is at 50, should not "Memory pages/sec" at least be 50 also. They are both measuring page IO from disk, but "buffer manager" are just measuring what SQL Server are doing.
March 11, 2016 at 2:37 am
No, they're not both measuring pages read from disk. They're measuring completely different things
Memory\\Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays. It is the sum of Memory\\Pages Input/sec and Memory\\Pages Output/sec. It is counted in numbers of pages, so it can be compared to other counts of pages, such as Memory\\Page Faults/sec, without conversion. It includes pages retrieved to satisfy faults in the file system cache (usually requested by applications) non-cached mapped memory files.
A hard page fault occurs when a request is made for a virtual memory page that isn't in physical memory, i.e. a page that has been swapped out into the page file.
The SQL buffer manager's pages/sec is measuring pages read from disk into the buffer pool, fetched because the query processor wants to read/write the page, no page faults occur during that process because pages that aren't in the buffer pool are on disk, they're not mapped into virtual memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply