Production SQL Server 2008 performance is far below expectations. Simple single-table queries sometimes are working for 5 seconds in the mid-day compared to 250ms after hours. We cannot find which resource is the bottleneck when load increases.
Usual advices about performance tuning don't help. CPU is not overloaded (about 30%), page life expectancy is about 1.5 hours; IO queues are almost always empty. There is a suspicious record leading sys.dm_os_wait_stats view by wait_time_ms: LATCH_EX. And the largest latch (4 times larger than second largest) is NESTING_TRANSACTION_FULL. Which is described as "internal use only".
Does NESTING_TRANSACTION_FULL statistic mean anything? And could it give us a lead to diagnose problems with its configuration, hardware, application etc?
Best Answer
TRANSACTION_*, MSQL_TRANSACTION_MANAGER, NESTING_TRANSACTION_FULL, NESTING_TRANSACTION_READONLY
Within this group of latch classes that are used during various transaction-related operations, the TRANSACTION_DISTRIBUTED_MARK latch is unique. It is used when placing markers in the transaction logs to allow for recovery to a named point. There is only one transaction mark latch in any instance of SQL Server 2005. This latch rarely, if ever, encounters contention, and thus there is no need for an extensive description. The source of any contention is also clear, because this latch is used by only a single operation. The other latch classes in this group are used in various transaction contents.
this post is original from:
http://www.informit.com/articles/article.aspx?p=686168&seqNum=5