DBCC CHECKDB와 Compute Column 인덱스의 성능 관계

DBCC CHECKDB와 Compute Column 인덱스의 성능 관계

 

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

 

하단의 참고 자료를 바탕으로 내용을 이해 하려고 하였으며 테스트를 진행 하였지만 원하는 결과가 도출 되지 않았다. (첨부 그림은 필자가 테스트한 내용이다.) 자세한 내용은 참고자료를 확인 하길 바란다.

 

참고 자료에 의하면 우리가 흔히 사용하는 DBCC CHECKDB (CHECKTABLE_의 성능이 계산된 열의 인덱스를 포함하고 있을 때 성능이 느려진다고 한다.

 

계산된 열에 비클러스터형 인덱스를 사용하는 경우 계산된 열의 값은 열 정의에 따라 계산되어야 한다. 이를 위해 내부 메커니즘은 'expression evaluator'이라는 것을 생성 한다. 'expression evaluator'는 쿼리 프로세스에서 제공한다. 이는 DBCC CHECKDB는 제어권을 벗어난다. 이는 매우 큰 병목을 생성하고 성능에 영향을 미친다고 한다.

(해석이 원만하지 않아 원문 첨부 합니다.)

When a nonclustered index uses a computed column, the value of the computed column has to be computed based on the column definition. To do that, an internal mechanism called an 'expression evaluator' is created. The expression evaluator is provided by the Query Processor code and its behavior is entirely outside the control of DBCC CHECKDB. The drawback of the expression evaluator is that every time it is used, an exclusive latch must be held by the thread using it. This creates an incredible bottleneck and drastically affects performance.

 

이럴 때 DBCC CHECK 성능에 도움을 줄 수 있는 방법은 계산된 열에 사용된 비클러스터형 인덱스를 사용하지 않음으로 속도를 높일 수 있다.

 

인덱스 활성 / 비활성 방법 : http://sqlmvp.kr/140174227769

 

실습을 통해 확인해 보자.

 

테스트 데이터를 생성한다. 테스트 테이블에는 계산된 열을 가지고 포함한다.

USE AdventureWorks2008R2; GO   IF OBJECT_ID('Sales.SalesOrderHeaderEnlarged') IS NOT NULL     DROP TABLE Sales.SalesOrderHeaderEnlarged; GO   CREATE TABLE Sales.SalesOrderHeaderEnlarged     (     SalesOrderID int NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION,     RevisionNumber tinyint NOT NULL,     OrderDate datetime NOT NULL,     DueDate datetime NOT NULL,     ShipDate datetime NULL,     Status tinyint NOT NULL,     OnlineOrderFlag dbo.Flag NOT NULL,     SalesOrderNumber AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID],0),N'*** ERROR ***')),     PurchaseOrderNumber dbo.OrderNumber NULL,     AccountNumber dbo.AccountNumber NULL,     CustomerID int NOT NULL,     SalesPersonID int NULL,     TerritoryID int NULL,     BillToAddressID int NOT NULL,     ShipToAddressID int NOT NULL,     ShipMethodID int NOT NULL,     CreditCardID int NULL,     CreditCardApprovalCode varchar(15) NULL,     CurrencyRateID int NULL,     SubTotal money NOT NULL,     TaxAmt money NOT NULL,     Freight money NOT NULL,     TotalDue AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))),     Comment nvarchar(128) NULL,     rowguid uniqueidentifier NOT NULL ROWGUIDCOL,     ModifiedDate datetime NOT NULL     ) ON [PRIMARY] GO   SET IDENTITY_INSERT Sales.SalesOrderHeaderEnlarged ON GO INSERT INTO Sales.SalesOrderHeaderEnlarged (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate) SELECT SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate FROM Sales.SalesOrderHeader WITH (HOLDLOCK TABLOCKX) GO SET IDENTITY_INSERT Sales.SalesOrderHeaderEnlarged OFF   GO ALTER TABLE Sales.SalesOrderHeaderEnlarged ADD CONSTRAINT     PK_SalesOrderHeaderEnlarged_SalesOrderID PRIMARY KEY CLUSTERED     (     SalesOrderID     ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]   GO   CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeaderEnlarged_rowguid ON Sales.SalesOrderHeaderEnlarged     (     rowguid     ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO   CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeaderEnlarged_SalesOrderNumber ON Sales.SalesOrderHeaderEnlarged     (     SalesOrderNumber     ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO   CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderEnlarged_CustomerID ON Sales.SalesOrderHeaderEnlarged     (     CustomerID     ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO   CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderEnlarged_SalesPersonID ON Sales.SalesOrderHeaderEnlarged     (     SalesPersonID     ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO   IF OBJECT_ID('Sales.SalesOrderDetailEnlarged') IS NOT NULL     DROP TABLE Sales.SalesOrderDetailEnlarged; GO CREATE TABLE Sales.SalesOrderDetailEnlarged     (     SalesOrderID int NOT NULL,     SalesOrderDetailID int NOT NULL IDENTITY (1, 1),     CarrierTrackingNumber nvarchar(25) NULL,     OrderQty smallint NOT NULL,     ProductID int NOT NULL,     SpecialOfferID int NOT NULL,     UnitPrice money NOT NULL,     UnitPriceDiscount money NOT NULL,     LineTotal AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),     rowguid uniqueidentifier NOT NULL ROWGUIDCOL,     ModifiedDate datetime NOT NULL     ) ON [PRIMARY] GO   SET IDENTITY_INSERT Sales.SalesOrderDetailEnlarged ON GO INSERT INTO Sales.SalesOrderDetailEnlarged (SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate) SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate FROM Sales.SalesOrderDetail WITH (HOLDLOCK TABLOCKX) GO SET IDENTITY_INSERT Sales.SalesOrderDetailEnlarged OFF GO ALTER TABLE Sales.SalesOrderDetailEnlarged ADD CONSTRAINT     PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID PRIMARY KEY CLUSTERED     (     SalesOrderID,     SalesOrderDetailID     ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]   GO CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderDetailEnlarged_rowguid ON Sales.SalesOrderDetailEnlarged     (     rowguid     ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_SalesOrderDetailEnlarged_ProductID ON Sales.SalesOrderDetailEnlarged     (     ProductID     ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO     BEGIN TRANSACTION     DECLARE @TableVar TABLE (OrigSalesOrderID int, NewSalesOrderID int)   INSERT INTO Sales.SalesOrderHeaderEnlarged     (RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag,      PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID,      BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID,      CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment,      rowguid, ModifiedDate) OUTPUT inserted.Comment, inserted.SalesOrderID     INTO @TableVar SELECT RevisionNumber, DATEADD(dd, number, OrderDate) AS OrderDate,      DATEADD(dd, number, DueDate), DATEADD(dd, number, ShipDate),      Status, OnlineOrderFlag,      PurchaseOrderNumber,      AccountNumber,      CustomerID, SalesPersonID, TerritoryID, BillToAddressID,      ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode,      CurrencyRateID, SubTotal, TaxAmt, Freight, SalesOrderID,      NEWID(), DATEADD(dd, number, ModifiedDate) FROM Sales.SalesOrderHeader AS soh WITH (HOLDLOCK TABLOCKX) CROSS JOIN (         SELECT number         FROM (    SELECT TOP 10 number                 FROM master.dbo.spt_values                 WHERE type = N'P'                  AND number < 1000                 ORDER BY NEWID() DESC             UNION                 SELECT TOP 10 number                 FROM master.dbo.spt_values                 WHERE type = N'P'                  AND number < 1000                 ORDER BY NEWID() DESC             UNION                 SELECT TOP 10 number                 FROM master.dbo.spt_values                 WHERE type = N'P'                  AND number < 1000                 ORDER BY NEWID() DESC             UNION                 SELECT TOP 10 number                 FROM master.dbo.spt_values                 WHERE type = N'P'                  AND number < 1000                 ORDER BY NEWID() DESC          ) AS tab ) AS Randomizer ORDER BY OrderDate, number   INSERT INTO Sales.SalesOrderDetailEnlarged     (SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID,      SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate) SELECT     tv.NewSalesOrderID, CarrierTrackingNumber, OrderQty, ProductID,     SpecialOfferID, UnitPrice, UnitPriceDiscount, NEWID(), ModifiedDate FROM Sales.SalesOrderDetail AS sod JOIN @TableVar AS tv     ON sod.SalesOrderID = tv.OrigSalesOrderID ORDER BY sod.SalesOrderDetailID   COMMIT

 

 

 

테스트 테이블이 생성된 상태에서 DBCC CHECKDB를 실행. 수행시간을 확인 하였다.

dbcc checkdb ('AdventureWorks2008R2') with ALL_ERRORMSGS

 

 

 

다음 스크립트를 통하여 비클러스터를 포함한 계산된 열을 확인 하였다. 그리고 테스트 테이블의 인덱스를 사용하지 않도록 설정 하였다.

SELECT [s].[name], [o].[name], [i].[name], a.[name], [ic].* FROM sys.columns as a JOIN sys.index_columns [ic] ON [ic].[object_id] = a.[object_id] AND [ic].[column_id] = a.[column_id] JOIN sys.indexes [i] ON [i].[object_id] = [ic].[object_id] AND [i].[index_id] = [ic].[index_id] JOIN sys.objects [o] ON [i].[object_id] = [o].[object_id] JOIN sys.schemas [s] ON [o].[schema_id] = [s].[schema_id] WHERE a.[is_computed] = 1 GO   ALTER INDEX aLL ON Sales.SalesOrderHeaderEnlarged DISABLE GO

 

 

 

계산된 열의 인덱스를 사용하지 않도록 수정하여 DBCC CHECKDB를 실행. 처음보다는 빠르게 진행 된 것을 확인 할 수 있었다.

dbcc checkdb ('AdventureWorks2008R2') with ALL_ERRORMSGS

 

 

 

동일한 테스트를 반복 하였을 계산된 열에 비클러스터형 인덱스를 사용하여 DBCC CHECKDB를 사용한 경우 50초가 나왔으며 비클러스터형 인덱스를 사용하지 않음으로 설정 후 테스트 결과는 37초가 나타났다.

 

참고 자료 처럼 몇 십배 빨라지지는 효과는 볼 수 없엇지만 성능에 유리하다는 것을 확인 할 수 있었다.

 

내가 이해한 내용을 정리하면 DBCC CHECKDB는 기본적으로 개체를 병렬로 검사한다. 무결성 검증 하기 위해서 해쉬를 만들어서 두 개를 비교하는데 DBCC CHECKDB는 테이블에 비클러스터형 인덱스의 존재 유무와 이 인덱스 레코드 구성이 어떻게 매핑 되는지를 알고 있다. 하지만 계산된 열의 경우에는 매핑되는 해쉬값을 알기 위해 계산된 값이 필요 한데 이는 DBCC CHECKDB의 제어를 벗어나게 되며 계산된 열이 참조 하고 있는 열을 읽어 계산하여야 하기 때문에 이로 인한 병목이 발생하지 않을까 하는 생각이 든다.

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/dbcc-checkdb-performance-and-computed-column-indexes/

 

 

2013-03-27 / 강성욱 / http://sqlmvp.kr