Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Posts Tagged ‘blocking’

Update statistics blocking issue(Part 1)

Posted by Simon Cho on 11/30/2017

Update statistics is known as online operation.

Detail lock mode is in below blog.

https://www.mssqltips.com/sqlservertip/4608/does-updating-sql-server-statistics-cause-blocking/

 

However, update statistics could create seriouly blocking issue such as “SCH-M” in certain case.

Here is detail lock mode in certain case.

<Database name="TEST">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="Tbl_cdc1" schema_name="dbo">
      <Locks>
        <Lock resource_type="METADATA.INDEXSTATS" index_name="PK_Tbl_cdc1" request_mode="Sch-S" request_status="GRANT" request_count="2" />
        <Lock resource_type="METADATA.STATS" request_mode="Sch-M" request_status="CONVERT" request_count="1" />
        <Lock resource_type="METADATA.STATS" request_mode="Sch-S" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="2" />
        <Lock resource_type="OBJECT.UPDSTATS" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

Please check that “request_mode=“Sch-M” request_status=”CONVERT“”.

I’m currently investigating detail.

I’ll share later in Part2.

 

Simon

Update statistics blocking issue(Part 1)

Advertisements

Posted in Common | Tagged: , | Leave a Comment »

sp_now : rewrite sp_who2

Posted by Simon Cho on 08/03/2011

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’

Posted in Common | Tagged: , , , , | 1 Comment »