February 1, 2019 at 8:33 am
I have encountered a situation when I cannot find out what stored procedure or query is blocking others. Here I put a simplified example:
Connection 1:
create table t1 (c1 int)
go
begin tran
insert into t1
values(1)
Connection 2:
select * from t1
Connection 1 - I wipe out all my code from here and run this:
getdate()
Connection 3 - I need to find all the information about blocking. I run this:
select * from sys.sysprocesses
where blocked>0
It happen to be SPID # 73. So I want to find what my blocker is running:
dbcc inputbuffer(73)
It shows that the blocker is running getdate and it is blocking my t1 table somehow.
In our production we use connection pool from the application side. That means that once connection to SQL Server is set it will be reused for all consecutive calls. And if SPID number from SQL Server side is 73, it will stay as 73 for all the rest calls.
When production DBA's report to us that SPID 73 is blocking other processes, and they report that (in this example) getdate is blocking, it completely does not make a sense.
I thought about some reliable methods to find genuine blocker. One of them is to trace all SQL Server calls, but prod DBAs are against it. Another one is extended events, but it is practically the same is tracing.
So my question to forum is to how reliably check what command actually started the blocking?
Thanks
February 1, 2019 at 8:48 am
I used to have Erland Sommarskog's beta_lockinfo utility. You could give it a try.
http://www.sommarskog.se/sqlutil/beta_lockinfo.html
February 1, 2019 at 9:16 am
Thank you Luis for this great stored procedure!
I downloaded, ran it and applied to my situation. But unfortunately, in its result-set, in inputbuffer column it still shows select getdate().
February 1, 2019 at 11:06 am
SQL Guy 1 - Friday, February 1, 2019 9:16 AMThank you Luis for this great stored procedure!I downloaded, ran it and applied to my situation. But unfortunately, in its result-set, in inputbuffer column it still shows select getdate().
Another one to try is sp_WhoIsActive. You can download it from here: sp_whoisactive
You can execute the follow to find the lead blocker:sp_WhoIsActive @find_block_leaders = 1
Sue
February 1, 2019 at 11:33 am
Thank you Sue, tried it too. It still shows getdate().
In my situation, I don't have a chain. I simply replaced one command (that holds open transaction) with another one (which is getdate).
February 1, 2019 at 11:55 am
SQL Guy 1 - Friday, February 1, 2019 11:33 AMThank you Sue, tried it too. It still shows getdate().In my situation, I don't have a chain. I simply replaced one command (that holds open transaction) with another one (which is getdate).
Now I see what you did in the script. You still have an uncommitted transaction. GetDate() is just the last thing that was run by the spid that is the lead blocker.
That in particular is an issue with uncommitted transaction. With connection pooling, it can stay in the same state as it will close the connection without a rollback. Query timeouts can have the same effect if not doing something like using SET XACT_ABORT ON.
Checking @@trancount, error handling for the connections and such would need to be checked from the application. A connection that continues to run from an application with existing open transactions are better handled from the application.
Sue
February 1, 2019 at 1:35 pm
Exactly, Sue. We have an uncommitted transaction that holds this block. And the problem is that our blocking monitoring job (it is running every 30 sec) wrongfully points to another procedure.
We are not using SET XACT_ABORT ON, but in most of the places we check with @@trancount, but apparently not in all of them. And another problem is that none of transactions are named. What I am thinking is that if we name all transactions uniquely throughout entire database, it would be easy to catch this kind of blocker with DBCC opentran.
However, any of these changes will take time, but I need to find the blocker now.
February 1, 2019 at 2:01 pm
SQL Guy 1 - Friday, February 1, 2019 1:35 PMExactly, Sue. We have an uncommitted transaction that holds this block. And the problem is that our blocking monitoring job (it is running every 30 sec) wrongfully points to another procedure.We are not using SET XACT_ABORT ON, but in most of the places we check with @@trancount, but apparently not in all of them. And another problem is that none of transactions are named. What I am thinking is that if we name all transactions uniquely throughout entire database, it would be easy to catch this kind of blocker with DBCC opentran.
However, any of these changes will take time, but I need to find the blocker now.
Yup. But for the DBA to try to trace every one of the statements against the database to find which one is causing the problems is likely not realistic. You'd basically be capturing all of the statements to try to find which one led to the uncommitted transaction. A connection could go through executing a lot after that open transaction. And you could have to do it over and over and over. I think you probably need to move your efforts to a non-production environment and start walking through every thing to find the places where it should be handled in the application.
Unfortunately connection pooling isn't something where you set it and forget it and too often things like what your seeing can happen. I think it takes more planning and discipline than most realize.
Sue
February 1, 2019 at 2:36 pm
Sue_H - Friday, February 1, 2019 2:01 PMSQL Guy 1 - Friday, February 1, 2019 1:35 PMExactly, Sue. We have an uncommitted transaction that holds this block. And the problem is that our blocking monitoring job (it is running every 30 sec) wrongfully points to another procedure.We are not using SET XACT_ABORT ON, but in most of the places we check with @@trancount, but apparently not in all of them. And another problem is that none of transactions are named. What I am thinking is that if we name all transactions uniquely throughout entire database, it would be easy to catch this kind of blocker with DBCC opentran.
However, any of these changes will take time, but I need to find the blocker now.
Yup. But for the DBA to try to trace every one of the statements against the database to find which one is causing the problems is likely not realistic. You'd basically be capturing all of the statements to try to find which one led to the uncommitted transaction. A connection could go through executing a lot after that open transaction. And you could have to do it over and over and over. I think you probably need to move your efforts to a non-production environment and start walking through every thing to find the places where it should be handled in the application.
Unfortunately connection pooling isn't something where you set it and forget it and too often things like what your seeing can happen. I think it takes more planning and discipline than most realize.Sue
Actually, by setting an Extended Events session that captures all sql_batch_starting events with the session_id, you could trace the blocker calls. I'm not sure if it's worth the trouble, though.
February 1, 2019 at 2:47 pm
It is a known limitation of the DMVs that they only remember the most recent command executed by a session. If you really need to know what command is blocking the other session and not just which session is causing blocking, probably the best you could do is to see what locks the blocker sill has open:SELECT s.session_id, s.host_name, s.program_name, s.login_name, s.status AS session_status,
DB_NAME(er.database_id) AS database_name,
er.status AS request_status, er.command, --er.percent_complete,
er.blocking_session_id, bs.host_name AS blocking_host, bs.program_name AS blocking_program, bs.login_name AS blocking_login,
er.wait_type, er.wait_time / 1000.0 AS wait_sec, er.wait_resource, bs.open_transaction_count,
DB_NAME(l.resource_database_id) AS database_name,
OBJECT_SCHEMA_NAME(l.resource_associated_entity_id, l.resource_database_id) AS schema_name,
OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) AS object_name,
l.request_mode, l.request_type, l.request_status
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_sessions bs ON er.blocking_session_id = bs.session_id
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp
LEFT OUTER JOIN sys.dm_tran_locks l ON bs.session_id = l.request_session_id AND l.resource_type = 'OBJECT'
LEFT OUTER JOIN sys.objects o ON l.resource_associated_entity_id = o.object_id
WHERE s.is_user_process = 1
AND er.blocking_session_id IS NOT NULL
AND s.session_id <> @@SPID
February 1, 2019 at 4:07 pm
Luis Cazares - Friday, February 1, 2019 2:36 PMSue_H - Friday, February 1, 2019 2:01 PMSQL Guy 1 - Friday, February 1, 2019 1:35 PMExactly, Sue. We have an uncommitted transaction that holds this block. And the problem is that our blocking monitoring job (it is running every 30 sec) wrongfully points to another procedure.We are not using SET XACT_ABORT ON, but in most of the places we check with @@trancount, but apparently not in all of them. And another problem is that none of transactions are named. What I am thinking is that if we name all transactions uniquely throughout entire database, it would be easy to catch this kind of blocker with DBCC opentran.
However, any of these changes will take time, but I need to find the blocker now.
Yup. But for the DBA to try to trace every one of the statements against the database to find which one is causing the problems is likely not realistic. You'd basically be capturing all of the statements to try to find which one led to the uncommitted transaction. A connection could go through executing a lot after that open transaction. And you could have to do it over and over and over. I think you probably need to move your efforts to a non-production environment and start walking through every thing to find the places where it should be handled in the application.
Unfortunately connection pooling isn't something where you set it and forget it and too often things like what your seeing can happen. I think it takes more planning and discipline than most realize.Sue
Actually, by setting an Extended Events session that captures all sql_batch_starting events with the session_id, you could trace the blocker calls. I'm not sure if it's worth the trouble, though.
My bad - I didn't mean to imply that it being likely not being realistic meant it was not doable. I meant that it's a bit much especially if this is an active system. I can certainly understand why the DBAs wouldn't want to trace every call which is what they originally told the OP.
To me it just is more of an application issue than a database issue even if the symptoms show up in the database. If there is one area where it happens, there are likely others so I don't think it would end with finding just one either.
February 3, 2019 at 8:04 pm
SQL Guy 1 - Friday, February 1, 2019 8:33 AMI have encountered a situation when I cannot find out what stored procedure or query is blocking others. Here I put a simplified example:
Connection 1:
create table t1 (c1 int)
gobegin tran
insert into t1
values(1)Connection 2:
select * from t1Connection 1 - I wipe out all my code from here and run this:
getdate()Connection 3 - I need to find all the information about blocking. I run this:
select * from sys.sysprocesses
where blocked>0It happen to be SPID # 73. So I want to find what my blocker is running:
dbcc inputbuffer(73)It shows that the blocker is running getdate and it is blocking my t1 table somehow.
In our production we use connection pool from the application side. That means that once connection to SQL Server is set it will be reused for all consecutive calls. And if SPID number from SQL Server side is 73, it will stay as 73 for all the rest calls.
When production DBA's report to us that SPID 73 is blocking other processes, and they report that (in this example) getdate is blocking, it completely does not make a sense.
I thought about some reliable methods to find genuine blocker. One of them is to trace all SQL Server calls, but prod DBAs are against it. Another one is extended events, but it is practically the same is tracing.
So my question to forum is to how reliably check what command actually started the blocking?
Thanks
Of course it's going to show GETDATE(). That the last code executed on the SPID that has the blocking transaction.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply