Archive
sys.dm_exec_requests error for “blocking_session_id”
Recently, I found that one of the DMV return error data when specific case.
sys.dm_exec_requests return error result of “blocking_session_id” data when CXPACKET is blocked by other session.
Here is the simulation
1. Create test data (if your server isn’t working as parallelism on #3, you need to increase top count)
| use test go if object_id(‘test1′) is not null select top 1000000 identity(int, 1,1) idx, c1.* into test1 from sys.syscolumns c1 cross join sys.syscolumns c2 cross join sys.syscolumns c3 go ALTER TABLE dbo.test1 ADD CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED (idx) go |
2. Open new Query on your SSMS and Update one record to make lock processing without close the transaction. (Session 1)
| Use test Go begin transaction update test1 –rollback transaction |
3. Open another new Query on your SSMS and run parallelism query.(Session 2)
| set transaction isolation level read committed exec sp_configure ‘show advanced options’, 1 reconfigure with override exec sp_configure ‘max degree of parallelism’,0 reconfigure with override select @@SPID go select COUNT(*) from test1 where name like ‘%object%’ option (maxdop 0) |
Before run the query, please see the execution plan to make sure this query is working as parallelism.
And run the query. It should keep running due to open transaction
4. Compare sp_who2 result, sys.sysprocesses and sys.dm_exec_requests result.
| – 53 is the session id of #3 exec sp_who2 53 select spid, blocked from sys.sysprocesseswhere spid=53 select session_id, blocking_session_id, wait_type from sys.dm_exec_requests where session_id=53 |
Result of sys.dm_exec_requets
The reason is that #2 just blocked 1 record to update.
#3 query is heavy, so it’s running as parallelism.
But, one of parallelism query can’t finish it due to blocking process of update lock.
So, it’s waiting for updating.
In this case, the sys.dm_exec_requests should return blocking session id as 59.
But, it returns as 0.
It’s error data.
Thanks.
Simon Cho
Replication status check
select
h.agent_id
, a.name
, rm.publication as pub_name
, rm.publisher_db as pub_db
, a.publisher_database_id as pub_db_id
, a.subscriber_db as sub_db
, comments
, h.time
, isnull(rm.cur_latency, 0) as cur_latency
, pub_srv.name as pub_srv
, sub_srv.name as sub_srv
from
distribution_svg011.dbo.msdistribution_agents a with(nolock)
join distribution_svg011.dbo.MSreplication_monitordata rm with(nolock) on rm.agent_id=a.id
join master.sys.servers pub_srv with(nolock) on a.publisher_id = pub_srv.server_id
join master.sys.servers sub_srv with(nolock) on a.subscriber_id= sub_srv.server_id
cross apply
(
select top 1 *
From distribution_SVG011.dbo.msdistribution_history with(nolock)
where agent_id=a.id
order by timestamp desc
) h
order by pub_db, pub_name
Available File group size
SELECT ISNULL(b.groupname, 'LOG') AS 'File Group' , Name , [Filename] , CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space(MB)] , CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)] , CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)] , CONVERT(Decimal(15,2),CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))/CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2))*100) FreeSpace_Ratio FROM dbo.sysfiles a (NOLOCK) left outer JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid --ORDER BY b.groupname
object size each table and index (update for partitioning table)
This query can check each table and index
And also, if it is partitioning table, it will show up partition # and each partition # size.
select FILEGROUP_NAME(au.data_space_id) as file_group
, o.name
, i.index_id
, i.name as index_name
, au.partition_number
, au.size_MB
, case when i.data_space_id > 65000 then 'Y' else 'N' end IsPartitionedTable
from (
select object_id, index_id, partition_number, data_space_id
, convert(numeric(10,2)
, SUM(in_row_data_page_count
+ lob_used_page_count
+ row_overflow_used_page_count) * 8/1024.0) size_MB
from sys.allocation_units au
join sys.dm_db_partition_stats ps
on au.container_id = ps.partition_id
group by object_id, index_id, partition_number, data_space_id
) au
join sys.indexes i
on i.object_id = au.object_id and i.index_id = au.index_id
join sys.all_objects o
on i.[object_id] = o.[object_id] and o.type='U'
order by 1,2,3,4
Disk IO response time check.
select convert(varchar(19), getdate(), 121) as date_time
, db_name(df.database_id) as DatabaseName
, f.name as FileLogicalName
, f.filename
, case when num_of_reads+num_of_writes>0 then io_stall/(num_of_reads+num_of_writes) else 0 end as ‘ms/io’
, case when num_of_reads>0 then io_stall_read_ms/num_of_reads else 0 end as ‘ms/Read io’
, case when num_of_writes>0 then io_stall_write_ms/num_of_writes else 0 end as ‘ms/Write io’
from sys.dm_io_virtual_file_stats(null,null) as df
join sys.sysaltfiles as f
on f.dbid= df.database_id
and f.fileid = df.file_id
–where df.database_id in(DB_ID(‘db_passport’), DB_ID(‘TempDB’))
where case when num_of_reads+num_of_writes>0 then io_stall/(num_of_reads+num_of_writes) else 0 end >50
or case when num_of_reads>0 then io_stall_read_ms/num_of_reads else 0 end >50
or case when num_of_writes>0 then io_stall_write_ms/num_of_writes else 0 end >50
order by 5 desc
sp_now : rewrite sp_who2
This SP is very helpful to troubleshooting concurrent issue.
use master
go
if object_id('sp_now') is not null
drop procedure sp_now
go
create procedure [dbo].[sp_now]
@session_id int = null
,@xml_on bit = 0
,@text nvarchar(500) =''
,@back_on bit = 0
as
/***********************************************************************************************
**Object Name: dbo.sp_now
**
**Description: Revise sp_who2
**
**Input Parameters:
@session_id int = null
, @text nvarchar(500) =''
, @back_on bit = 0
**
**Return Value: N/A
**
**Return Result Set: session info
**
**Creator: Simon Cho
**
**Example: exec [sp_now]
**
*************************************************************************************************/
begin
set nocount on;
set transaction isolation level read uncommitted;
select
sp.spid as s_id
, max(sp.blocked) as BlkBy
, substring(max(convert(varchar(23),r.start_time,121)+sp.status), 24, 50) status
, rtrim(max(sp.loginame)) as Login
, rtrim(ltrim(max(sp.hostname))) as hostname
, min(r.start_time) as st_time
, convert(numeric(10,2),max(r.total_elapsed_time/1000)/60.0) [run_time(min)]
--, r.total_elapsed_time
, GETDATE() as current_datetime
, max(sp.waittime) as waittime
, substring(max(convert(varchar(23),r.start_time,121)+r.wait_type), 24, 50) wait_type
, db_name(max(sp.dbid)) as db
, substring(max(convert(varchar(23),r.start_time,121)+command), 24, 50) command
, max(r.cpu_time) as cpu_time
, max(r.reads) as reads
, max(r.writes) as writes
, max(r.logical_reads) as logical_reads
, rtrim(max(sp.program_name)) as program_name
, max(c.client_net_address) ip
, min(sp.login_time) as login_time
, convert(numeric(10,2),max(r.granted_query_memory)/128.0) as granted_query_memory_MB
--, t.objectid
, OBJECT_NAME(t.objectid, t.dbid) objname
, convert(xml,'<?Q--'+CHAR(13)+CHAR(10)+substring(max(case when text like 'Fetch%' then substring(text, 1,32) else text end)
, (case when (statement_start_offset/2)+1') as text_stmt_xml
--, max(t.text) as text
, case when @xml_on = 1 then (select query_plan from sys.dm_exec_query_plan(max(r.plan_handle)))
else convert(xml, '')
end as query_plan
from sys.sysprocesses sp with(nolock)
join sys.dm_exec_requests r with(nolock) on r.session_id=sp.spid
left join sys.dm_exec_connections c with(nolock) on r.connection_id=c.connection_id
outer apply sys.dm_exec_sql_text(r.sql_handle) t
where 1=1
-- and sp.spid!=@@SPID
and case when @session_id is null then 1 else sp.spid end
= case when @session_id is null then 1 else @session_id end
and case when @text='' then '1' else text end
like case when @text='' then '1' else '%'+@text+'%' end
and case when @back_on = 0 then sp.spid else 51 end > 50
and case when @back_on = 0 then sp.status else 'all' end != 'background'
group by sp.spid
, sp.dbid, t.dbid
, t.objectid
, r.statement_start_offset
, r.statement_end_offset
, r.transaction_id
, c.connection_id
, sp.blocked
end
--exec sys.sp_MS_marksystemobject 'sp_now'
