SQL Server Page Swaps Handling and Avoiding

In SQL Server page swapping is one of the most destructive events that can occur within your environment. The reason is that SQL Server maintains several caches with its largest one being the buffer pool, which it uses for fast data access. By costing out the expense and frequency of retrieving data, SQL does it’s best to make sure those pages that are accessed frequently, or take a heavy toll to retrieve from disk are sitting in memory. When swapping occurs Windows takes data supposed to be in memory and places it on to disk. When SQL Server asks for data from windows which it assumes to be in memory, the OS goes to disk to get it. Doing so blows all the memory management algorithms SQL is designed to use. To compound this issue, windows algorithm for swapping is much different then SQL Servers for flushing to disk. Hence the cost of retrieving data from the swap file is often more expensive in comparison to SQL Server simply flushing it to disk, and retrieved it in a normal IO read. Once your server starts getting into a swapping condition, it’s difficult to recover, but there are a few tricks to recover and prevent this.

Setting Up Your SQL Server to Avoid Page Swapping
First you need to understand the Min/Max memory settings within SQL Server. This is actually the buffer pool setting. Most of your memory used in SQL Server comes out of here but not all of it, and manipulating this setting is the key to having an efficient system. When setting up your memory in SQL Server do not use the default or affinity setting as the max. The reason being is if SQL Server believes it has this much memory it will quickly surpass the amount of physical memory you have on the box. The word from Redmond will be use the default and they’ve figured out not to page with it, but I’ve yet to see a system get it right. This is a statement not out of arrogance but experience.

Understand that the other processes on your box that needs memory. The Windows OS takes 2 GB off the bat. If there are other apps running on the box make sure they are accounted for. As an enterprise DBA I make sure only SQL Server is running on the server, but budgetary constraints might not make this possible. However put only what you need. Don’t chew up 200 MB of memory running a web browser. Understand that your memory setting is for the SQL Server engine, not it’s add-on features that come at of the box. Reporting Services, Analysis Services, SSIS, Full-Text search, all run in there own memory space and should be accounted for if they’re sharing the environment. Also understand processes within SQL Server will run outside the memory set for your buffer pool (Max Memory) setting, such as extended stored procedures, CLR’s, linked servers and even large query plans. In SQL Server 2005 you can actually see how different caches use the different portions of memory. The best tool to use is the “sys.dm_os_memory_cache_counters” DMV which gives a breakdown of memory usage. The single_page_kb fields points directly to the buffer pool or the max memory setting in your server configuration. The multi_page_kb field points to memory being used outside the buffer pool, or reserved memory. In SQL 2000 and before this was known as the MTL (Memory to Leave) area. By using this DMV you can determine how much of your data is residing outside the buffer pool.


Unfortunately 64 bit systems aren’t as forgiven as 32 bit systems. Under 32 bit systems, changing the max memory system down to a reasonable level, will often kick the system into making the proper adjustments on the fly. The 64 bit systems seem to have more trouble in this area. Once a SQL Server data page gets mapped to a swapped page, the OS wants to continue to map that page and tweaking the buffer pool, doesn’t provide the immediate relief you need. The ultimate fix here is to restart SQL Services, with the new max memory configuration, as doing so wipes out any mapping between SQL and Windows for memory mapping. However this is a tall task to do on a production system with dollars on the line. The cheat here is too flush your buffer cache until it’s appropriate to restart SQL Services. The command to do this is “DBCC DROPCLEANBUFFERS”, for good measure I’d drop the proc cache as well. Make sure prior to running this you’ve made your max memory changes that are appropriate for your system. There is of course a hit here, which is all data that once resided in memory needs to be reloaded, so you’ll see some sluggishness after the command, but data is quickly reloaded. Before leaving you I’d like to give some suggestions in setting the Max Memory configuration. My personal preference is to give 2 GB’s to the OS, and an additional ½ GB for every 8 GB’s of memory. On a system with 8 total GB’s I give the Max Memory config 5.5, for 16 then it’s 13GB, for 32 it’s 29GB, and so on.