In fact, I will prove that it’s entirely possible for SQL Server to be under significant memory pressure while displaying a value for BCHR that, viewed in isolation, would lead a DBA to assume that SQL Server was in fine heath. This article will, I hope, convince you that this interpretation of the BCHR counter value is completely incorrect and very misleading. Prevailing wisdom suggests that “low” is less than 95% for OLTP systems, or less than 90% for OLAP or data warehouse systems. Conversely, if the BCHR value is “low”, then this is a sure sign sign that SQL Server is under memory pressure and doing lots of physical disk reads to retrieve the required data. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server.”Ĭommonly, this definition is interpreted like this: if the value of the Buffer Cache Hit Ratio ( BCHR) counter is “high”, then SQL Server is efficiently caching the data pages in memory, reads from disk are relatively low, and so there is no memory bottleneck. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high.
After a long period of time, the ratio moves very little. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. “Percentage of pages found in the buffer cache without having to read from disk. One of those counters was SQLServer :Buffer Manager\Buffer Cache Hit Ratio, described as follows in the Books Online topic for the SQL Server:Buffer Manager Object: Many years ago, when I first started working with SQL Server, there were a number of performance counters that were on the radar of all conscientious DBAs, and were used to track SQL Server performance and assess the general health of a server. Great SQL Server Debates: Buffer Cache Hit Ratio - Simple Talk Skip to content
0 kommentar(er)
