SQL Server Best Practices: Set a Fixed Memory Size

SQL Server allows you to set a “bottom” and “top” range for memory that the Instance will use. The memory will dynamically expand and contract based on Instance need and system pressure. But this flexibility and ease of configuration can (not always, mind you) come at a cost.

While there are some caveats, it’s a good idea to set the memory SQL Server uses to a “fixed” size, and to set the bottom and top values to the same number. This releases SQL Server from the duty of having to manage the memory, and speeds up the system. It’s especially important when you have multiple instances installed, or you’re using a clustered system.

Now, on to those caveats. Don’t fix the memory for SQL Server so high that the Operating System is starved for resources, because it will then just page out to the hard drive and that’s bad, or it will make the system unstable in extreme cases. I normally leave at least 1GB for the OS, and sometimes, depending on what the system is doing, I give it even more.

Skip to main content