SQL 2005 Memory usage on Windows Server 2008 Standard 64 Bit SP1

I recently installed a new server for a client to replace their old SQL server.  It's a huge improvement as it's not crashing anymore but users have complained about slowness.  This server is Windows 2008 Standard 64Bit SP1.  It originally had 4GB of RAM.  The memory was always maxed out in task manager so we installed an additional 4GB for a total of 8GB of RAM.  They are using SQL 2005.

In task manager, The physical Memory usage is always maxed out.  This is true even at night when no one is using the server.  I've read numerous articles on paging file size and thought I'd ask if anyone has encountered or has recommemdations for this issue.  Currently, the virtual memory is set to "Automatically manage paging file size for all drives".  Any thoughts or suggestions would be greatly appreciated.
spirestechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andyalderCommented:
With SQL RAM is always maxed out, it takes it as it wants it and doesn't give it back unless there is demand from other applications. It shouldn't be paging though so page file size shouldn't matter much.

If you have more RAM and presumably a better CPU than before but it's slower than perhaps there's something else that's worse than before, what was the disk subsystem and network before/after?
0
spirestechnologyAuthor Commented:
The previous server was built on what appeared to be spare parts and it was constantly crashing.  The owners bought new Dell Poweredge R410 servers.  Unfortunately, they did that before I came in and had no say in the matter but it's much better than what they had.  Don't have the exact specs on me but will post them later wheh I go in tonight.  It is a 64Bit machine.
0
Anthony PerkinsCommented:
If I was to guess, this has nothing to do with memory and everything to do with poorly written queries and/or an ill-conceived database.  Yes, you can thorw more hardware at it, but in the end you will have to roll-up your sleaves and do some work.

But to answer your specific question SQL Server by default uses all available memory and the paging file has nothing to do with it and should be kept at a minimum (2GB).  That is provided SQL Server is installed as recommended as a standalone server and is not sharing CPU/memory with other apps.
0
ON-DEMAND: 10 Easy Ways to Lose a Password

Learn about the methods that hackers use to lift real, working credentials from even the most security-savvy employees in this on-demand webinar. We cover the importance of multi-factor authentication and how these solutions can better protect your business!

LouisvilleSystemsJockeyCommented:
Open SQL server Management Studio, Rigth click the server and go to Properties.  On the memory tab you will see a crazy number listed for max RAM.  If your server has 8GB, I usually cut it in half and give it a MAX here of 4096mb.  This seems to keep the RAM consumption in check.  See the attached image.  I hope this helps!

Lville Systems Jockey Example of RAM Throttling
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>If your server has 8GB, I usually cut it in half and give it a MAX here of 4096mb. <<
The only way I would cripple SQL Server this way would be if I had other apps installed (not recommended) on the same server.  If you want to allow some free memory, than I would set it to something like 7GB.
0
spirestechnologyAuthor Commented:
Do you think having the log files on the same physical drive is contributing to the problem?  They only came with 2 x 300GB drives which I mirrored using RAID1.  This is a dedicated SQL server so nothing else runs on it.  

I'll make the memory change tonight when I go there.

Thanks
0
andyalderCommented:
How many disks were in the old server that performed OK when it wasn't crashing?
0
spirestechnologyAuthor Commented:
When I was hired, the server has already crashed and all attempts to restore data from the drives failed.  I was able to find a backup that was about a month old and they had to manually re-enter everything they lost.  The server had 4 drives but I don't know how it was configured.  I'm guessing RAID 5 since that is how there other servers were setup.
0
andyalderCommented:
Twice as many disks could nean twice as fast disk subsystem, but having re-entered a month of data would also make the transaction log a bit big unless it's backed up. Might need to recreate indexes too but I'll leave that one to a SQL expert.
0
spirestechnologyAuthor Commented:
yeah, they have a SQL programmer they use, I just built the server they had.  Thanks for the info!
0
andyalderCommented:
You may as well get another days consulting out of them, adding another 2 disks in RAID1 for the data so it's not on the same disks as the logs would speed it up (you'd suggested that already) , another 4GB RAM wouldn't hurt either although it'll still use it all, that would be one DIMM per memory channel assuming single CPU. Looking at the disk queues from a remote workstation with perfmon looks good when on site and does yield useful info.

I suspect it'll speed up on its own though, they're probably still re-entering data and running more queries than normal to catch up with the downtime so it's doing more work than normal even though it's xmas/newyear week.
0
spirestechnologyAuthor Commented:
This is just what we needed and the server is working great.  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Anti-Spyware

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.