Optimal Memory Options MS SQL 2005 on Windows Server 2003

Hi All,

We have an ASP.NET application with MS SQL 2005 Express database.

The server OS is a Windows Server 2003 Standard x64 sp . The machine is dedicated to this application and database and runs nothing else.

We have upgraded to 4GB of ram and are considering what would be the appropriate settings for memory options for MSSQL. Given 4GB of RAM on the machine what should the numbers for Minimum server memory and minimum memory per query be? Should I also have a number for query govenor (we have some long running queries)?

Thanks  
JenebyMAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
lofConnect With a Mentor Commented:
Hi JenebyM,

First you need to remember that 1GB for the buffer pool is one of Express limitations. Have a look here for more: http://msdn.microsoft.com/en-us/library/ms345154(SQL.90).aspx

Here you will find information about maximum and minimum memory
http://support.microsoft.com/kb/321363

If you have 4GB RAM and you can use only 1GB and you don't have any other applications running, setting the minimum memory level high my give you slightly smoother performance as SQL server won't free the memory only to get it back again seconds after. But in general if the memory is available anyway there won't be much of a difference.

And now the minimum memory per query. That the number of kilobytes guaranteed for each query. By default it's 1024KB and unless you have very specific queries (very big ones or thousands of small ones per second) you don't need to change it at all.

As always the saying << it ain't broke don't fix it >> is true.  You don't say you have any problems at the moment, so let it work.

And don't trouble trouble until trouble troubles you.

Hope it helps
Lof
0
 
JenebyMAuthor Commented:
lof:

Good references and response. We don't have a specific problem but from our experience the web application can can get memory hungry and we try to ensure we can benefit from every available option that optimizes performance.

The articles indicate we will outgrow the express edition  and its 4GB per database limit quite quickly once we complete migrating all databases from SQL 2000.

In all we have a clear way forward on how to manage this are.

Thanks
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
JenebyMAuthor Commented:
lof:

Good references and response. We don't have a specific problem but from our experience the web application can can get memory hungry and we try to ensure we can benefit from every available option that optimizes performance.

The articles indicate we will outgrow the express edition  and its 4GB per database limit quite quickly once we complete migrating all databases from SQL 2000.

In all we have a clear way forward on how to manage this are.

Thanks
0
 
lofCommented:
Just a thought: If you are migrating why won't you migrate already to 2008? It has also an express edition which limitations are same as in 2005.
0
 
JenebyMAuthor Commented:
lof:

Having made this "discovery" on 2005 limitations we are looking at 2008 standard edition licensing  as we speak. 2005/08 Express editions with this limitation will always present a problem at some point.

Thanks again for your help. We are studying the 2008 overview (we should have done this more thoroughly before embarking on 2005 express).
0
All Courses

From novice to tech pro — start learning today.