23 September,2014 by Tom Collins
Question: A SQL Server has decreased in response time. Is there memory pressure on SQL Server? Is the server paging?
The server has 16383 megabytes of physical memory, but only 10 megabytes free memory are available.
Answer: As more memory is consumed on a server , swapping to disk becomes a real possibility. Swapping to disk kills SQL Server performance.Read more on SQL Server Performance Killers
1) check the SQL Server is capped with a max memory.
2) check to see if free memory is at least over 500 MB. In my experience, when a server begins to drop below 500 MB , it is an indicator that it’s about to start using the page file. Basically – operations that normally were running to memory , begin to read \ write to disk.
A quick way to check is to use Task manager |performance tab. It gives you a quick visual guide on the state of free memory.
Focus on what processes are utilising memory and act on them. Another option is to increase memory.
SQL Server – Memory: Pages sec, memory pressure and thrashing
Sys.dm_os_memory_clerks and AWE memory allocation
SQL Server – Windows Virtual memory and paging file location
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |