I've used SQL Server Management Studio for many years and whilst there's other options out there like Azure Data Studio, SQLCMD or PowerShell it's mainly my go to option for doing stuff in SQL Server.
For most DBA I write T-SQL code; for example I haven't used Activity Monitor for a long time and instead prefer the use of Dynamic Management Objects to query out the bits of information that I need.
But Management Studio does have some useful functionality in there. If I right click on a database, select Reports and Standard Reports I get a list of all kinds of different reports where I see what's
I'll be honest, I've occasionally used the Disk Usage reports and apart from that I had no idea what else was in here so I've decided to look at the All Blocking Transactions report in action being that it's a fairly common monitoring task and in order to do that I'll run the following update statement in a transaction without committing it or rolling it back:
BEGIN TRANUPDATE Person.Person
SET FirstName = 'Dave'
WHERE FirstName = 'David' and LastName = 'Bartness';
When I run this code I will get a message saying that 1 row has been affected but as my transaction is still open my update has not been committed which means if I try to run a select query on that same table in another query window I will run into a classic case of blocking:
SELECT * FROM [AdventureWorks2019].[Person].[Person]
My select query is now in a bit of limbo, and in Management Studio I can see that it's stuck executing my query, now let's run the report:
In order to see what queries are being blocked I can expand the first column of the report, Transaction ID (in this case 151616) which returns the details of my blocked select statement:
Now as I mentioned earlier on in the post I don't often use Management Studio for much else other than writing queries. I've captured the query used by this particular report and it's, well here it is:
exec sp_executesql @stmt=N'begin try
declare @tab_tran_locks as table(
database_id int
, l_resource_type nvarchar(60) collate database_default
, l_resource_subtype nvarchar(60) collate database_default
, l_resource_associated_entity_id bigint
, l_blocking_request_spid int
, l_blocked_request_spid int
, l_blocking_request_mode nvarchar(60) collate database_default
, l_blocked_request_mode nvarchar(60) collate database_default
, l_blocking_tran_id bigint
, l_blocked_tran_id bigint
);
declare @tab_blocked_tran as table (
tran_id bigint
, no_blocked bigint
);
declare @temp_tab table(
blocking_status int
, no_blocked int
, database_id int
, l_resource_type nvarchar(60) collate database_default
, l_resource_subtype nvarchar(60) collate database_default
, l_resource_associated_entity_id bigint
, l_blocking_request_spid int
, l_blocked_request_spid int
, l_blocking_request_mode nvarchar(60) collate database_default
, l_blocked_request_mode nvarchar(60) collate database_default
, l_blocking_tran_id bigint
, l_blocked_tran_id bigint
, local1 int
, local2 int
, b_tran_id bigint
, w_tran_id bigint
, b_name nvarchar(128) collate database_default
, w_name nvarchar(128) collate database_default
, b_tran_begin_time datetime
, w_tran_begin_time datetime
, b_state nvarchar(60) collate database_default
, w_state nvarchar(60) collate database_default
, b_trans_type nvarchar(60) collate database_default
, w_trans_type nvarchar(60) collate database_default
, b_text nvarchar(max) collate database_default
, w_text nvarchar(max) collate database_default
, db_span_count1 int
, db_span_count2 int
);
insert into @tab_tran_locks
select
a.resource_database_id
, a.resource_type
, a.resource_subtype
, a.resource_associated_entity_id
, a.request_session_id as blocking
, b.request_session_id as blocked
, a.request_mode
, b.request_mode
, a.request_owner_id
, b.request_owner_id
from sys.dm_tran_locks a
join sys.dm_tran_locks b on (a.resource_type = b.resource_type and a.resource_subtype = b.resource_subtype and a.resource_associated_entity_id = b.resource_associated_entity_id and a.resource_description = b.resource_description)
where (a.request_status = ''GRANT'' and (b.request_status = ''WAIT'' or b.request_status = ''CONVERT'')) and a.request_owner_type = ''TRANSACTION'' and b.request_owner_type = ''TRANSACTION'';
insert into @tab_blocked_tran
select ttl.l_blocking_tran_id
, count(distinct ttl.l_blocked_tran_id)
from @tab_tran_locks ttl
group by ttl.l_blocking_tran_id
order by count( distinct ttl.l_blocked_tran_id) desc
insert into @temp_tab
select 0 as blocking_status
, tbt.no_blocked
, ttl.*
, st1.is_local as local1
, st2.is_local as local2
, st1.transaction_id as b_tran_id
, ttl.l_blocked_tran_id as w_tran_id
, at1.name as b_name
, at2.name as w_name
, at1.transaction_begin_time as b_tran_begin_time
, at2.transaction_begin_time as w_tran_begin_time
, case when at1.transaction_type <> 4
then case at1.transaction_state
when 0 then ''Invalid''
when 1 then ''Initialized''
when 2 then ''Active''
when 3 then ''Ended''
when 4 then ''Commit Started''
when 5 then ''Prepared''
when 6 then ''Committed''
when 7 then ''Rolling Back''
when 8 then ''Rolled Back''
end
else case at1.dtc_state
when 1 then ''Active''
when 2 then ''Prepared''
when 3 then ''Committed''
when 4 then ''Aborted''
when 5 then ''Recovered''
end
end b_state
, case when at2.transaction_type <> 4
then case at2.transaction_state
when 0 then ''Invalid''
when 1 then ''Initialized''
when 2 then ''Active''
when 3 then ''Ended''
when 4 then ''Commit Started''
when 5 then ''Prepared''
when 6 then ''Committed''
when 7 then ''Rolling Back''
when 8 then ''Rolled Back''
end
else case at1.dtc_state
when 1 then ''Active''
when 2 then ''Prepared''
when 3 then ''Committed''
when 4 then ''Aborted''
when 5 then ''Recovered''
end
end w_state
, at1.transaction_type as b_trans_type
, at2.transaction_type as w_trans_type
, case when r1.sql_handle IS NULL then ''--'' else ( select top 1 substring(text,(r1.statement_start_offset+2)/2, (case when r1.statement_end_offset = -1 then (len(convert(nvarchar(MAX),text))*2) else r1.statement_end_offset end - r1.statement_start_offset) /2 ) from sys.dm_exec_sql_text(r1.sql_handle)) end as b_text
, case when r2.sql_handle IS NULL then ''--'' else ( select top 1 substring(text,(r2.statement_start_offset+2)/2, (case when r2.statement_end_offset =-1 then len(convert(nvarchar(MAX),text))*2 when r2.statement_end_offset =0 then len(convert(nvarchar(MAX),text))*2 else r2.statement_end_offset end - r2.statement_start_offset) /2 ) from sys.dm_exec_sql_text(r2.sql_handle)) end as w_text
, ( Select count(distinct database_id) from sys.dm_tran_database_transactions where transaction_id = st1.transaction_id ) as db_span_count1
, ( Select count(distinct database_id) from sys.dm_tran_database_transactions where transaction_id = st2.transaction_id ) as db_span_count2
from @tab_tran_locks ttl
inner join sys.dm_tran_active_transactions at1 on(at1.transaction_id = ttl.l_blocking_tran_id)
inner join @tab_blocked_tran tbt on(tbt.tran_id = at1.transaction_id)
inner join sys.dm_tran_session_transactions st1 on(at1.transaction_id = st1.transaction_id)
left outer join sys.dm_exec_requests r1 on(at1.transaction_id = r1.transaction_id )
inner join sys.dm_tran_active_transactions at2 on(at2.transaction_id = ttl.l_blocked_tran_id)
left outer join sys.dm_tran_session_transactions st2 on(at2.transaction_id = st2.transaction_id)
left outer join sys.dm_exec_requests r2 on(at2.transaction_id = r2.transaction_id )
where st1.is_user_transaction = 1
order by tbt.no_blocked desc;
declare @db_blocking_tab table (
database_id int
, blocking_status int
, no_blocked int
, total_blocked int
, l_resource_type nvarchar(60) collate database_default
, l_resource_subtype nvarchar(60) collate database_default
, l_resource_associated_entity_id bigint
, l_blocking_request_spid int
, l_blocked_request_spid int
, l_blocking_request_mode nvarchar(60) collate database_default
, l_blocked_request_mode nvarchar(60) collate database_default
, local1 int
, local2 int
, b_tran_id bigint
, w_tran_id bigint
, b_name nvarchar(128) collate database_default
, w_name nvarchar(128) collate database_default
, b_tran_begin_time datetime
, w_tran_begin_time datetime
, b_state nvarchar(60) collate database_default
, w_state nvarchar(60) collate database_default
, b_trans_type nvarchar(60) collate database_default
, w_trans_type nvarchar(60) collate database_default
, b_text nvarchar(max) collate database_default
, w_text nvarchar(max) collate database_default
, db_span_count1 int
, db_span_count2 int
, lvl int
);
declare @b_tran_id_tab table ( tran_id bigint);
WITH Blocking(
database_id
, blocking_status
, no_blocked
, total_blocked
, l_resource_type
, l_resource_subtype
, l_resource_associated_entity_id
, l_blocking_request_spid
, l_blocked_request_spid
, l_blocking_request_mode
, l_blocked_request_mode
, local1
, local2
, b_tran_id
, w_tran_id
, b_name
, w_name
, b_tran_begin_time
, w_tran_begin_time
, b_state
, w_state
, b_trans_type
, w_trans_type
, b_text
, w_text
, db_span_count1
, db_span_count2
, lvl)
AS ( SELECT
database_id
, blocking_status
, no_blocked
, no_blocked
, l_resource_type
, l_resource_subtype
, l_resource_associated_entity_id
, l_blocking_request_spid
, l_blocked_request_spid
, l_blocking_request_mode
, l_blocked_request_mode
, local1
, local2
, b_tran_id
, w_tran_id
, b_name
, w_name
, b_tran_begin_time
, w_tran_begin_time
, b_state
, w_state
, b_trans_type
, w_trans_type
, b_text
, w_text
, db_span_count1
, db_span_count2
, 0
from @temp_tab
UNION ALL
SELECT E.database_id
, E.blocking_status
, M.no_blocked
, convert(int,E.no_blocked + total_blocked)
, E.l_resource_type
, E.l_resource_subtype
, E.l_resource_associated_entity_id
, M.l_blocking_request_spid
, E.l_blocked_request_spid
, M.l_blocking_request_mode
, E.l_blocked_request_mode
, M.local1
, E.local2
, M.b_tran_id
, E.w_tran_id
, M.b_name
, E.w_name
, M.b_tran_begin_time
, E.w_tran_begin_time
, M.b_state
, E.w_state
, M.b_trans_type
, E.w_trans_type
, M.b_text
, E.w_text
, M.db_span_count1
, E.db_span_count2
, M.lvl+1
from @temp_tab AS E
JOIN Blocking AS M ON E.b_tran_id = M.w_tran_id )
insert into @db_blocking_tab
select * from Blocking
insert into @b_tran_id_tab
select top 20 b_tran_id from @db_blocking_tab
where database_id = db_id() group by b_tran_id order by max(total_blocked) desc ;
select (dense_rank() over (order by dbt.no_blocked desc,dbt.b_tran_id))%2 as l1
, (dense_rank() over (order by dbt.no_blocked desc,dbt.b_tran_id,dbt.w_tran_id))%2 as l2
, dbt.*
from @b_tran_id_tab btid
left outer join @db_blocking_tab dbt on (btid.tran_id = dbt.b_tran_id)
order by dbt.no_blocked desc, dbt.b_tran_id,dbt.w_tran_id
end try
begin catch
select -100 as l1
, ERROR_NUMBER() as l2
, ERROR_SEVERITY() as blocking_status
, ERROR_STATE() as no_blocked
, ERROR_MESSAGE() as total_blocked
, 1 as l_resource_type,1 as l_resource_subtype,1 as l_resource_associated_entity_id,1 as l_blocking_request_spid,1 as l_blocked_request_spid,1 as l_blocking_request_mode,1 as l_blocked_request_mode,1 as local1,1 as local2,1 as b_tran_id,1 as w_tran_id,1 as b_name,1 as w_name,1 as b_tran_begin_time,1 as w_tran_begin_time,1 as b_state,1 as w_state,1 as b_trans_type,1 as w_trans_type,1 as b_text,1 as w_text,1 as db_span_count1,1 as db_span_count2,1 as lvl
end catch',@params=N''
That's a pretty big query and whilst it does return some useful query level information I can't see other information such as the login details or the host name of the blocked/blocking queries that I might need to determine who to blame, sorry I mean take the appropriate course of action for resolving the blocking problems.
So is there a better way?
Well in my opinion, absolutely yes!
I could use a third party script such as sp_whoisactive or this one from Pinal Dave both of which I can honestly say I use all of the time when investigating blocking, but I also encourage people to spend time looking into SQL's Dynamic Management Objects and the DMV's such as sys.dm_exec_requests and sys.dm_tran_locks, both of which are used in the query from the All Blocking Transactions report above. It's a great way to start peeking into the internals of SQL Server and working with their information to create custom scripts for monitoring and diagnosing problems.
No comments:
Post a Comment