Database Reference
In-Depth Information
associated with them. There are four types of SQL workloads:
OLTP (Online Transaction Processing)
OLAP (Online Analytical Processing)
Batch/ETL (Extract Transform Load)
DSS (Decision Support System)
OLTP workloads tend to have large amounts to small queries, sustained CPU utilization
during working hours (8 a.m. to 5 p.m., for example), and are sensitive to the contention
that occurs during peak resource utilization. Common examples of an OLTP database
include the backend database for an online retailer as well as ERP systems such as SAP
or CRM (Customer Relationship Management) systems. In addition, these workloads
often experience very large amounts of very small network packets and queries and can
therefore be sensitive to network latency. These systems must perform because any
performance impact to these systems can directly affect a company's bottom line.
OLAP workloads are read heavy and typically contain complex queries that span a
limited number of columns but a larger number of rows. These queries tend to touch a
large data set.
Batch/ETL workload types tend to be write intensive, run during off-peak hours,
tolerate contention better than OLTP workloads, and sometimes are network intensive.
Batch workloads are often run at the end of the business day to generate reports about
transactions that occurred throughout the business day. Batch/ETL workloads are broken
down into three distinct phases:
Extract
—When multiple sources are contacted and data is obtained from these
sources. This is the first phase.
Transform
—When an action (or actions) is taken upon the obtained data to
prepare it for loading into a target system. This is the second phase.
Load
—When the data is loaded into the target system (which is often a data
warehouse). This is the third phase.
DSS workloads are characterized by a few queries that are longer running, resource
intensive (CPU, memory, I/O), and often exhibit these characteristics during month,
quarter, or year-end. DSS queries favor read over write, so it is important for the
system to be able to provide that data in the quickest manner possible. An example of a
query run in a DSS system is “Show me all the customers over the past ten years who
ever bought our 'Baseball Package' but did not buy it this year.”
Putting It Together (or Not)
It is imperative architects understand the workload types they are going to virtualize
before the virtualization project begins, which is why we are talking about this in the
