Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

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.


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">
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
    <Object name="Tbl_cdc1" schema_name="dbo">
        <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" />

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

I’m currently investigating detail.

I’ll share later in Part2.



Update statistics blocking issue(Part 1)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s