An OLTP application is characterized by a high volume of small identical transactions, which frequently include SELECT, INSERT, UPDATE, and DELETE operations.
Unlike large data warehouse or reporting transactions where multiple CPUs work in parallel, dividing up the query into smaller pieces, the small transactions of OLTP do not require parallelism. Parallelism is multiple CPUs working in parallel, dividing up the query into smaller pieces.
While a query is divided across multiple CPUs it will run faster, but it does so by sacrificing CPU resources, as it requires merges and sorts of the smaller pieces before presenting the final result set. An OLTP transaction is small to begin with so there is no need for parallel operations that basically sacrifice CPU and memory resources for speed of execution. Plus, with the high transaction volumes of OLTP, it is important not to waste CPU resources. Parallelism is most appropriate for the big, low volume transactions of data warehouse or reporting applications.
The implications are significant for database design, resource usage and system performance.
OLTP Performance blue print objectives: There are likely to be performance and scalability problems if any of resource issues the following tables are true.
Note The values in Value column are good starting point. The actual values will vary.
Common scenarios to avoid in OLTP
Database Design
Rule
|
Description
|
Value
|
Source
|
Problem Description
|
1
|
High Frequency queries having a high number of table joins.
|
>4
|
Sys.dm_exec_sql_text
Sys.dm_exec_cached_plans
|
High frequency queries with lots of joins can be too normalized for high OLTP scalability.
|
2
|
Frequently updated tables having # indexes.
|
>3
|
Sys.indexes
sys.dm_db_operational_index_stats
|
Excessive index maintenance for OLTP.
|
3
|
Big IOs
Table Scans
Range Scans
|
>1
|
Perfmon object
SQL Server Access Methods
Sys.dm_exec_query_stats
|
A missing index flushes the cache.
|
4
|
Unused Indexes.
|
Index not in Sys.dm_db_index_usage_stats. If an index is NEVER used, it will not appear in the DMV sys.dm_db_index_usage_stats
|
Avoid Index maintenance for unused indexes.
|
CPU
Rule
|
Description
|
Value
|
Source
|
Problem Description
|
1
|
Signal Waits
|
>25%
|
Sys.dm_os_wait_stats
|
Time in runnable queue is pure CPU wait.
|
2
|
Plan reuse
|
<90%
|
Perfmon object
SQL Server Statistics
|
OLTP identical transactions should ideally have >95% plan reuse.
|
3
|
Parallelism: Cxpacket waits
|
>5%
|
Sys.dm_os_wait_stats
|
Parallelism reduces OLTP throughput. CXPACKET indicates that multiple CPUs are working in parallel, dividing up the query in smaller pieces. Ordinarily a well tuned OLTP application would not parallelize unless an index is missing, there is an incomplete WHERE clause, or the query is not a true OLTP transaction.
|
Memory
Rule
|
Description
|
Value
|
Source
|
Problem Description
|
1
|
Page life expectancy
|
<300 sec
|
Perfmon object
SQL Server Buffer Manager
SQL Server Buffer Nodes
|
Page life expectancy is the average number of seconds a data page stays in cache. Low values could indicate a cache flush that is caused by a big read. Pure OLTP workloads do NOT issue big reads, thus possible missing index.
|
2
|
Page life expectancy
|
Drops by 50%
|
Perfmon object
SQL Server Buffer Manager
|
Page life expectancy is the average number of seconds a data page stays in cache. Low values could indicate a cache flush that is caused by a big read. Pure OLTP workloads do NOT issue big reads, thus possible missing index.
|
3
|
Memory Grants Pending
|
>1
|
Perfmon object
SQL Server Memory Manager
|
Current number of processes waiting for a workspace memory grant.
|
4
|
SQL cache hit ratio
|
<90%
|
SQL cache hit ratio falls under 90% for sustained periods of time greater than 60 sec.
|
It is likely that large scans have to be performed, which in turn flushes out the buffer cache.
|
IO
Rule
|
Description
|
Value
|
Source
|
Problem Description
|
1
|
Average Disk sec/read
|
>20 ms
|
Perfmon object
Physical Disk
|
Reads should take 4-8 ms without any IO pressure.
|
2
|
Average Disk sec/write
|
>20 ms
|
Perfmon object
Physical Disk
|
Writes (sequential) can be as fast as 1 ms for transaction log.
|
3
|
Big IOs
Table Scans
Range Scans
|
>1
|
Perfmon object
SQL Server Access Methods
|
A missing index flushes the cache.
|
4
|
If Top 2 values for wait stats are any of the following:
ASYNCH_IO_COMPLETION
IO_COMPLETION
LOGMGR
WRITELOG
PAGEIOLATCH_x
|
Top 2
|
Sys.dm_os_wait_stats
|
If top 2 wait_stats values include IO, there is an IO bottleneck.
|
5
|
Low bytes per sec.
|
|
Perfmon object
Physical Disk
|
|
Blocking
Rule
|
Description
|
Value
|
Source
|
Problem Description
|
1
|
Block percentage
|
>2%
|
Sys.dm_db_index_operational_stats
|
Frequency of blocks.
|
2
|
Block process report
|
30 sec
|
Sp_configure profiler
|
Report of statements.
|
3
|
Average Row Lock Waits
|
>100ms
|
Sys.dm_db_index_operational_stats
|
Duration of blocks.
|
4
|
If Top 2 values for wait stats are any of the following:
LCK_M_BU
LCK_M_IS
LCK_M_IU
LCK_M_IX
LCK_M_RIn_NL
LCK_M_RIn_S
LCK_M_RIn_U
LCK_M_RIn_X
LCK_M_RS_S
LCK_M_RS_U
LCK_M_RX_S
LCK_M_RX_U
LCK_M_RX_X
LCK_M_S
LCK_M_SCH_M
LCK_M_SCH_S
LCK_M_SIU
LCK_M_SIX
LCK_M_U
LCK_M_UIX
LCK_M_X
|
Top 2
|
Sys.dm_os_wait_stats
|
If top 2 wait_stats values include locking, there is a blocking bottleneck.
|
5
|
High number of deadlocks
|
>5 per hour
|
Trace flag 1204 to display in the errorlog and or the profiler deadlock graph.
|
If the deadlock occurs with the same participant SQL commands or operations multiple times, it is likely that there is a locking problem.
|
Network
Rule
|
Description
|
Value
|
Source
|
Problem Description
|
1
|
High network latency coupled with an application that has many round trips to the database.
|
Output queue length >2
|
Perfmon object: Network Interface
|
Indicates that the latency between the application server and the database is high.
Could be caused by significant network infrastructure between the application and the instance of SQL Server.
|
2
|
Network bandwidth is used up.
|
Packets Outbound Discarded
Packets Outbound Errors
Packets Received Discarded
Packets Received Errors
|
Perfmon object: Network Interface
|
Dropped packets are detected.
|
In summary, given the high volume of identical small transactions that characterize OLTP, transactions per second and resource usage can be improved as follows:
-
Database designs usually keep the number of indexes to a functional minimum as every insert, update, and delete incurs index maintenance.
-
CPU can be reduced with plan reuse and join reduction.
-
IO performance can be reduced with good indexing, join reduction, and high page life expectancy.
-
Memory is optimal when there are no sudden drops in Page Life Expectancy.
-
Sorts can be limited with index usage. That is, a certain sort order is supported by an index that is sorted the same way, either ascending or descending.
-
Blocking can be reduced with index design and short transactions.
|
Yüklə 0.86 Mb.