How does SQL Server determine memory bottlenecks?

Detect memory bottleneck in SQL serverby: Kasim wirama, mcdba

 

This article, I wowould 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 tuning Ming 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 busy 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/3 GB options
3. 3. Switch to 64 bit machine that have 8 TB memory address space.