人人為我 我為人人

當我們在線上 UPDATE STATISTICS 時，會不會造成 LOCK 呢？

可能會有人很擔心的問，這個動作會不會造成什麼嚴重的後果？

那我們就來實際驗證一下 UPDATE STATISTICS 會產生什麼樣的鎖吧～

首先來建立一個驗證環境

USE [master]
GO
CREATE DATABASE Demo CONTAINMENT = NONE ON  PRIMARY 
( NAME = N'Demo', FILENAME = N'D:\Temp\Demo.mdf', SIZE = 128MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB) 
 LOG ON 
( NAME = N'Demo_log', FILENAME = N'D:\Temp\Demo.ldf', SIZE = 8MB, MAXSIZE = 2048GB, FILEGROWTH = 8MB)
GO
ALTER DATABASE Demo SET RECOVERY SIMPLE
GO
ALTER DATABASE Demo SET AUTO_UPDATE_STATISTICS OFF
GO

再建立一個測試資料表

USE Demo
GO
IF OBJECT_ID('dbo.Test','U') IS NOT NULL
    DROP TABLE dbo.Test
GO
CREATE TABLE dbo.Test(
  [Id]        INT NOT NULL IDENTITY(1,1),
  [Name]      NVARCHAR(50) NOT NULL,
  [Age]      INT NOT NULL,
  [Address]   NVARCHAR(50) NOT NULL,
  [CreateDate]  DATETIME NOT NULL DEFAULT  GETDATE()
  PRIMARY KEY CLUSTERED (Id),
  INDEX IDX_Age NONCLUSTERED (Age)
)
GO

塞入一些測試用的資料

USE Demo
GO
CREATE TABLE [TempData1] ([Name] nvarchar(50))  
CREATE TABLE [TempData2] ([Age] int) 
CREATE TABLE [TempData3] ([Address] nvarchar(50))
CREATE TABLE [TempData4] ([Modified] datetime default getdate()) 

INSERT INTO TempData1 VALUES ('John')  
INSERT INTO TempData1 VALUES ('Jack')  
INSERT INTO TempData1 VALUES ('Jill')  
INSERT INTO TempData1 VALUES ('Bill')  
INSERT INTO TempData1 VALUES ('Mary')  
INSERT INTO TempData1 VALUES ('Kate')  
INSERT INTO TempData1 VALUES ('Kevin')  
INSERT INTO TempData1 VALUES ('Matt')  
INSERT INTO TempData1 VALUES ('Rachel')  
INSERT INTO TempData1 VALUES ('Tom')  
 
INSERT INTO TempData2 VALUES (40)  
INSERT INTO TempData2 VALUES (39)  
INSERT INTO TempData2 VALUES (38)  
INSERT INTO TempData2 VALUES (37)  
INSERT INTO TempData2 VALUES (36)  
INSERT INTO TempData2 VALUES (35)  
INSERT INTO TempData2 VALUES (34)  
INSERT INTO TempData2 VALUES (33)  
INSERT INTO TempData2 VALUES (32)  
INSERT INTO TempData2 VALUES (31)  

INSERT INTO TempData3 VALUES ('Taipei')  
INSERT INTO TempData3 VALUES ('Banqiao')  
INSERT INTO TempData3 VALUES ('Taoyuan')  
INSERT INTO TempData3 VALUES ('Zhongli')  
INSERT INTO TempData3 VALUES ('Hsinchu')  
INSERT INTO TempData3 VALUES ('Taichung')  
INSERT INTO TempData3 VALUES ('Changhua')  
INSERT INTO TempData3 VALUES ('Douliou')  
INSERT INTO TempData3 VALUES ('Chiayi')  
INSERT INTO TempData3 VALUES ('Tainan')  
INSERT INTO TempData3 VALUES ('Kaohsiung')  
INSERT INTO TempData3 VALUES ('Pingtung')  
INSERT INTO TempData3 VALUES ('Yilan')  
INSERT INTO TempData3 VALUES ('Hualien')  
INSERT INTO TempData3 VALUES ('Taitung')  

INSERT INTO TempData4 VALUES (DATEADD(DAY, -1, GETDATE()))  
INSERT INTO TempData4 VALUES (DATEADD(DAY, -2, GETDATE()))  
INSERT INTO TempData4 VALUES (DATEADD(DAY, -3, GETDATE()))  
INSERT INTO TempData4 VALUES (DATEADD(DAY, -4, GETDATE()))  
INSERT INTO TempData4 VALUES (DATEADD(DAY, -5, GETDATE()))  
INSERT INTO TempData4 VALUES (DATEADD(DAY, -6, GETDATE()))  
INSERT INTO TempData4 VALUES (DATEADD(DAY, -7, GETDATE()))  
INSERT INTO TempData4 VALUES (DATEADD(DAY, -8, GETDATE()))  
INSERT INTO TempData4 VALUES (DATEADD(DAY, -9, GETDATE()))  
INSERT INTO TempData4 VALUES (DATEADD(DAY, -10, GETDATE()))  

INSERT INTO Test SELECT [Name], [Age], [Address], [Modified] 
FROM TempData1 
CROSS JOIN TempData2 
CROSS JOIN TempData3 
CROSS JOIN TempData4 
GO 10000

接下來我們開始更新統計值並且去 sys.dm_tran_locks 看看更新統計值會使用哪些鎖

USE Demo
GO
UPDATE STATISTICS dbo.Test WITH FULLSCAN, ALL
GO

USE [master]
GO
SELECT resource_type ,
       resource_subtype ,
       resource_description ,
       resource_associated_entity_id ,
       request_mode ,
       request_type ,
       request_status ,
       request_session_id  
FROM sys.dm_tran_locks
WHERE request_session_id = 53
GO

從結果中可以看到 UPDATE STATISTICS 會先對資料庫取得一個 shared lock

接下來我們看到 resource_subtype 為 INDEXSTATS、STATS (resource_description 的 object_id 及 index_id 或 stats_id)

會取得 Sch-S (結構描述固定性 Sch-S 鎖定與所有鎖定模式相容，除了結構描述修改 Sch-M 鎖定模式以外。Sch-M 鎖定與所有鎖定模式皆不相容。)

在 OBJECT 也看到兩個 Sch-S 鎖，唯一看見一個 X 鎖在 UPDSTATS (這表示唯一被鎖定的只有統計數據)

由此可證，當進行 ​UPDATE STATISTICS 時並不會造成 BLOCKING。

have fun ʕ•͡ᴥ•ʔ