Database Reference
In-Depth Information
start your settings for Max Server Memory. It's important to remember that with Server
Max Memory set, the balloon driver's ability to reclaim memory will be affected.
Because all the memory is accounted for, there will be no memory available for the
balloon driver to reclaim, which is why for non-mission-critical systems this may not be
advantageous, especially if you are trying to achieve high consolidation rates. As DBAs
and system administrators, we know managing the infrastructure is all about weighing
the pros and cons of each choice. With a tier-1 production database, it's pretty cut and
dry. Make sure it gets the resources it needs, when it needs them.
Figure 7.8
SQL MAX MEM settings.
The settings in
Figure 7.8
are appropriate for a SQL Server database on a physical
server. In a virtualized environment, it's important to remember that you are in a shared
environment. So when you use the quick reference guide in
Figure 7.8
, it should be done
within the memory size of the virtual machine that houses the database, not within the
memory of the physical server that hosts all the virtual machines. Therefore, if you are
sitting on a physical server with 128GB of RAM and the mission-critical database is
housed within a 32GB virtual machine, the setting for Max Server Memory should be
28GB.
Large Pages
Another place where you can squeeze additional performance from your virtualized
tier-1 SQL Server database is through the use of large pages. For SQL Server to use
large pages, you must first enable it through the trace flag -T834.
Figure 7.9
illustrates how you enable large pages for SQL Server using trace flag -
T834.
