Database Reference
In-Depth Information
If your virtual machine that houses the database is configured at 32GB of memory, you
can dynamically allocate an additional 32GB to it. After a slight delay, the virtual
machine will have access to 64GB of RAM.
Using the SP_Configure command within SQL Server, you can also dynamically adjust
Server Max Memory. With a virtualized database, resources such as vCPU (virtual
CPU) and virtual RAM are just a click away. If you choose to use the capability to hot-
plug a CPU, remember it affects your ability to use NUMA.
SQL Server 2014 In-Memory Built In
The Microsoft website talks about SQL Server 2014 and the new in-memory
capabilities built in to the core database engine for online transaction processing
(OLTP) and data warehousing (
http://www.microsoft.com/en-us/sqlserver/sql-server-
storage array. If we could keep all our needed data in memory, the database
performance improvement would be enormous.
According to the Microsoft data sheet titled “SQL Server 2014 & The Data Platform,”
you will see up to a 30× performance gain using SQL Server 2014 in-memory
capabilities. It claims you will see in average of 10× performance gains for existing
SQL Server applications. When we think about the speed of reading from memory
versus physical I/O, a 10× or more improvement seems very attainable. It's important to
note that as of the writing of this topic, SQL Server 2014 was in General Release, so
your individual mileage with the product may vary. However, our experience tells us
that the numbers quoted should be very attainable.
Summary
In this chapter, we discussed the IT food groups with a focus on memory. Memory is
one of the most critical resources you have available. Everyone from hardware vendors
to software vendors are finding new ways to leverage memory to speed up performance.
The newest version of SQL Server will have an in-memory database that is able to
perform magnitudes faster than its predecessors by levering memory as a resource.
We stressed how important it is that you have the right balance of resources if you want
to optimize the performance of your virtualized SQL Server database. This is especially
important in a shared environment. By using techniques such as setting memory
reservations, you can ensure that mission-critical resources have the resources they
need when they need them, even in a shared-resource environment.
We discussed the many tools available to the hypervisor, such as TPS and ballooning,
to help ensure the hypervisor is getting the most leverage out of the physical memory
available to it. We also discussed NUMA and a number of other things you need to take
into consideration when you virtualize your production SQL Server database.
