February 15, 2016 at 11:59 am
Hi all
I've been trying to resolve some highly problematic blocking in one of our SQL Servers. The problem is, I don't quite understand what the sys.dm_exec_requests is telling me.
When I run a query to find all requests that are currently blocked:
SELECT *
FROM sys.dm_exec_requests
I look up the queries with a non-null blocking_session_id value and can see queries that have been blocked for say, 2 minutes. The problem I have is that the blocked_session_id that is causing the blocking, simply doesn't exist in either the sys.dm_exec_requests view or sys.dm_exec_sessions.
Unless I'm imaging it, this situation can go on for a few minutes, where queries are "blocked" by something that doesn't exist.
Can anyone advise on what could cause that?
I'm wondering if its possible the blocking query is being killed by the client application being forcibly closed but SQL Server hasn't detected it?
Thanks
S
February 15, 2016 at 12:04 pm
Not existing in exec_requests is completely understandable. That DMV only shows executing requests. If a session has opened a transaction, run a query and is now in the sleeping state waiting for more work, it could still be blocking but won't be in exec_requests.
Not showing up in exec_sessions is harder to explain. That DMV is all established sessions.
Can you post the exact queries you're using?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2016 at 1:34 am
Hi Gail
Thanks for the reply.
> If a session has opened a transaction, run a query and is now in the sleeping state waiting for more work, it could still be blocking but won't be in exec_requests.
Apologies - my terminology was wrong before. What you've said above is exactly what is happening.
I have 2 or 3 blocked requests blocked by session 127 (for example). However, when I look at the sessions DMV it says that 127 is sleeping.
Can you advise on how I can tell what the session is actually doing? I didn't realise that a session could be blocking without actually having an outstanding request of its own.
Any help gratefully received!
S
February 16, 2016 at 2:08 am
If you ran this kind of setup, the query in the second window would be blocked forever (or until someone closed the connection for window 1), even though there's nothing running in Window 1. It started a transaction, took locks and has not committed or rolled back. Locks taken by data modifications are held until the end of the transaction.
Window 1:
BEGIN TRANSACTION
UPDATE SomeTable SET SomeColumn = 42;
Window 2:
SELECT SomeColumn FROM SomeTable -- will be blocked 'forever'
As for what the session in window 1 is doing, the answer is nothing. It's sleeping. But it's holding locks due to the open transaction. What you'll need to do is trace it to a piece of code (the most_recent_sql_handle in sys.dm_exec_connections may help there) and figure out why transactions are not being committed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2016 at 2:18 am
Hi Gail,
Thanks for this. I think you've definitely helped me get into the right ballpark.
The application in question is quite an old and buggy CRM system built using classic ASP and likely has some quite dubious coding practices from what I've seen.
Do you happen to know what would happen in the following circumstances:
A user opens a web page that performs a very large table scan. They get bored after a couple of minutes and close the page down...
Would SQL Server detect that the user aborted? My current theory is that this is what is happening, and SQL Server is retaining the locks until some timeout threshold is reached. Then it releases the locks and all is well again.
Thanks again
Simon
February 16, 2016 at 2:25 am
Do you have sp_whoisactive on your server? If not, look it up, install it on a test server, and when you're happy with it, put it on your live server. I seem to remember that it has a parameter that sorts the output according to position in the blocking chain.
John
February 16, 2016 at 2:31 am
sh856531 (2/16/2016)
Would SQL Server detect that the user aborted?
No, because it's not the user that's requesting the data, it's the web server. SQL will finish the web server's request. If there's no transaction, any locks get released at that point. If there are, then locks are held until the transaction ends.
If the web server requests an abort, SQL will honour it, just as if you pressed stop in SSMS.
Now, if the asp pages are written so that a transaction stays open, then a transaction stays open, but that's back to bad code.
My current theory is that this is what is happening, and SQL Server is retaining the locks until some timeout threshold is reached.
No.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2016 at 5:56 am
Hi Gail
> No, because it's not the user that's requesting the data, it's the web server. SQL will finish the web server's request. If there's no transaction, any locks get released at that point. If there are, then locks are held until the transaction ends.
Just for my own understanding, what happens if the client, in our example the web server was disconnect in an ungraceful manner? For example, we are requesting the results of a very large SELECT statement to the web server, and the power went out on the web server?
Does SQL Server detect the connection loss immediately and release all locks?
Many thanks
February 16, 2016 at 6:19 am
Any uncommitted modifications roll back and the connection is terminated. While rolling back, it shows as an active session in ROLLBACK. Whether the SELECT aborts immediately or not I'm not sure. It'll probably be treated much like a stop request from SSMS with the connection terminated as soon as the abort completes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply