In this short article i will share a simple query that i wrote to find the number of pages of a database in the buffer pool and there usage of the buffer pool
SELECT CASE database_id WHEN 32767 THEN 'ResourceDb'
END AS [Database],COUNT(*) AS PageCount ,
( COUNT(*) * 8.0 ) / 1024 AS [SpaceOccupiedInBuffer-Mb]
GROUP BY database_id
ORDER BY 3 DESC
Buffer cache hit ratio(BCHR) : Well every dba who has ever done any Performance tuning work would know about this particular counter. So what is BCHR: Percentage of pages that were found in the buffer pool without having to incur a read from disk.
And how much guidance does it give us….
Well to know that i had to share this great article from Jonathan Kehayias. I read this article on http://www.simple-talk.com. Read the article and challenge your understanding of BCHR once again.