October 2, 2012 at 12:15 pm
We migrated our production servers to a new SAN over the weekend. While monitoring for baseline improvements, we found that we appear to be reading from MORE often than before (more page i/o latch). During the investigation, we started looking at buffer cache usage. I was expecting the 2 following queries to return the same results (within reason):
select bpool_committed /1024.0/1024*8 [BPoolGB],bpool_committed
from sys.dm_os_sys_info
select
count(*)as cached_pages_count,
count(*) /1024.0/1024* 8 as cached_pages_GB
from sys.dm_os_buffer_descriptors as bd
The first returns 221 GB used across 28966912 pages (what I expect from MAX memory setting).
The second returns (as rolled up for all DBs) 100 GB used across 13144048 pages.
What am i missing?? I have approximately 2.5 GB used in MPA and 15 GB in proc cache. But nothing to explain the 121 GB discrepancy between the 2 queries.
October 6, 2012 at 5:12 am
What do these say?
SELECT cntr_value / 1048576.0 AS [Lock Memory (GB)]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Lock Memory (KB)';
SELECT SUM(CAST(single_pages_kb AS DECIMAL(18, 2))) / 1048576.0 AS single_pages_gb
FROM sys.dm_os_memory_clerks
WHERE single_pages_kb != 0;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 6, 2012 at 7:08 am
The buffer pool does not just consist of the data cache. There are several other caches that form part of the buffer pool, and space for workspace memory grants and the like.
Not all pages in the data cache will be in use (SQL likes to maintain some free pages for stuff to be read off disk), well, if they are, the server's under memory pressure.
More PageIOLatch waits does not mean you're reading more, only that you're waiting more.
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply