分类： 数据库开发技术
2011-05-04 14:03:41
(17)Have you ever checked out the page life expectancy counter in Performance Monitor's Buffer Manager object? SQL Server Books Online (BOL) says the page life expectancy value is the "number of seconds a page will stay in the buffer pool without references." So, a buffer that has a 300-second page life expectancy will keep any given page in memory in the buffer pool for 5 minutes before the buffer pool flushes the page to disk—unless a process references the page.
This counter can be helpful in determining whether you have a memory problem, giving you a reasonably accurate view of whether your server has memory pressure. According to Microsoft, 300 seconds is the minimum target for page life expectancy. If the buffer pool flushes your pages in less than 300 seconds, you probably have a memory problem. Looking at this value is particularly handy when your page life expectancy is significantly higher or lower than 300 seconds. For example, a customer recently asked me, "Do we need more memory?" I monitored the page life expectancy value for the customer's system, and the value didn't fall below 3000 seconds. That's quantifiable proof that more memory wouldn't help performance. Other customers have an average page life expectancy between 10 and 50 seconds, and they wonder why their servers are slow.
I often see organizations that have plenty of memory add more memory without realizing that it likely won't improve performance much, if at all. If you have a low page life expectancy, simply adding more memory isn't the cure. Memory pressure is a result of such problems as inefficient query plans. For example, a customer that had a page life expectancy in the 50-second range also had SELECT queries performing more than 1.5 million logical reads on its servers. Do the math—that's almost 11.5GB of logical reads on a server that had 700MB of memory. The server might have needed more memory, but by using judicious indexing, we reduced the logical reads for the query in question to about 10.
Memory pressure doesn't mean you need to add more physical memory—it means you don't have enough memory for your workload. You could add enough memory to fix problems such as a single query performing 1.5 million logical reads. But a high page life expectancy value will assure you that adding more memory won't add performance value.
