Configure SQL Server 2005 to Load Entire Database Into Memory? 1

[JohnBates]

hi everyone,

This is SQL Server 2005 Standard on Windows Server 2003 Enterprise.

I have a database that is performing poorly primarily due to not having enough physical disks.

But the server does have lots of memory - 24GB. The database datafile is 16GB.

** Is there anyway to configure SQL Server Std to load the entire 16GB database into memory to improve performance? ** (I do have a few other databases but this one is definitely the heavily used db and is causing the slow performance.)

Thanks for your opinions. John

 

[bborissov]

No.
But you should seriously start thinking about your index strategy and maintaining you database.
You could add a maintenance plan to backup your DB, shrink the LOG file, rebuild indexes and recompute statistics (these are on top off my head).
Rebuild the TEMP DB is other option. Unfortunately I don't know other way that stop the service and then start it again (I am not a DBA )

Check execution plan of every query you use in your application (or at least for these you use often) and try to make them to use INDEX SEEK instead of Table SCAN or something. But do not use indexes everywhere, they will slow down your INSERT/UPDATE/DELETE statements so you must be very careful.
And as I said that is on top off my head. If I can give you more, I will (but I doubt because I am too sleepy right now ) I hope that some real DBAs will give you more sensible answer.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mrdenny]

As the system runs SQL will end up loading most of the database into memory.

It will still need access to the disks for all the writes.

I would start with looking at your database index setup. You probably need to add more indexes to the database to better optimize your queries.

Denny
MVP
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