Analyzing SQL Server memory utilization is one of the techniques the SQL Server Customer Advisory team uses to help customers discover and rectify various performance problems. In "Minding Memory," September 2004, InstantDoc ID 43419, we looked at how SQL Server utilizes memory and how you can monitor that utilization. Now, let's learn how to use information about memory utilization to detect and correct performance problems.
Looking for Memory Pressure
Sometimes, application performance problems might first manifest themselves in ways that end users can see, such as poor response time or poor throughput. DBAs and developers then start investigating the problems to find the underlying causes. In other cases, an observant DBA might notice an off-balance system characteristic that end users don't see, such as high CPU usage or a high disk-response time, and start looking for causes. Regardless of when you start your investigation, one of your first steps should be to find out whether your system is under memory pressure. Because SQL Server is designed to dynamically manage memory, finding this answer isn't as straightforward as it might seem. For example, on a system devoted to SQL Server, the SQL Server process (sqlservr.exe) commonly acquires most of the memory on the system. This behavior is normal because SQL Server is designed to use as much system memory as possible, as long as no other applications are competing for memory and SQL Server memory growth isn't restricted by the max server memory configuration parameter. Thus, the amount of memory the SQL Server process has allocated doesn't tell you whether the system is under memory pressure. (For information about memory-utilization differences in 64-bit and 32-bit SQL Server, see the Web-exclusive sidebar "64-Bit vs. 32-Bit Memory Management" at InstantDoc ID 44557.) And although you might think you can detect memory pressure by looking at whether the SQL Server process is paging, this method doesn't work either because SQL Server is designed to avoid paging.
So how do you detect memory pressure? You can start by looking at the Performance Monitor (PerfMon) counters SQL Server:Memory Manager:Target Server Memory and SQL Server:Memory Manager:Total Server Memory. Target Server Memory tells you how much memory SQL Server would allow itself to use based on external factors such as other applications' memory requirements, total system memory, and the max server memory configuration parameter. Total Server Memory tells you how much memory SQL Server is currently using. If over time the value of Total Server Memory is consistently lower than the value of Target Server Memory, there's no memory pressure on the system: SQL Server isn't using as much memory as it could if it needed to. However, if Total Server Memory is equal to or greater than Target Server Memory, memory pressure could be the cause of the problem. To determine where the memory pressure is coming from and resolve it, you need to examine each of the ways SQL Server uses memory, which we described in "Minding Memory." Let's look at what you can learn from memory utilization in the database page cache, the plan cache, the query workspace, and locks.
Database Page Cache
Counters under the SQL Server:Buffer Manager PerfMon object provide clues about memory pressure in the database page cache. First, look at the Database pages counter and compare it to the Total pages counter. If Database pages is a significant fraction (more than 50 percent) of Total pages, you know you have a data-intensive application. Two other counters, Buffer Cache Hit Ratio and Page life expectancy tell you how well the page cache is coping with the workload. The Buffer Cache Hit Ratio tells you what fraction of page requests SQL Server is servicing out of the cache and correspondingly, what fraction of page requests are going to the I/O system. Keep in mind that with a Buffer Cache Hit Ratio of 99 percent, you incur 10 times as much I/O as you would at 99.9 percent, and at 90 percent, you incur 10 times as much I/O as at 99 percent. The Page life expectancy counter tells you how long you can expect a page to reside in memory under current conditions. Low values (typically, less than 300 seconds) mean the system is "churning" the buffer pool at a high frequency, a potential sign that memory pressure could be causing performance problems.
If your system has a low Buffer Cache Hit Ratio or low Page life expectancy, check your I/O system to see how well it's coping with the I/O workload. In particular, a high value (typically more than 10ms) for the Avg. Disk sec/Read or Avg. Disk sec/Write PerfMon counters in the PhysicalDisk object might indicate that that the I/O system is struggling. Be careful to inspect these counters separately for each drive letter; if you look only at the averages, the numbers you see might mask a problem on a particular drive.
If you see low values for the Buffer Cache Hit Ratio and Page life expectancy counters, you might consider adding more memory to boost application performance. However, before you go cut that check for more RAM, a little investigation of the underlying causes is in order. Perhaps your buffer cache suffers because SQL Server is using too many table or index scans instead of using index seeks to restrict access to specific rows. Scans, especially of large tables or indexes, are likely to cause a drop in the Buffer Cache Hit Ratio because it's unlikely that all the pages of a large object can remain cached in memory. Scans might also cause a drop in Page life expectancy by forcing out other pages residing in the buffer cache. Index seeks restrict access to a particular range of rows and thus cause fewer page reads. The SQL Server:Access Methods:Full Scans/sec PerfMon counter lets you monitor the number of full scans SQL Server is performing in a second. If you determine that overuse of scans is a problem, you can use SQL Server Profiler to narrow down which SQL statements are causing the scans by monitoring the Showplan Statistics event under the Performance category in Profiler. Analysis of the Profiler data might show that you need to add an index to a table or perhaps reorder the columns of an existing index to turn an index scan into an index seek. For information about how to use SHOWPLAN output information, see the Microsoft article "HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later." (You can find the URL for this article in Related Reading.)
Another factor that might reduce the performance of the buffer cache is excessive denormalization, which leads to redundant data and thus increases the overall number of pages in the database. When the buffer cache has to serve more data pages, it becomes less efficient. Another common cause of poor buffer cache performance involves form-based update programs, which display a row to users, who can choose the columns they want to update. Then, the application writes back all the columns to the database instead of updating just the ones the users changed. This kind of application not only wastes CPU cycles on the server by processing updates of unchanged columns, it also causes SQL Server to update all indexes to reflect the potentially changed column values. This behavior causes dozens of unnecessary page reads and writes.
Prev. page
[1]
2
3
next page 
