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.
High Frequency queries having # table joins
High Frequency queries with lots of joins may be too normalized for high OLTP scalability
Frequently updated tables having # indexes
Excessive index maintenance for OLTP
SQL Server Access Methods
Missing index, flushes cache
index not in*
Index maintenance for unused indexes
Time in runnable queue is
pure CPU wait.
SQL Server Statistics
OLTP identical transactions should ideally have >95% plan re-use
Parallelism: Cxpacket waits
Parallelism reduces OLTP throughput
Avg page life expectancy
< 300 (seconds)
SQL Server Buffer Manager
SQL Server Buffer Nodes
Cache flush, due to big read
Possible missing index
Drops by 50%
Memory Grants Pending
SQL Server Memory Manager
Current number of processes waiting for a workspace memory grant
Avg Disk seconds / read
> 20 ms
Reads should take 4-8ms with NO IO pressure
Avg Disk seconds / write
Writes (sequential) can be as fast as 1ms for transaction log.
If Top 2 values for wait stats are any of the following:
If top 2 wait_stats values include IO, there is an IO bottleneck
Frequency of blocks
Block process report
Report of statements
Avg Row Lock Waits
Duration of blocks
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.
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 ベスト・プラクティスと呼ばれるプロジェクトに参加しています。このプロジェクトで得られた結果は、米国の
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!
Thank you for this nice and brief but deep blueprint.