February 13, 2023 at 2:52 pm
Hi All,
I came across a blocking scenario where everyone thinks its SELECT is blocking the UPDATE operation but it's not true.
The DMVs are showing up only current batch or current sql text which was executed.
My question:
How to prove its not the SELECT but its the DML update which took the 'X' lock and because of which we
Is there a DMV query which can show me all the sql stmts ran inside a session/spid so far.
I tried with sp_wia and sysprocesses but both giving me the current batch. How can I get the entire sql block of stmts which were executed so far within that session using DMVs? I know we should be able to get this using trace/XE but looking for any DMV for Live troubleshooting which shows me the sql stmts run in that session, transaction id info and what locks held by each session which are involved in blocking.
Repro steps
===========
CREATE TABLE [dbo].[DEPT](
[DEPTNO] [int] NOT NULL primary key,
[DNAME] [varchar](14) NULL,
[LOC] [varchar](13) NULL,
)
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (10, N'ACCOUNTING',N'NEW YORK')
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (20, N'RESEARCH',N'DALLAS')
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (30, N'SALES',N'CHICAGO')
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (40, N'OPERATIONS',N'BOSTON')
GO
session1 -- blocker
-- First, I have highlighted/selected only the UPDATE portion and executed as one batch
-- next , I have highlighted/selected only "select * from dept;" and ran it
--------------------------
use db1
go
BEGIN TRAN
UPDATE dept
SET [loc] = '*******'
WHERE DEPTNO=10; --//updating 1 row
select * from dept; --//u ll see changed values
--- ROLLBACK;
Then I have opened two new sessions and ran below sql stmts in respective ssms windows.
session2 -- victim1
--------------------------
use db1
go
SELECT * FROM dept;
go
session3 -- victim2
--------------------------
use db1
go
UPDATE dept
SET [loc] = '$$$$$'; --//trying to update all rows but gets blocked
--rollback;
go
----monitor queries
sp_WhoIsActive
@Output_Column_List = '[session_id][blocking_session_id][status][wait_info][open_tran_count][sql_text]'
, @Find_Block_Leaders = 1
, @Sort_Order = '[blocked_session_count] DESC'
go
---other way using sysprocesses
SELECT
s.spid,
s.blocked as blockedby,
s.[dbid],
db_name(s.[dbid]) as dbname,
s.open_tran,
s.[status],
s.loginame,
s.last_batch,
s.hostname,
s.[program_name],
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' +
QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS 'stored_proc',
st.[text] as qry_txt
from sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS st
where blocked<>0 --blocked victims
OR s.spid in ( select blocked from sys.sysprocesses where blocked<>0 ) -- i want to show blockers info as well
--order by s.spid
Many thanks,
Sam
February 13, 2023 at 5:58 pm
I would download and install sp_whoisactive
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 14, 2023 at 9:30 am
I am already using it but not helpful in my case it is giving only the current sql stmt.
February 14, 2023 at 12:36 pm
I am already using it but not helpful in my case it is giving only the current sql stmt.
Ok, one of the parameters is to capture the output to a table. Set up a SQL job to capture that output on a regular basis, and use that to show the users what is blocking.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 14, 2023 at 1:09 pm
Have you checked the error logs?
February 14, 2023 at 1:35 pm
Have you captured the blocked process report? That's going to show you the precise blocking chain. You'll have a total story, and one that cannot be argued with. And yeah, if the blocking chain of the blocked process report shows that it's a SELECT statement, then it is 100% a SELECT statement that is causing the blocking.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply