OLTP Blueprint - A Performance Profile of OLTP applications
Performance and Tuning Blue Prints
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.
OLTP blueprint
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 |