OLTP Blueprint - A Performance Profile of OLTP applications
Published Dec 20 2018 11:09 AM 452 Views
Microsoft
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


Version history
Last update:
‎Oct 28 2022 12:47 PM
Updated by: