Let us start with a personal story.
Personal Story – My Daughter and I
My day has its own part of long meetings and my daughter tries to pull a string here and there to get my attention almost every other day. And recently I was made to watch one of her school assignment on drawing. As a parent, the beauty is in the eye of the beholder – so I can never complain what my daughter drew for me. Since this was some sort of school competition, I wanted to see what the remark of the teacher was – it mentioned “Very Good”. Intrigued by this comment, I started to quiz my daughter to who got the Best painting marks and what did they draw? I am sure the parent inside me was taking a sneak peek into how my daughter performed as compared to others in the class. She was quick to respond with a few names of her friends and said they also drew best. The honesty moved me because as a child we are unbiased and true to our very self. This moved me and I thought it is a great time to take my daughter to the park and spend some quality time as father-daughter.
Getting Back to SQL Server
Returning from park, this incident was on top of my mind and I thought being a class topper or ahead of the crowd is an intrinsic quality we all try to follow as human beings. It is so strange that today’s post is all about the “Top” nature. In life, it is great to be a Top performer but in SQL Server parlance it is not a great thing to be on these Top reports. You are surely going to become a subject for tuning next. This surely is a great boon for Administrators though.
This blog will call out 3 different report from the Server Node -> Reports -> Standard Reports
- Top Transactions by Age
- Top Transactions by Locks Count
- Top Transactions by Blocked Transactions Count
Since all these reports were from the Top category about the transaction based on various factors, I thought to have them all covered in one post.
Top Transactions by Age
This is one of the simplest of reports which shows based on when the query was submitted to the server and how long some of the transactions have been waiting in the instance. This is a good starting point to know which Session ID, Database is holding the # of lock for how long. These for all practical purposes is a very good starting point in my opinion to start looking at long running transactions.
Some of the DMV’s that work behind the scenes to generate the report are:
- sys.dm_tran_active_transactions – Has information of transactions in a given instance.
- sys.dm_tran_session_transactions – Has information of transactions for a given session.
- sys.dm_tran_database_transactions – Gives transactions at a database level.
- sys.dm_exec_sessions – Has information about the active sessions currently on the server.
- sys.dm_exec_requests – Information of each request currently running on the server.
From this report, the DBA can take a decision to what process is causing these locks? And why they are held for such a long time.
Top Transactions by Lock Count
I would say, this is in continuation to the previous report. In the previous report, I was able to find out the number of locks for a given Session ID and database. But the specifics on the type of locks were not clear.
This report is all about expanding that part of the uncertainty. This report shows the type of locks are held by a specific session. In our report below we can see the session ID 52 and 53 are holding Object, Page and Key locks respectively. While 52 has an Exclusive Lock already taken, 53 has an Update fired on the dbo.test table.
I am sure on a highly transactional production server this will surely be a busy report to view. Do let me know how many nodes you see on your servers.
The transaction state can be one of the following:
- Uninitialized
- Initialized
- Active
- Prepared
- Committed
- Rolled Back
- Commiting
These values can be got from sys.dm_tran_database_transactions DMV. While the Active Transaction State can take the below values as defined in the sys.dm_tran_active_transactions DMV:
- Invalid
- Initialized
- Active
- Ended
- Commit Started
- Prepared
- Committed
- Rolling Back
- Rolled Back
Top Transactions by Blocked Transactions Count
The last report under the Top category is the Blocked transaction Count report. This is almost similar to the report we say in our previous post of Blocking Transactions Report. Since we have explained the same out there already, I will refrain from getting into the details here.
These reports can help in finding cause of below few errors:
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. (Msg 1204, Level 19, State 4)
Action: Find the session_id which is taking more locks and tune them.
Lock request time out period exceeded.(Msg 1222, Level 16, State 45)
Action: Find the session_id which is holding locks and see why they have a long running transaction.
I am curious to know how many of you have every used the Top reports mentioned here in your environments to debug something? Do let me know how you used them effectively so that we can learn from each other.
Reference: Pinal Dave (https://blog.sqlauthority.com)
