Partilhar via

OLTP Blueprint - A Performance Profile of OLTP applications

  • Artigo

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

Comments

  • Anonymous
    September 06, 2006
    Tom,

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

    Thanks

  • Anonymous
    September 13, 2006
    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

  • Anonymous
    November 03, 2006
    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.

  • Anonymous
    May 06, 2007
    The comment has been removed

  • Anonymous
    May 14, 2007
    http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/oltp-performance-issues.mspx

  • Anonymous
    January 21, 2009
    Apparently DM function sys.dm_db_operational_index_stats has been replaced by   sys.dm_db_index_operational_stats Always nice to know.

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

  • Anonymous
    August 02, 2015
    Hi Tom Thank you for this nice and brief but deep blueprint.