Database Reference
In-Depth Information
Chapter 7. Architecting for Performance: Memory
In
Chapter 5
,
“
Architecting for Performance: Design
,” we introduced the concept of the
“IT food group,” shown in
Figure 5.1
.
We discussed how important it is to provide your
database the right balance of memory, disk, CPU, and network. Without enough of any
single one of these essential resources, you will never have a properly performing
system. We also stressed how important it is for you to balance these resources to
ensure you get optimal performance from your virtualized database. All this must be
done in the context of a shared resource environment.
In this chapter, we focus on leveraging memory as a resource, with the goal being to
optimize the performance on your virtualized SQL Server database. Topics to be
discussed in this chapter include the following:
How to properly set SQL Max Memory
Benefit of locking pages in memory
NUMA (non-uniform memory access)
Memory reservations
Swapping, ballooning, and transparent page sharing
Large memory pages
How many VMs can you put on a physical host?
SQL Server 2014 in-memory database
Memory
One of the most critical resources a database has is memory. You want to speed up a
SQL Server database; the quickest way to do this based on my experience is to allocate
more memory to it. By allocating more memory, you are minimizing the amount of
physical I/O your database will have to perform. In other words, when the SQL Server
database does not have enough memory, the database will move more of its workload to
the physical I/O. A physical I/O request is still one of the slowest actions a database
can perform.
As mentioned before, a database is just an extension of your disk drives. A slow disk
array typically means a slow database. To speed up a database quickly, you need to
minimize the physical I/O the database has to perform. In a perfect world, you would
read all your data into memory, and the only time the database would have to go out to
the storage array is to record a transaction.
Caution
