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

In SQL Server 2005 standard edition, sql can use all the available memory and there is no restriction like sql 2000.

But you can't ping whole database into memory, because sql takes the memory as needed and when you run the query/procedure it will load the data into memory...sql will flush the memory if it needs memory to accomadate other results...

I didn't understand your "database that is performing poorly primarily due to not having enough physical disks." remarks... because if the space is not available is nothing to do with perfomance. It might be some other issue...either poor indexing and/or fragmentation and/or parameter sniffing etc...

Check how much memory SQL is using perfmon Total server memory and Target Server Memory...

If you are not set the max server memory then set it to 20 GB....

Have you enabled AWE?

Mohammed is correct can you explain what exactly you mean by  "database that is performing poorly primarily due to not having enough physical disks."

Thanks Rohit and Mohammed.

Not enough physical disks - I mean the database is on a single disk. It is large enough, there is no space issue, but you get better performance by spreading the database across several physical disks.

AWE is not enabled. Do I have to have AWE enabled for SQL Server 2005 Std to use > 3GB of memory? I have it set to "dynamically adjust the memory allocation".

Thanks, John

When you don't have the disks, it should be fine as long as you optimized your sql code as well as we code...

You are using windows 2003 EE so it is supported upto 32 GB.

Enable AWE and configure min and max server memory to 1 and (Total Server memory-4) respectively...and read the following...make sure you reboot your server after doing it...and use perfmon to check the memory usage by sql server...

 http://msdn.microsoft.com/en-us/library/ms179301(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms190673(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms191481(SQL.90).aspx