인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block

 

·         Version : SQL Server

 

SQL Server에서 인덱스 재구성 통계 업데이트 작업을 일반적으로 DML(SELECT, INSERT, UPDATE, DELETE)문은 차단하지 않는 것으로 알고 있다. 하지만 인덱스 재구성할 SELECT 문에서 차단이 발생하는 경우가 있다. 아래 실습을 통해서 어떻게 차단이 발생하는지 알아본다.

 

실습을 진행하기 위해 간단한 시나리오를 만든다. 해당 시나리오는 SQL Server Adventurework2014 데이터베이스를 사용하며 데이터 집합을 만들기 위해 아래 링크의 스크립트를 적용하였다.

·         Enlarging the AdventureWorks Sample Databases : https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/

 

차단이 발생하는것을 재현하기 위해 ALTER INDE…REORGANIZE 실행하고 UPDATE 구문 SELECT 구문을 실행 한다.

세션 1에서 아래 스크립트를 실행 한다.

ALTER INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]

ON [Sales].[SalesOrderDetailEnlarged] REORGANIZE;

 

 

세션2에서 아래 스크립트를 실행한다.

UPDATE STATISTICS [Sales].[SalesOrderDetailEnlarged] WITH FULLSCAN, ALL;

 

sp_who2 실행하고 프로세스가 모두 실행중인지 확인한다.

SPID

Status

BlkBy

DBName

Command

57

RUNNABLE

.

AdventureWorks2014

DBCC

59

RUNNABLE

.

AdventureWorks2014

UPDATE STATISTIC

 

 

세션3에서 아래 스크립트를 실행한다.

SELECT *

FROM [Sales].[SalesOrderDetailEnlarged]

WHERE [SalesOrderId]=1302257;

 

sp_who2 실행하면 BlkBy 컬럼 정보를 통해서 블럭킹이 발생한 것을 확인할 있다.

SPID

Status

BlkBy

DBName

Command

57

RUNNABLE

.

AdventureWorks2014

DBCC

58

SUSPENDED

59

dventureWorks2014

SELECT

59

RUNNABLE

57

AdventureWorks2014

UPDATE STATISTIC

 

지금까지는 매우 간단한 시나리오였으며 SELECT UPDATE STATISTICS 의해 차단되고 UPDATE STATISTICS INDEX REORG(DBCC) 의해 차단되었음을 확인할 있다. 실제 서비스에서 sp_who2 실행하면 블로킹 체인의 SPID 한번에 확인하기 어려울 있으므로 아래 스크립트를 사용하면 전체 블럭킹 트리가 생성되고 체인에 포함된 SPID 확인할 있다.

(https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/ )

SET NOCOUNT ON

GO

 

SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH INTO #T

FROM sys.sysprocesses R

CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T

GO

 

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)

AS (SELECT

       SPID,

       BLOCKED,

       CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,

       BATCH

   FROM #T R

   WHERE (BLOCKED = 0 OR BLOCKED = SPID)

   AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)

   UNION ALL

   SELECT

      R.SPID,

      R.BLOCKED,

      CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,

      R.BATCH

   FROM #T AS R

   INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID

   WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID

   )

SELECT

   N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +

   CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END

   + CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE

FROM BLOCKERS ORDER BY LEVEL ASC

GO

 

DROP TABLE #T

GO

 

 

아래 출력을 보면 ALTER INDEX 체인의 머리 부분에 있고 UPDATE STATISTICS 차단되어 있어 간단한 SELECT문을 차단하고 있음을 확인할 있다.

BLOCKING_TREE

HEAD - 57 alter index [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]...

| |------ 59 UPDATE STATISTICS Sales.SalesOrderDetailEnlarged WITH FULLSCAN, ALL

| | |------ 58 SELECT * FROM [Sales].[SalesOrderDetailEnlarged] WHERE [SalesOrderId]=...

 

아래 스크립트를 사용하면 명령문에서 획득한 잠금을 보다 깊게 어떤 잠금이 잠금 경합을 일으키는지 확인할 있다. WHERE절에 SPID 수정해서 사용한다.

SELECT

  tl.request_session_id as spid,tl.resource_type,

  tl.resource_subtype,

  CASE

     WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)

     ELSE ''

  END AS object,

  tl.resource_description,

  request_mode,

  request_type,

  request_status,

  wt.blocking_session_id as blocking_spid

FROM sys.dm_tran_locks tl

LEFT JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address

WHERE tl.request_session_id in (57,58,59);

 

 

스크립트 실행결과에서 STATS 자원의 잠금이 차단을 야기하는 것을 있다. ALTER INDEX 보유한 Sch-S(스키마 안정성) 잠금은 UPDATE STATISTICS 획득하려고 시도하는 Sch-M(스키마 수정) 잠금을 차단한다. 이것은 SELECT 쿼리가 획득하려고 하는 Sch-S 잠금을 막는것이다.

AUTO_UPDATE_STATISTICS 활성화 되어 있고 AUTO_UPATE_STATISTICS_ASYNC 비활성화된 경우 조회를 실행하기 전에 통계가 갱신될 까지 SELECT 대기하게 되어 위에서 설명한 것과 동일한 상황이 발생할 있다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5880/why-is-index-reorganize-and-update-statistics-causing-sql-server-blocking/

 

 

2019-01-22 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, index reorganize, update statistics, 인덱스 재구성, 통계 업데이트, mssql, DBA

SQL Server Statistics Update row sampling rate and histogram step

-          통계 업데이트 샘플링 비율과 히스토그램 단계

 

·         Version : SQL Server

 

SQL Server에서 통계(statistics)정보는 데이터를 조회할때 검색 조건에 따라 옵티마이저가 해당 컬럼으로 구성되는 인덱스를 사용할지 여부를 결정하는 중요한 참고 정보이다. 통계는 데이터가 있는 컬럼에 대해 인덱스가 만들어질 컬럼값의 히스토그램 관련정보를 이용해서 만들어 진다.

·         SQL Server Statistics : http://sqlmvp.kr/140165557766

 

통계에 대한 샘플링 비율이 높을 수록 통계 정보의 신뢰도는 높아진다. 그렇다면 통계에 대한 샘플링 비율을 Full Scan(전체 샘플링)하였을때, 오히려 히스토그램의 단계가 줄어든 이유는 무엇일까?

히스토그램의 단계 수는 고유 수보다 적을 있다. 경계 지점이 200 미만인 열에도 고유 값의 개수보다 적을 있다. 고유 값만으로 구성된 열인 경우 통합된 히스토그램은 최소 3단계를 포함하게 된다. 따라서 완벽한 분포(빈도 = 1) 경우 데이터 분포를 정확하게 묘사하는데 필요한 단계가 많기 때문에 최소 3단계를 포함하게 된다.

 

아래 실습을 통해서 히스토그램에 대한 분포 변화를 살펴보자. IDENTITY GUID 사용하여1백만건을 데이터를 생성한다.

IF NOT EXISTS (SELECT [object_id] FROM sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(N'[CustomersTableGuid]') AND [type] IN (N'U'))

CREATE TABLE CustomersTableGuid

(

    ID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

)

GO

 

IF NOT EXISTS (SELECT [object_id] FROM sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(N'[CustomersTableIdent]') AND [type] IN (N'U'))

CREATE TABLE CustomersTableIdent

(

    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

)

GO

 

SET NOCOUNT ON;

DECLARE @i INT = 0

WHILE (@i <= 1000000)

BEGIN

    INSERT INTO CustomersTableGuid (FirstName, LastName)

    VALUES ('FirstName' + CAST(@i AS VARCHAR),'LastName' + CAST(@i AS VARCHAR))

 

    INSERT INTO CustomersTableIdent (FirstName, LastName)

    VALUES ('FirstName' + CAST(@i AS VARCHAR),'LastName' + CAST(@i AS VARCHAR))

   

    SET @i +=1

END

 

 

FULLSCAN으로 전체 행을 샘플링하여 통계를 업데이트 한다.

UPDATE STATISTICS CustomersTableGuid WITH FULLSCAN

GO

UPDATE STATISTICS CustomersTableIdent WITH FULLSCAN

GO

 

샘플된 행과 단계를 살펴보면 통계에 대해 3 또는 4단계 히스토그램이 있는 것을 확인할 있다.

SELECT OBJECT_NAME(stat.[object_id]) AS [TableName], sp.stats_id, name,

    last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter  

FROM sys.stats AS stat  

CROSS APPLY sys.dm_db_stats_properties(stat.[object_id], stat.stats_id) AS sp 

WHERE stat.[object_id] = OBJECT_ID('CustomersTableGuid')

    OR stat.[object_id] = OBJECT_ID('CustomersTableIdent');

GO

 


 

 

ID열에 대한 히스토그램을 살펴 보면 통계에 대해 3단계 또는 4단계로 표시된다. SQL Server 히스토그램 품질을 유지하면서 가능한 적은 단계로 여러 히스토그램을 압축 한다. 예상대로 FULLSCAN 완벽한 분포이므로 3단계로 압축 있다.

SELECT OBJECT_NAME(stat.[object_id]) AS [TableName], stat.name, sh.stats_id,

    sh.range_high_key, sh.range_rows, sh.equal_rows

FROM sys.stats AS stat

INNER JOIN sys.stats_columns AS sc

    ON stat.stats_id = sc.stats_id AND stat.[object_id] = sc.[object_id]

INNER JOIN sys.all_columns AS ac

    ON ac.column_id = sc.column_id AND ac.[object_id] = sc.[object_id]

CROSS APPLY sys.dm_db_stats_histogram(stat.[object_id], stat.stats_id) AS sh

WHERE (stat.[object_id] = OBJECT_ID('CustomersTableGuid')

    OR stat.[object_id] = OBJECT_ID('CustomersTableIdent'))

    AND ac.name = 'ID';

GO

 


 

통계 샘플을 90% 설정하여 업데이트 한다. (사용자 원한는 입력하여 테스트 가능)

UPDATE STATISTICS CustomersTableGuid WITH SAMPLE 90 PERCENT

GO

UPDATE STATISTICS CustomersTableIdent WITH SAMPLE 90 PERCENT

GO

 

통계 정보를 살펴본다. SQL Server 페이지 데이터를 가져온 다음 이를 전체 분포로 추정한다. 예상대로 표본 분포는 근사치로 빈도가 1 근접하지만 정확히 1 아니다.


 


 

실습을 통해서 히스토그램 단계가 많은 것이 나은 예측을 있다는 판단을 모든 환경에 적용된다고 없다. 하지만 테스트 결과는 유니크한 데이터를 기준으로만 판한단 것이므로 무조건 단계가 낮은것이 좋은 것으로 오해하지 않기를 바란다. 일반적으로 샘플링 비율이 높을 수록 상대적으로 정확한 통계 정보로 인해 옵티마이저가 나은 판단을 있다. 또한 대용량의 테이블 경우, 샘플링 비율이 높을 성능적인 문제가 발생하기 때문에, 통계 업데이트에 대한 주기 비율은 각자의 비즈니스 환경에 따라서 DBA 최적의 컨디션을 유지할 있도록 조절해야 한다.

 

[참고자료]

·         https://blogs.msdn.microsoft.com/sql_server_team/perfect-statistics-histogram-in-just-few-steps/

·         https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics

 

 

2018-01-22 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

SQL Server, MS SQL, SQL Statistics, Histogram, SQL Optimizer, SQL Index, query, DB Tuning, SQL Density, update statistics, sys.stats


인덱스 리빌드는 통계를 업데이트 할까?

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

인덱스를 리빌드하면 통계가 업데이트 될까? 이 질문에 많은 사람들은 "YES"라고 답할 것이다. 사실은 모든 통계를 업데이트를 하지 않는다. 인덱스를 리빌드 하는경우 해당 인덱스와 관련된 통계만 업데이트 된다.

 

Index Stats

Non-index stats

ALTER INDEX REORG

NO

NO

ALTER INDEX <index_name> REBUILD

해당 인덱스와 관련된 인덱스 통계 업데이트 됨

NO

ALTER INDEX ALL REBUILD

모든 인덱스 통계 업데이트 됨

NO

DBREINDEX (old syntax)

YES

YES

 

위에서 볼 수 있듯이 모든 통계는 인덱스 리빌드 작업을 통해서 업데이트 된다. 때로는 비 인덱스 통계도 매우 중요하다. 비인덱스 통계는 자동 또는 수동으로 생성된 관련 통계를 의미한다. 임계값이 큰 테이블의 경우 수동 통계 업데이트가 필요할 수 있다. 추적플래그 2371이 도움이 될 수 있다.

 

  • Statistical maintenance functionality (autostats) in SQL Server :

 

http://support.microsoft.com/ko-kr/kb/195565

  • Changes to automatic update statistics in SQL Server – traceflag 2371 :

http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

 

실습을 통해 모든 통계를 업데이트 하지 않는 경우를 확인 할 수 있다.

 

[샘플 데이터 생성]

if object_id ('t') is not null

drop table t

go

create table t(c1 int, c2 as c1 & 1)

go

 

create index t1_indx1 on t(c1 )

go

set nocount on

declare @i int

set @i = 0

while @i < 1000

begin

insert into t (c1) values (@i)

set @i = @i + 1

end

go

 

update statistics t with fullscan

go

 

go

--this will create a stats on c2

select count(*) from t where c2 =1

 

go

 

[통계 정보 확인]

SELECT

obj.name, stat.name, stat.stats_id, last_updated

FROM sys.objects AS obj

JOIN sys.stats stat ON stat.object_id = obj.object_id

CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp

where obj.name = 't'

 

 

[인덱스 리빌드 진행]

-- alter all indexes

alter index all on t rebuild

--re-organize won't update even stats of the index

--alter index all on t reorganize

 

통계 정보를 확인해 보면 t1_index1의 통계는 최근 업데이트가 반영된 것을 확인 할 수 있으며 __WA_Sys_00000002_34C8D9D1 은 업데이트가 되지 않은 것을 확인 할 수 있다.

SELECT

obj.name, stat.name, stat.stats_id, last_updated

FROM sys.objects AS obj

JOIN sys.stats stat ON stat.object_id = obj.object_id

CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp

where obj.name = 't'

 

 

 

 

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2015/03/06/does-rebuild-index-update-statistics.aspx

 

 

2015-03-16 / 강성욱 / http://sqlmvp.kr

 

 

Sqlserver, msslq, sqlmvp, sql index, 인덱스 통계, 인덱스 리빌드, Index Rebuild, Update Statistics, 인덱스 유지관리작업

+ Recent posts