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