Performance & Tuning

Update statistics blocking issue(Part 1)

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


<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)