SQL 2005 Standard Edition 64bit not using all available memory

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » SQL 2005 Standard Edition 64bit not using all...

SQL 2005 Standard Edition 64bit not using all available memory

Rate Topic

Display Mode

Topic Options

Author

Message

ben.liu99

Posted Wednesday, January 11, 2012 9:00 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 7:16 AM
Points: 35, Visits: 154

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 Reserved 8479136
VM Committed 1745976
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

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!

Post #1234073

spaghettidba

Posted Wednesday, January 11, 2012 10:35 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 4,804, Visits: 8,074

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?

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1234187

ben.liu99

Posted Wednesday, January 11, 2012 5:19 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 7:16 AM
Points: 35, Visits: 154

Memory is the first thing I have noticed. Sorry what is PLE?

Post #1234483

spaghettidba

Posted Thursday, January 12, 2012 1:59 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 4,804, Visits: 8,074

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?")

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1234571

Grant Fritchey

Posted Thursday, January 12, 2012 6:09 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383, Visits: 25,189

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1234702

« Prev Topic | Next Topic »

Permissions