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

OLTP Workloads
One extreme is a pure OLTP workload, which is typically characterized by numerous short-duration
queries and transactions with a relatively high percentage of write activity. Processors with higher
base clock speeds and higher turbo speeds (within the same processor family) tend to perform
better on most OLTP queries. A pure OLTP workload usually has a high degree of data volatility,
especially in some of the database's key tables. Having a pure OLTP workload will inl uence your
hardware options and how you coni gure your hardware and storage subsystem. These workloads
generate more input/output (I/O) operations per second (IOPS) than an equivalent data warehouse
(DW) system.
With a single OLTP database, you will see mostly sequential write activity to your transaction log
i le, and more random write activity to your data i le(s). If you have more than one OLTP database
on your instance of SQL Server, and the transaction log i les for these databases are located on the
same drive array, you will see more random write activity because the drive array is forced to service
all the transaction log i les for multiple OLTP databases. If you are using technologies such as SQL
Server transactional replication, database mirroring, or AlwaysOn availability groups, you will also
see sequential read activity against your transaction log i le(s).
Data Warehousing Workloads
Another completely different type of workload is a pure DW workload, which has long-running,
complex queries that are often parallelized by the Query Optimizer; this places a premium on
having processors with higher physical core counts and better memory controllers in order to
execute these types of queries as quickly as possible. Also very important for DW workloads is
having a large amount of memory to ensure you have adequate room for the buffer pool.
A DW workload has more sequential reads from your data i les and very little write activity to your
data i les and log i le during normal operations. During data loads, you will see predominantly
sequential write activity to your transaction log i le and a combination of sequential and random
write activity to your data i les. You want to consider sequential read and write performance as you
select and coni gure your I/O subsystem for a DW workload.
Relational Reporting Workloads
Many organizations maintain a second copy of an OLTP database for reporting usage. This is
ideally located on a dedicated server that is separate from the primary OLTP database server. This
“reporting” database will have many additional nonclustered indexes added to the existing OLTP
tables and it may also have additional reporting tables containing calculated summary data for
reporting purposes.
In some cases, this reporting database is restored from a backup of the production OLTP database,
perhaps once a day. After the restore is i nished, all the additional nonclustered indexes are created
and the reporting tables are loaded and indexed. In terms of sequential read and write performance,
this type of pattern places a lot of stress on the I/O subsystem. Restoring a database from a
backup and creating many new indexes is a sequential operation, so having a lot of sequential I/O
performance is very important. After the reporting database is ready for use, the overall workload
becomes very similar to a DW workload. If you have this type of pattern, you should consider using
the new columnstore index feature in SQL Server 2012.