May 18, 2011 at 8:40 am
Hi,
I have recently started capturing pefmon counters to allow me to monitor the performance of out database servers.
One of our more heavily used servers always has a buffer cache hit ratio of less then 20% so this would indicate a potential lack of sufficient memory. However, the avg memory pages / second counter is never higher then 8 (which is an acceptable level according to my reading). I would have assumed that the paging counter would be much higher, or am i misunderstanding the connection between the two counter?
May 18, 2011 at 8:43 am
What is your current page life expectancy? A minimum of 600 is the generally accepted limit. If you have plenty of memory it could be much higher.
May 18, 2011 at 8:46 am
I'm not currently capturing this counter; i'll add it and check.
Thanks for the advice.
May 18, 2011 at 8:58 am
MysteryJimbo (5/18/2011)
What is your current page life expectancy? A minimum of 600 is the generally accepted limit. If you have plenty of memory it could be much higher.
600 means the entire data cache changes in 10 minutes. There's no single acceptable value for that counter, depends on whole lot, including size of cache
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
May 18, 2011 at 9:00 am
Pages/sec are reads from the page (swap) file, not reads from any file on disk. If SQL is reading pages into its data cache, its reading them from the data file, not the swap file. Swap file should barely be in use.
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
May 18, 2011 at 9:06 am
GilaMonster (5/18/2011)
Pages/sec are reads from the page (swap) file, not reads from any file on disk. If SQL is reading pages into its data cache, its reading them from the data file, not the swap file. Swap file should barely be in use.
Thanks for the info, do you know if there is an alternate counter which counts the number of times pages are read from disk into the cache? I would assume this is the counter which would be really high given my low buffer cache hit ratio?
May 18, 2011 at 9:17 am
GilaMonster (5/18/2011)
MysteryJimbo (5/18/2011)
What is your current page life expectancy? A minimum of 600 is the generally accepted limit. If you have plenty of memory it could be much higher.600 means the entire data cache changes in 5 minutes. There's no single acceptable value for that counter, depends on whole lot, including size of cache
I completely agree, I never said it was a hard set value. Only a generally acceptable number.
My number was 10 minutes though 😀 I misquoted the 5 minutes or 300 seconds.
May 18, 2011 at 9:28 am
DBANewbie (5/18/2011)
GilaMonster (5/18/2011)
Pages/sec are reads from the page (swap) file, not reads from any file on disk. If SQL is reading pages into its data cache, its reading them from the data file, not the swap file. Swap file should barely be in use.Thanks for the info, do you know if there is an alternate counter which counts the number of times pages are read from disk into the cache? I would assume this is the counter which would be really high given my low buffer cache hit ratio?
Try Page Reads/sec under SQL Server Buffer manager. That's not necessarily a very useful counter though, it just tells you pages are being read from disk, not what DB or anything relating to why.
Maybe combine that with full scans/sec (same perfmon group), lazy writes/sec, checkpoint pages/sec and, from SQL side, sys.dm_io_virtual_file_stats
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
May 18, 2011 at 9:29 am
GilaMonster (5/18/2011)
Pages/sec are reads from the page (swap) file, not reads from any file on disk. If SQL is reading pages into its data cache, its reading them from the data file, not the swap file. Swap file should barely be in use.
I'm confused here, the articles that i have read say that the memory\Pages/sec counter is concerned with reads/writes to the hard drive. I am obviously a new comer and as such i'm in no position to doubt what i'm told but i am clearly missing something here.
The Microsoft website states:
Memory : Pages/sec. This value is often confused with Page Faults/sec. The Pages/sec counter is a combination of Pages Input/sec and Pages Output/sec counters. Recall that Page Faults/sec is a combination of hard page faults and soft page faults. This counter, however, is a general indicator of how often the system is using the hard drive to store or retrieve memory associated data.
Links:
http://technet.microsoft.com/en-us/library/cc768048.aspx#XSLTsection131121120120
http://www.sql-server-performance.com/articles/audit/hardware_bottlenecks_p1.aspx
May 18, 2011 at 10:25 am
Yes. "memory associated data." The swap file. It is NOT reads/writes from random files on disk. It is memory-related reads from the swap file.
These are all windows-level counters. Windows knows absolutely nothing about SQL's pages in SQL's data cache. It knows about it's own memory pages (all memory is divided into pages) and it's own swap file which it writes memory pages to and reads from in times of Windows-level memory pressure.
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply