데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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/

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1751 SQL Server 네트워크 백업 트러블슈팅(UNC 설정) jevida(강성욱) 2016.09.15 5164
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 3721
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1584
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 1714
» DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1435
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1310
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1587
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1239
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 3710
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1239
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1063
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1180
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1075
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1134
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1304
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1669
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1233
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1427
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1067
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 959





XE Login