OLTP Blueprint - A Performance Profile of OLTP applications
We will look at different types of applications, how they use resources, and how one would approach performance tuning each. The performance profile of OLTP differs significantly from a Relational Data Warehouse or Reporting application. It is helpful to understand these differences and the objectives for high performance.
For example, OLTP applications are characterized by high volumes of small identical transactions. These can include SELECT, INSERT, UPDATE and DELETE operations. The implications are significant in terms of database design, resource utilization and system performance.
OLTP Performance blue print objectives. There are performance problems if any of the following are true. Note: Actual value used in Value column can be debated.
|
Resource issue |
Rule |
Description |
Value |
Source |
Problem Description |
|
Database Design |
Rule 1 |
High Frequency queries having # table joins |
>4 |
Sys.dm_exec_sql_text,
Sys.dm_exec_cached_plans |
High Frequency queries with lots of joins may be too normalized for high OLTP scalability |
|
Rule 2 |
Frequently updated tables having # indexes |
>3 |
Sys.indexes, sys.dm_db_operational_index_stats |
Excessive index maintenance for OLTP |
|
Rule 3 |
Big IOs
Table Scans
Range Scans |
>1 |
Perfmon object
SQL Server Access Methods
Sys.dm_exec_query_stats |
Missing index, flushes cache |
|
Rule 4 |
Unused Indexes |
index not in* |
* Sys.dm_db_index_usage_stats |
Index maintenance for unused indexes |
|
CPU |
Rule 1 |
Signal Waits |
> 25% |
Sys.dm_os_wait_stats |
Time in runnable queue is
pure CPU wait. |
|
Rule 2 |
Plan re-use |
< 90% |
Perfmon object
SQL Server Statistics |
OLTP identical transactions should ideally have >95% plan re-use |
|
Rule 3 |
Parallelism: Cxpacket waits |
>5% |
Sys.dm_os_wait_stats |
Parallelism reduces OLTP throughput |
|
Memory |
Rule 1 |
Avg page life expectancy |
< 300 (seconds) |
Perfmon object
SQL Server Buffer Manager
SQL Server Buffer Nodes |
Cache flush, due to big read
Possible missing index |
|
Rule 2 |
Avg page life expectancy |
Drops by 50% |
Perfmon object
SQL Server Buffer Manager |
Cache flush, due to big read
Possible missing index |
|
Rule 3 |
Memory Grants Pending |
>1 |
Perfmon object
SQL Server Memory Manager |
Current number of processes waiting for a workspace memory grant |
|
IO |
Rule 1 |
Avg Disk seconds / read |
> 20 ms |
Perfmon object
Physical Disk |
Reads should take 4-8ms with NO IO pressure |
|
Rule 2 |
Avg Disk seconds / write |
> 20 ms |
Perfmon object
Physical Disk |
Writes (sequential) can be as fast as 1ms for transaction log. |
|
Rule 3 |
Big IOs
Table Scans
Range Scans |
>1 |
Perfmon object
SQL Server Access Methods |
Missing index, flushes cache |
|
Rule 4 |
If Top 2 values for wait stats are any of the following:
1. ASYNCH_IO_COMPLETION
2. IO_COMPLETION
3. LOGMGR
4. WRITELOG
5. PAGEIOLATCH_x |
Top 2 |
Sys.dm_os_wait_stats |
If top 2 wait_stats values include IO, there is an IO bottleneck |
|
Blocking |
Rule 1 |
Block percentage |
> 2% |
Sys.dm_db_index_operational_stats |
Frequency of blocks |
|
Rule 2 |
Block process report |
30 sec |
Sp_configure, profiler |
Report of statements |
|
Rule 3 |
Avg Row Lock Waits |
> 100ms |
Sys.dm_db_index_operational_stats |
Duration of blocks |
|
Rule 4 |
If Top 2 values for wait stats are any of the following:
1. LCK_x |
Top 2 |
Sys.dm_os_wait_stats |
If top 2 wait_stats values include locking, there is a blocking bottleneck |
In summary, OLTP applications are comprised of high volumes of identical small transactions. They differ significantly in terms of resource usage as compared with other types of applications such as reporting or data warehousing. The blueprint above considers resources usage as a means of formulating a “performance profile” of a typical OLTP application.
For example, the high volumes of OLTP identical transactions mean plan re-use is desirable. CPU utilization can thusly be reduced with plan re-use and join reduction. IO performance can be improved with good indexing, join reduction, and high page life expectancy. Sorts can be limited with index usage. Blocking can be reduced with index design and short transactions.
The unique performance profile of OLTP provides guidance in terms of resource utilization, expressed in the blueprint above. While the specific values can be debated, the general concepts applicable to OLTP applications in terms of resource utilization and performance characteristics are valid.
enjoy,
Tom Davidson
