Hello Geeks and welcome to the Day 51 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
After the significant 50 blogs in the DMV series and a week of PASS Summit in my head, I am in cloud 9. I still can’t believe that I have completed 50 blogs in the series. The day I started I was not sure if I could reach the first milestone of 31 blogs of any one day series on SQL Server. But look at this post today, 51 days. You just need the commitment to do what you are doing consistently.
Having said that let me jump to today’s dose of DMV. To keep it light after the ultramarathon, I will be covering sys.dm_tran_current_transaction. This DMV is a limited result set very specific to the current session. This helps in analyzing the state of few parameters when you are inside a transaction.
Let us learn the output of sys.dm_tran_current_transaction with the below example. I will open a transaction which will run an update and create few worktables and a temp table.
BEGIN TRANSACTION
UPDATE Person.Person
SET Title = Title
WHERE BusinessEntityID < 10000
SELECT transaction_id AS [tranId],
transaction_sequence_num AS [seqNo],
transaction_is_snapshot AS [isSnap],
first_snapshot_sequence_num AS [1stSnapSeqNo],
last_transaction_sequence_num AS [lastSeqNo],
first_useful_sequence_num AS [1stUsefulSeqNo]
FROM sys.dm_tran_current_transaction
IF (OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL)
DROP TABLE #temp
SELECT *
INTO #temp
FROM bigTransactionHistory
ORDER BY ActualCost
--COMMIT
Now in a new session run the select on sys.dm_tran_current_transaction.
SELECT transaction_id AS [tranId], transaction_sequence_num AS [seqNo], transaction_is_snapshot AS [isSnap], first_snapshot_sequence_num AS [1stSnapSeqNo], last_transaction_sequence_num AS [lastSeqNo], first_useful_sequence_num AS [1stUsefulSeqNo] FROM sys.dm_tran_current_transaction
The output displays the transaction_id which is unique for the lifetime of a running instance. It gets reset after a restart. Now the interesting columns are the sequence numbers. The transaction_sequence_number is the sequence number of current transaction. The next column, transaction_is_snapshot is to tell if this is a snapshot transaction.
First_snapshot_sequence_num is the lowest sequence number of any transaction which is active when first snapshot is generated. Last_transaction_sequence_num is the last sequence number generated. First_useful_sequence_num is the oldest sequence number that should be retained in version store. I will cover more about version store in the next two DMVs. For now we can conclude from this column that sequence number lower than this can be removed.
In the second output you will observe the first_useful_sequence_num is 1358 which is the sequence number for the first session. Now commit the first session and run the second command again.
You will see the value change to 1362. It will be any number greater than 1358. In this case it is 1362. This means any sequence number before 1362 can be removed.
Now you know the significance of sys.dm_tran_current_transaction. Keep guessing what I will be covering tomorrow. So stay tuned. Till then.
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
