January 11, 2012 at 9:00 am
We have a rather busy production server which is running SQL2005 SE 64bit on Windows 2003 64bit. The server has 8GB memory and 8 CPUs. However SQL is only taking up less than 2GB constantly. The users are complaining the system is dog slow.
I've checked the memory settings, and there are no restrictions or anything.
DBCC Memorystatus
Memory Manager KB
VM Reserved8479136
VM Committed1745976
AWE Allocated0
Reserved Memory1024
Reserved Memory In Use0
We use Spotlight to monitor the server, while on SQL side only 1.8GB is used, on the Windows side 98% of physical memory has been used.
We also have a clone of the server with exact setup apart from less memory 4GB and CPU power, and no users are working on it. The memory usage is at 2.7GB, which I think is more reasonable.
Please help!
January 11, 2012 at 10:35 am
Something else is eating all the memory, apparently.
What else is running on this box?
Also, are you sure you're having memory issues? What's the PLE for this instance?
-- Gianluca Sartori
January 11, 2012 at 5:19 pm
Memory is the first thing I have noticed. Sorry what is PLE?
January 12, 2012 at 1:59 am
Page Life Expectancy.
It's one of the easiest methods to detect memory pressure.
Here's a good article on the subject:
http://www.sqlskills.com/blogs/jonathan/post/Finding-what-queries-in-the-plan-cache-use-a-specific-index.aspx (Read the paragraph "What’s Wrong about Page Life Expectancy >= 300?")
-- Gianluca Sartori
January 12, 2012 at 6:09 am
I just finished writing part of the chapter of my new book on troubleshooting memory issues. Let's see how it works.
First, check Memory:Available Bytes. Is that low? If so, you may have external memory problems. You need to look in the Windows OS. If not, Check Paging File %Usage Peak and Paging File %Usage. Are these values higher than normal? If so, again, you have external memory issues, possibly related to the page file. Look in the OS. If no, in DBCC MEMORYSTATUS is Committed above Target? If so, you may have internal memory pressure, use sys.dm_os_memory_brokers to identify them. Assuming all that's false, do you have high Process:Private Bytes? If yes, you may have processes running internally in SQL Server consuming memory. Check sys.dm_os_memory_clerks to identify the culprits. Otherwise, you need to look in the error logs for SQL Server & Windows to see if there are memory errors or go through the process of checking for VAS memory issues in SQL Server.
Before all that, just make sure that you don't have max allocated memory set to the wrong value for SQL Server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply