OLTP Blueprint - A Performance Profile of OLTP applications

OLTP Blueprint - A Performance Profile of OLTP applications

Rate This
  • Comments 7

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

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

 

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
  • Tom,

    Are all these values applicable to both 32-bit and 64-bit?  I would assume some counters may have different tresholds.

    Thanks
  • Wesley, yes, these values should be applicable to both 32- and 64-bit.  

    64-bit provides larger space than 32-bit for sorting, plan cache, user connections, locks and so forth.  However the rules for scalability would still apply to both.

    Regards,
    Tom Davidson
  • Tom

    I recently attended a SQL deep dive and one of the documents that they mentined (Kevin Cox) was the SQL Server 2005 Waits and Queues.

    Can't seem to find it on any of the Microsoft sites.  Can you point me to the location.

  • マイクロソフトの植田です。 私は SQL Server 開発部門で SQL Server のテストを担当しております。現在は主に SQL Server ベスト・プラクティスと呼ばれるプロジェクトに参加しています。このプロジェクトで得られた結果は、米国の

  • http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/oltp-performance-issues.mspx

  • Apparently DM function sys.dm_db_operational_index_stats

    has been replaced by   sys.dm_db_index_operational_stats

    Always nice to know.

  • Good catch... SQL Server never had sys.dm_db_operational_index_stats. It is a typo in the blog. Sorry about that!

Page 1 of 1 (7 items)