Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite
1

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?

share|improve this question
What indication do you have that "performance is far below expectations"? Please be more specific. – Thomas Stringer Jun 21 '12 at 2:29
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 '12 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 '12 at 10:25
I wonder if you're still having this problem, and if by chance you've read any of the VLF (virtual log file) articles on sqlfool.com or other sites. – Jeff Aug 1 '12 at 17:43
Yes, I've read these articles. 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 '12 at 22:35

migrated from stackoverflow.com Jun 21 '12 at 8:19

1 Answer

up vote 1 down vote

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

share|improve this answer
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 '12 at 18:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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