Memory Usage in SQL 2005

Question

Memory Usage in SQL 2005

trebor69

Valued Member

Points: 55
More actions
August 26, 2009 at 6:31 am

#204095

Hi,
We recently moved our SQL 2000 db's to the following machine:
64-bit windows Server 2003
Quad Core
4GB RAM
SQL 2005 Standard edition (64-bit)
Databases running in sql 2000 compatibility mode
We are experiencing a gradual slow down in performance, as SQL memory usage increases, which can only be solved by a re-boot. I
have tried limiting SQL to 2GB which helped the server but not SQL which just got slower quicker.
I've read that SQL should free up it's memory when not being heavily used but this is not the case. It's almost like SQL is caching every request and keeping it in memory until it's full. It dramatically slows down when it tries to use the page file and we get timeouts.
I've already tuned the db and gone thru best practices, etc..
I know there are other posts related to this, but my specific question is how can I manage the memory usage better?.
Options i have thought of are:
Increase physical memory (is 4GB enough?)
Upgrade to SQL Enterprise edition (i've read it manages memory better than standard)
upgrade db's to SQL 2005 compatibility
Hit it with a large hammer!
Any help appreciated.
Thanks
Rob

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736059 More actions · Answer 1

SQL Server doesn't really release memory to the OS, but it will replace the memory it uses with new data as needed.

It is possible that you need more memory. There's no way to say that a server needs XX memory from a spec. It depends on the load and queries that you throw at it. You might need more memory.

When you say slow down, are you objectively measuring the same queries run after reboot and some time later? Or do you mean that there are other applications on the server and everything slows down.

patrickn SSC Eights! Points: 905 More actions · Answer 2

For what it is worth: We just had a similar issue. Take a look at your logs. Are you getting a lot of batch type error messages?

If so, we changed our AWE flag in SQL 2005. This helped.

Take a look more at this post that from our issue. The mentors in the thread had good advice that might help.

http://www.sqlservercentral.com/Forums/Topic751161-360-2.aspx#bm769420

Good luck.

Amit Singh Ten Centuries Points: 1295 More actions · Answer 3

are you getting any SQL Server memory paged out ? you could check in the error logs for a message like "A significant part of sql server process memory has been paged out" and if that is the case then

go through the article

http://support.microsoft.com/kb/918483

best thing would be to Lock pages in memory for standard edition.

if your app is using a lot of adhoc queries.... then you could tune them using guidelines in http://technet.microsoft.com/en-us/library/cc966425.aspx

I have seen adhoc queries taking up lot of plan cache size and this plan cache in SQL Server 2005 resides outside of SQL Server Buffer pool that you defined through max server memory. So that could be the culprit.

just throwing some ideas to start looking out for.

-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]

Amit Singh Ten Centuries Points: 1295 More actions · Answer 4

trebor69 (8/26/2009)
We are experiencing a gradual slow down in performance, as SQL memory usage increases, which can only be solved by a re-boot.

if right now you are only using reboot, then you could try running these commands as a part of a job that runs every 30 mins or every hr

DBCC FREEPROCCACHE

GO

DBCC FREESYSTEMCACHE ( 'ALL')

off course this would cause things to recompile, it is still better than a reboot and might help your case. And if it does help, then you should be looking out for memory bottlenecks.

-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Here are a couple of things:

A way to determine memory pressure:

select * from sys.sysperfinfo where counter_name like '%page life expectancy%'

if the counters are below 300, you should probably get more memory.

I like the Idea of converting the databases to 2005 rather than 2000 compatible. 2005 sp2 changed how memory usage was done so servers with less memory might be able to handle the load better.

Memory and Plan Cache

This might help out with the quick slowdown.

And lastly, I would baseline some procs and retest them several times after certain intervals to qualify the slowdown.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

trebor69 Valued Member Points: 55 More actions · Answer 6

Many thanks for all your replies, really useful info.

It does appear that the memory is being throttled from running the suggested queries, so we're gonna chuck some more memory at it initially. Gonna also try upgrading to SQL 2005 compatibility, although one of our systems has hard coded *= syntax so need to fix these first!. I'll then look at the 64-bit/AWE specific changes and the various other suggestions.

I'll update this post with anything that proves valuable.

Thanks

Rob

patrickn SSC Eights! Points: 905 More actions · Answer 7

patrickn

SSC Eights!

Points: 905

August 27, 2009 at 10:17 am

#1045663

Sounds like a good plan.

Good luck.