Detect Memory Bottleneck in SQL Server

By : Kasim Wirama, MCDBA

?

This article, I would like to give information about memory bottleneck at SQL Server. The easiest way to know whether your SQL Server undergoes memory bottleneck is by looking at Task Manager, tab Performance, Physical Memory section. Compare the Total and Available value, as long as Available is over 100 MB, it is still okay. If Available value is under 10 MB, your database server box is definitely under physical memory pressure. Besides physical memory pressure, there is virtual memory pressure. Let’s take a look for these kinds of pressure and see what solutions addressed to the corresponding problems.

?

You can get information about physical memory pressure by looking at these performance monitor counters: Memory:Available bytes, SQL Server:Buffer Manager: Buffer Cache Hit Ratio, Page Life Expectancy, Checkpoint Pages/sec, and Lazywrites/sec. You have your system performing well if value of first two counters are high and last two counters are low.

?

You can check how many memories allocated for buffer pool by issuing DBCC MEMORYSTATUS query in SSMS. Compare value between Committed and Target, if Target value is low, it indicates external physical memory pressure. You need to find out whether other application that causes external memory pressure, besides SQL Server, by looking at maximum memory consumed in Process tab of Task Manager window. If other application causes external memory pressure, it is advisable that the application is separated from database server box, or you add more memory. You need to check internal memory pressure by looking at percentage of stolen page to total committed page from DBCC MEMORYSTATUS. Percentage value is greater than 75% or 80% indicating internal physical memory pressure. ?Interesting thing about internal memory pressure is that you need to find out what components causing so many stolen memory page by querying this DMV below :

?

SELECT type, SUM(single_pages_kb) FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC

?

From query above, single page allocation takes memory from SQL Server buffer pool, whereas multipage allocation allocates memory outside buffer pool. Multipage allocator possibly causes internal memory pressure. You can get information about memory allocated to multipage page allocation by querying DMV below :

?

SELECT type, SUM(multi_pages_kb) FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb != 0
GROUP BY type
ORDER BY SUM(multi_pages_kb) DESC

?

To detect virtual memory pressure, you can look at performance counter name here : Paging File: %Usage and Memory:Commit Limit.

Some solutions addressed to virtual memory pressure. They are:

1. 1. ?????? Increase size of page file
2. 2. ?????? Use /3GB options
3. 3. ?????? Switch to 64 bit machine that have 8 TB memory address space.

SQL Server 如何確定內存瓶頸