Add parent_transaction_id to sys.dm_tran_active_transactions - by Adam Machanic

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 753648 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 7/13/2012 11:52:23 AM
Access Restriction Public


When trying to figure out how much work is being done on behalf of a query, we often want to take into account the full transactional impact. 

SQL Server's parent-child model for transactions makes this extremely difficult, since child system transactions (e.g. worktables) are not tied to the parent transactions in any obvious way in the DMVs.

We need an easy way to get all of the information, in order to assist with performance tuning efforts.

(Note: There is a way to get this information, via sys.dm_tran_locks, but that DMV is so slow that the information is useless in any real-world situation.)
Sign in to post a comment.
Posted by Kevin [MSFT] on 2/19/2013 at 3:30 PM
Thanks for your suggestion, Unfortunately this cannot be implemented at this time.
Posted by Michael K Campbell on 7/13/2012 at 12:05 PM
Yup. Without this info or a way to cleanly relate the work being done in sys.dm_tran_active_trans without the actual cause/process associated with the work being done... all this DMV really allows us to do is take a peek at the worktable to user-trans ratio. (And JOINs against this DMV and some of the other options available are either DOG-SLOW or consistently fail to link requests/sessions against the operation in process simply because the parent_tran detail is missing.)