Demystifying Hardware - SQL Server 2012 Internals and Troubleshooting - page 32

Another scenario for a relational reporting database is to use transactional replication between the
production OLTP database, which acts as a publisher, to the “reporting” database, which acts as a
subscriber. Usually, many additional nonclustered indexes are added to the subscriber to improve
query performance for reporting queries. Maintaining acceptable INSERT , UPDATE , and DELETE
performance in this database is more difi cult because of these additional indexes. This places more
stress on your I/O subsystem, so you will see sequential writes to the log i le and random writes
to the data i les. The reporting queries cause sequential reads from the data i les. Overall, this is a
relatively challenging mixed workload type.
OLAP Workloads
OLAP workloads have several different components, including reading data from the source(s) to
initially build or update the cube, processing the cube when changes are made, and then actually
running various types of OLAP queries to retrieve the data for users. Having processors with higher
physical core counts, with better memory controllers in order to execute these types of queries as
quickly as possible, is very valuable. Also very important for OLAP workloads is having a large
amount of memory so that you can process large cubes quickly. OLAP workloads tend to have a
lot of random I/O, so l ash-based storage (see Chapter 4) for the cube i les can be very benei cial.
Flash-based storage includes solid-state drives (SSDs) and other devices such as Fusion-io cards that
use solid-state l ash memory for permanent storage. These types of devices offer extremely high
random I/O performance, which is very useful for OLAP workloads.
Server Model Selection
In order to choose an appropriate server model for your database server, you must i rst decide
whether you want to use an Intel processor or an AMD processor, as this absolutely dictates which
server models you can consider from your system vendor. Next, you need to decide whether you
will be using a one-socket, two-socket, or four-socket database server, or something even larger,
as that constrains your available processor options. You also have to decide what vertical form
factor you want for the server — that is, whether it will be a 1U, 2U, 4U, or even larger server.
These designations, (1U, 2U, etc.) refer to how tall the server is in rack units, with a rack unit being
roughly 1.75 inches tall. This affects how many servers will i t in a rack, and how many internal
drive bays will i t inside a rack-mounted server.
These choices also affect the maximum amount of physical memory (RAM) that you can have, the
number of Peripheral Component Interconnect Express (PCIe) expansion slots that are available,
and the number of internal drive bays that are available in the server.
Here are some things to consider as you decide whether to purchase a two-socket database server or
a four-socket database server. Traditionally, it was very common to use a four-socket machine for
most database server scenarios, while two-socket servers were most often used for web servers or
application servers. However, given recent advances in processors, improvements in memory density,
and the increase in the number and bandwidth of PCIe expansion slots over the past several years,
you might want to seriously reconsider that conventional wisdom.