September 24, 2008 at 8:12 am
Guys,
I am running sql server 2000 with 30GB database on windows 2003 server having 2GB ram. The application was running extremely slow and I restarted sql server. After restart
the response times improved and then gradually back to normal self (slow). The memory used by sql server is consistent at 1.6GB.
After from query tuning is there anything I need to look at on the database server and instance level settings to improve the performance.
Any suggestion/inputs would help.
Thanks
September 24, 2008 at 8:20 am
Suggestion: upgrade the RAM to 4 GB. Cheap and effective.
September 24, 2008 at 8:30 am
Glen (9/24/2008)
Suggestion: upgrade the RAM to 4 GB. Cheap and effective.
Agree with the above, but be sure to add the /3GB switch to the boot.ini file or the extra memory won't help.
😎
September 24, 2008 at 8:58 am
Sorry for the short response. I was interrupted while answering.
It looks like that you are experiencing a memory pressure problem. SQL server 2000 does not respond to memory pressure.
You can add /3GB switch as Lynn suggested. You would also have to limit max memory settings on SQL server itself (please, see Slava Oak blog http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx), but ultimate solution would be to upgrade server RAM.
September 24, 2008 at 9:11 am
Hi,
It is pure Memory Pressure problem which we also have faced.. Above said is one of the quick & easy & effective option.. Incase you are not able to do that for immediate effect then try below things...
1. Run profiler and collect statistics for "Object Opened" for 24 hours period. Save data to SQL table.
2. After collecting the data, use group by and see the maximum objects opened and consider evaluating the indexes which can help you more..
3. Also it can be Disk IO also... Again run profiler to see Cache Hit Ratio to see whether it is 100%..
4. Also there are softwares which can help you in releasing memory to OS which are often locked by applications/SQL Server eventhough they are not using it...
5. Note that if Backup and Data and Log files are placed in same disk also this issue is possible to come...
Note:
Run profiler from a server other than this server where issue is happening since this will add to existing OH.
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
September 24, 2008 at 9:21 am
I agree, upgrade the RAM either way
We have a SQL 2008 box on Windows 2003 R2 (all 64-bit) with only 1.5GB RAM
DB is about ~30GB in total, it's slow as hell 🙁
You don't need 3GB switch if you are using 64-bit I believe
may not need AWE, or PAE either.
I always get confused, which is another benefits of running on 64-bit
September 24, 2008 at 9:44 am
True, if running x64, you don't need the /3GB switch, but the OP didn't indicate what version they are running.
We are running a mixed environment ourselves; SQL Server 2005 x32 and x64, and SQL Server 2000 x32.
😎
September 24, 2008 at 9:46 am
Jerry,
SQL server 2000 running on non Itanium server is always 32 bit 🙂
SQL server 2000 Enterprise Edition is ONLY IA64.
SQL Server 2000 Standard Edition is always 32 bit.
September 24, 2008 at 1:59 pm
Does adding 4GB RAM allow sql server 2000 (32bit) to grab more memory
how is this different from sql server 2000 with 64bit with 4 GB RAM
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply