2005 Memory Usage -

[sanjdhiman]

Hi all,

I have MS SQL 2005 Standard installed.
Running on Windows 2003.

16GB of RAM

I understand that in 2005 they changed memory management so that they would not have an issue with O.S. Ram memory being relinquished by SQL server. in 2000 it never gave memory back to the O.S. until you restarted the SQL services.
In 2005 I have not seen my memory (RAM)usage go above 1.7GB. Which is weird as I have set it go to use maximum RAM.

HAve you guys experiences similar problems.

Also on a second note, there are 40 OLTransactional db on t he box, what benefit would I get to move to Enterprise. I am getting a lot of timeouts across all databases.

Thanks

Sanj

 

[mofusjtf]

If you are using SQL 2005 Standard on Windows Server 2003 Standard your server can only use a maximum of 4 GB of memory. SQL supports as much as the OS can use.

SQL by default will only use 2GB of RAM leaving 1GB for the OS and essentially wasting 1Gb. You can add the /3GB switch to the boot.ini file which will allow SQL to use 3GB of RAM instead of the standard 2GB.

Your timeout issue is probably a resource issue since 12 GB of RAM in your server is left unused. You might want to consider upgrading to Enterprise Editions of each.

And it sounds like your using Task manager to watch memory useage. You're better off using performance monitor to get a true representation of the memory SQL is actually using.

 

[mrdenny]

To allow Windows to use more than 2 Gigs of RAM you need to add the /PAE and /3GB switches to the boot.ini file on the server.

You then within SQL need to enable AWE and restart SQL Server. This will allow SQL to use over 2 Gigs of RAM (up to the OS limit).

SQL will pretty much not release memory back to the OS unless the OS is out of memory. As long as the OS doesn't tell SQL to release memory it will keep the memory for it's use.

An upgrade to Enterprise edition will get you the ability to lock pages in memory (keep them from being paged out), and will allow you to use more CPUs.

A better upgrade might be moving to a 64bit enviroment. This will increase the amount of procedure cache you can have as the 32bit edition of SQL can only put it's procedure cache in the non-awe memory space (as 64bit doesn't have, use or need AWE this isn't a problem).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mofusjtf]

Mr. Denny,

If I am not mistaken, when you enable AWE memory in SQL you need to set a max memory size because SQL no longer dynamically allocates memory.

So useing the /PAE switch on Standard edition will allow windows to utilize more than 4GB of RAM? Despite the limitations MS indicates? Interesting.

 

[mrdenny]

You always need to set the max memory size otherwise SQL will use all the memory on the server.

SQL Always dynamically allocated memory.

No, the /PAE switch will not allow Windows 2003 Standard to go above the 4 Gig limit. The OP has not clarified if he's using Standard or Enterprise.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog