5

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?

Improve this question
3
  • For these queries that take 5 seconds can you poll sys.dm_os_waiting_tasks whilst they are running to see what the wait types and wait resources are that they are getting? i.e. Are they waiting on NESTING_TRANSACTION_FULL or maybe in a blocking chain blocked by something waiting on that?
    – Martin Smith
    Jun 21, 2012 at 9:42
  • Your best bet is to contact product support. they have the means to analyze the issue and recommend solutions.
    – Remus Rusanu
    Jun 21, 2012 at 10:25
  • Average VLF count per database is about 150, maximum is near 500, sum of VLF per server is about 15,000 (I didn't understand from articles if sum per server is important).
    – Abelevich
    Sep 11, 2012 at 22:35

1 Answer 1

Reset to default
-1

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

Improve this answer
1
  • Thank you, I've read this article before asking. However, I still don't understand what to do with this particular latch, and where is the problem.
    – Abelevich
    Jun 25, 2012 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.