OLTP Blueprint

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.

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