Tip

Dynamic or static memory allocation in SQL Server?

SQL Server has two basic administrative options that control how much memory is used by the program: dynamic and static allocation. Dynamic allocation allows the administrator to declare a range of memory sizes; SQL Server can allocate up to the maximum amount of memory allowed for its use, and (in theory) releases memory when not in use. Static allocation creates a fixed-memory space for SQL Server to work with -- no more, no less.

By default, SQL Server is set to dynamically allocate up to all of the available physical memory on the computer it's run on. Many administrators who see SQL Server's memory usage swelling over time may attribute this to a malfunction or a memory leak, but this is simply the program's design. SQL Server is meant to be run on its own computer whenever possible, and so makes use of all of the available memory for the best possible performance. If SQL Server is running on its own machine, then let it allocate and release memory as needed.

On a small-business-server box, where SQL may run in conjunction with other programs like IIS, administrators may be tempted to set SQL Server to a static memory size to keep it from eating up shared memory. This doesn't always pay off as intended. For one thing, it's all too easy to set the memory ceiling too low and not give SQL Server enough available memory for things like the transaction log or the query execution cache. The only way for SQL Server to get the memory it needs for those operations is to swap

    Requires Free Membership to View

other pages out, which is a slow process.

There are a number of ways to calculate the best memory allocation. If you have a predictable user load, go by the maximum number of users. Microsoft recommends using at least 4 MB (24 KB times the number of users) as one possible formula for the dynamic maximum size. If your user load varies widely -- as may be the case when you're serving a database connected to the public Internet through an IIS front end -- live statistics will help better than guesswork. During peak hours, gather the cache-hit ratio and page faults per second performance counters for SQL Server. If these stats show that SQL Server is swapping a great deal, increase the maximum memory size until the swapping drops off. Swapping once per second or more is bad.

Another alternative is to enable the "Reserve physical memory for SQL Server" option, which prevents SQL Server from swapping out its allocated memory to disk even when other applications could use it. This can be a double-edged sword: It can either enhance performance considerably or detract from it. On systems with plenty of RAM to spare (1 GB or more) it's worth trying, but it should not be used when other critical processes may need to use memory explosively (and SQL Server may be forced to give up some of its own if needed). If SQL Server is run on its own box, this is worth enabling to maximize performance.


Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators -- please share your thoughts as well!

This was first published in December 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.