OLTP Blueprint - A Performance Profile of OLTP applications

DataCAT team

Microsoft

Dec 20, 2018

First published on MSDN on Jun 23, 2006

Authored by Thomas Davidson

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