Managing Memory
Let us discuss on some on some aspects of performance tuning and optimization of sql queries. The following terms are quiet relevant in this regard. Although we do not always think about these counters unlesss we are seriously having issues with the sql server memory or the performance of a strong query. Buffer Cache Hit Ratio The buffer cache is a syndicate or collection of buffer pages. The data pages occupies the biggest part of the memory and pages are mostly read into this cache. In OLTP systems this counter value should be as high as possible. Ideally, 99 percent in most cases. It is exactly the reverse in OLAP or DW environment where cache requirement although is there but as most of it is bulk data (billions of data records), cache requirement is less. Also frequency of requests coming in can be much lower than e.g. transactions in bank. If Buffer cache hit ratio value is low that means, only a few requests could be served out of the buffer cache, with the rest of the ...