가상 인덱스 (Hypothetical Indexes)

가끔 튜닝을 하고자 할때 인덱스를 생성해서 미리 어떻게 동작하는지 알고 싶을 때가 있습니다.

보통은 테스트서버 or 로컬(개인PC)에서 데이터를 마이그레이션 하거나 일부러 더미 데이터를 넣어두고, 인덱스를 생성하여, 어떻게 실행계획이 동작하는지 테스트를 합니다. 

하지만 이 방법은 대단히 귀찮(?)습니다. 그래서 실제 서버에서 가상으로 인덱스를 만들어서, 실 데이터를 가지고 테스트를 진행 할 수 있는 방법을 소개하고자 합니다. 

하지만 이 방법은 통계를 이용하기 때문에 100% 정확하지는 않습니다. 하지만 실제 서비스에서 바로 결과를 확인 가능하며, 빠르게 할 수 있다는 이점이 있습니다.

이 인덱스는 Hypothetical Indexes 라고 부르며, Hypothetical는 가상의라는 뜻으로 나타납니다. 

이 인덱스의 장점과 단점은 다음과 같습니다.

• 장점 

1. 인덱스를 생성하는데 시간이 오래 걸리지 않음

2. 인덱스를 만들면서 실제 데이터를 건들이는 것이 아니기에 Live 중에 만들어도 상관 없음

3. 인덱스를 만들어도 공간을 차지하지 않음

• 단점

1. 실제 데이터를 정렬하거나 엑세스하지 않으므로, 통계 데이터를 가지고 동작하기에 100% 정확하지 않음

인덱스를 만드는 방법은 정말 간단합니다. 

만들때 WITH STATISTICS_ONLY만 넣고 플래그 값만 넣어주면 됩니다. 여기서 0은 계산없이 통계정보를 이용하여 생성하는 옵션이며, -1은 통계를 생성하여 인덱스를 만드는 옵션입니다.

The option STATISTICS_ONLY accepts two values: 0 to create an index without computed statistics; and -1 to generate statistics.

자세한 내용 전달을 위해서 위에 영문 그대로를 가져왔습니다.

저같은 경우 -1 플래그 값을 이용해봤고 0은 이용해 보지 않았습니다. 

CREATE INDEX MyIndex ON MyTable (MyColumn) WITH STATISTICS_ONLY = 0 

CREATE INDEX MyIndex ON MyTable (MyColumn) WITH STATISTICS_ONLY = -1 

위의 WITH STATISTICS_ONLY = -1 옵션은 실제로 인덱스를 생성하지는 않지만, 통계는 인덱스에 연결되게 만들라는 옵션 입니다. 

이 옵션으로 인덱스를 생성 하고 즉시 실행한다고 가상의 인덱스를 사용할 수 있는 것은 아닙니다. 

이 옵션을 사용하기 위해서는 문서화되지 않은 DBCC AUTOPILOT을 사용해야 합니다. 

이 옵션은 문서화되지 않았으며, 사용하기 위해서는 2588 추적 플래그를 실행해야 합니다. 이 옵션의 파라미터 값을 살펴보기 위해 쿼리를 날려 보겠습니다.

DBCC TRACEON (2588)
GO

DBCC HELP('AUTOPILOT')
GO

이 옵션은 다음의 파라미터를 받아서 동작 합니다. (위의 쿼리 실행 결과)

파라미터의 설명은 다음과 같습니다. (일부만 번역했습니다. 내용이 이해가지 않는 부분이 많으며, 실질적 사용 하는 부분은 매우 적습니다.

위에서 실제 사용하는 것은 (제가 테스트 할때는) 별로 없습니다. 아래의 테스트에서 확인 가능 합니다.

테스트 

Step 1 - 테이블 생성 

- 데이터베이스는 이미 있다고 가정 하고 테이블 만들기 부터 시작 합니다.

USE StudyDB
GO

CREATE TABLE Customers (
CustomerID  INT NOT NULL,
CustomerName  VARCHAR(50) NOT NULL,
CustomerAddress VARCHAR(50) NOT NULL,
[State]   CHAR(2) NOT NULL,
CustomerCategoryID CHAR(1) NOT NULL,
LastBuyDate  DATETIME,
PRIMARY KEY CLUSTERED (CustomerID)
)
GO

CREATE TABLE CustomerCategory (
CustomerCategoryID CHAR(1) NOT NULL,
CategoryDescription VARCHAR(50) NOT NULL
)
GO

Step 2 - 데이터 삽입

- Customers 테이블과 CustomerCategory 테이블 2개의 테이블에 들어가는 데이터입니다.

USE StudyDB
GO

INSERT INTO [dbo].[Customers] ([CustomerID],
                               [CustomerName],
                               [CustomerAddress],
                               [State],
                               [CustomerCategoryID],
                               [LastBuyDate])
SELECT   1,
        'Nicole Franco',
        '78 Fabien Freeway',
        'AZ',
        'C',
        '2013-05-27 02:46:20'

UNION ALL
SELECT  2,
        'Pablo Terry',
        '29 West Milton St.',
        'DE',
        'A',
        '2013-12-27 14:52:57'
UNION ALL
SELECT  3,
        'Desiree Lambert',
        '271 Fabien Parkway',
        'NY',
        'C',
        '2013-01-13 21:44:21'

UNION ALL
SELECT  4,
        'Chadwick Stephenson',
        '444 North Rocky Milton Avenue',
        'NH',
        'A',
        '2013-04-03 09:55:17'

UNION ALL
SELECT  5,
        'Daphne Gilbert',
        '97 Hague Blvd.',
        'VT',
        'B',
        '2014-01-15 20:55:35'

UNION ALL
SELECT  6,
        'Sonya Mosley',
        '60 West Fabien Avenue',
        'AZ',
        'A',
        '2014-01-23 01:38:20'

UNION ALL
SELECT  7,
        'Katina Browning',
        '835 Cowley Street',
        'NH',
        'B',
        '2014-04-08 21:10:51'

UNION ALL
SELECT  8,
        'Kari Boyd',
        '35 West First St.',
        'ND',
        'C',
        '2013-11-23 21:46:33'

UNION ALL
SELECT   9,
        'Seth Mc Bride',
        '912 First Freeway',
        'AK',
        'B',
        '2013-05-17 12:06:45'

UNION ALL
SELECT   10,
        'Bernard Knight',
        '33 Nobel Blvd.',
        'NY',
        'B',
        '2013-12-14 19:04:15'
GO


INSERT [dbo].[CustomerCategory] ([CustomerCategoryID], [CategoryDescription])
 VALUES (N'A', N'Elite')
INSERT [dbo].[CustomerCategory] ([CustomerCategoryID], [CategoryDescription])
 VALUES (N'B', N'Special')
INSERT [dbo].[CustomerCategory] ([CustomerCategoryID], [CategoryDescription])
 VALUES (N'C', N'Regular')
 GO

Step 3 - 인덱스 생성 

- 가상의 인덱스를 생성해 줍니다. SQL SERVER 2012 에서 만들때 WITH STATISTICS_ONLY 옵션이 오류라고 붉은 실선이 표시되지만 만들면 잘 만들어 지니 당황하지 마시길...

- 인덱스 옵션에서 WITH STATISTICS_ONLY = 0 으로 인덱스를 생성하지만 예제를 위해서 만들었고, 저는 자세한 내용을 몰라서 테스트 하지 않았습니다.

 USE StudyDB
 GO

CREATE INDEX IX_CustomerCategoryID_NO_STATS 
    ON dbo.CustomerCategory (CustomerCategoryID) WITH STATISTICS_ONLY = 0  
GO

CREATE INDEX IX_CustomerCategoryID_STATS  
    ON dbo.CustomerCategory (CustomerCategoryID) WITH STATISTICS_ONLY = -1
GO

CREATE CLUSTERED INDEX IX_CustomerCategoryID_CLUSTERED  
    ON dbo.CustomerCategory (CustomerCategoryID) WITH STATISTICS_ONLY = -1
GO

Step 4 - 인덱스 정보 확인 및 페이지 확인

- 위에서 말씀 드렸 듯이 인덱스의 정보에 따라 타입 값이 변경되며, 몇가지 파라미터 값을 넣기위해 정보가 필요합니다.

USE StudyDB
GO

exec sp_helpindex CustomerCategory
GO

위의 쿼리 실행 결과 입니다. 

DBCC에 넣을 정보를 검색하여 찾습니다.

SELECT  object_id ,
   OBJECT_NAME(object_id) AS 'Table' ,
        name ,
        index_id ,
        type ,
        type_desc ,
        is_unique ,
        is_hypothetical 
    FROM sys.indexes 
    WHERE object_id in (object_id('CustomerCategory'), object_id('Customers'))

위의 쿼리 실행 결과 입니다.

Step 5 - 쿼리의 실행결과를 XML 형태로 받아 볼수 있도록 옵션을 켭니다.

Step 6 - 이제 가상의 인덱스를 사용하여, 인덱스가 어떻게 바뀌는지 확인 합니다.

- 실제 실행계획과 가상의 인덱스를 이용한 실행계획을 비교 합니다.

우선 첫번째로 실제 실행 계획입니다. 현재 Customers 테이블에 CustomersID로 PK가 걸려있어서 PK를 이용하여 Index Scan을 탑니다.

두번째로 가상의 인덱스를 이용하여, 실행계획을 확인해 보겠습니다. 우선 우리가 3개의 인덱스를 만들었는데, Clustered 인덱스를 이용하여 실행계획입니다.

DBCC AUTOPILOT에서 Type 6은 Clusetered 인덱스를 뜻 합니다.

DBCC AUTOPILOT (5, 9, 0, 0, 0)
DBCC AUTOPILOT (6,9,1877581727,2)
GO 

SET AUTOPILOT ON
GO
 
SELECT  CustomerID ,
        CustomerName ,
        CustomerAddress ,
        State ,
        C.CustomerCategoryID ,
        LastBuyDate ,
        CC.CustomerCategoryID ,
        CategoryDescription FROM dbo.Customers C 
INNER JOIN dbo.CustomerCategory CC 
    ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE State = 'NY' 
GO

SET AUTOPILOT OFF
GO

위에서 보면 CustomerCategory 에 인덱스가 전혀 없었으므로,(실제인덱스) Table Scan을 했는데 이번에는 Seek를 하였습니다. 이것은 우리가 만든 가상의 인덱스를 정상적으로 타고 있음을 보여줍니다.

그 다음으로 Non Clusetered 인덱스를 태워서 실행계획을 확인해 보겠습니다.

DBCC AUTOPILOT (5, 9, 0, 0, 0)
DBCC AUTOPILOT (0,9,1877581727,2)
GO 

SET AUTOPILOT ON
GO
 
SELECT  CustomerID ,
        CustomerName ,
        CustomerAddress ,
        State ,
        C.CustomerCategoryID ,
        LastBuyDate ,
        CC.CustomerCategoryID ,
        CategoryDescription FROM dbo.Customers C 
INNER JOIN dbo.CustomerCategory CC 
    ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE State = 'NY' 
GO

SET AUTOPILOT OFF
GO

이번에는 실행계획이 바뀌었음을 알수 있습니다. 우리가 만든 넌 클러스터 인덱스를 잘 탓습니다. 

몇가지 궁금증

가상 인덱스의 인덱스 정보를 다른 인덱스와 비교하여 확인 가능 한가?

- sp_help 의 결과 입니다. 인덱스가 있다고 나타납니다. 그렇다면 sys.indexes DMV에서는 어떨까요?

실제 인덱스를 만들고, 비교해보면 is_hypothetical 컬럼의 bit 값이 다르게 표시됨을 알 수 있습니다.

CREATE NONCLUSTERED INDEX IX_CustomerCategoryID_IND	ON CustomerCategory(CustomerCategoryID)
GO

select T.name as Tbl_name
	   ,I.name as Ind_name
	   ,I.type_desc as Type
	   ,I.is_hypothetical
from sys.tables as T
inner join  sys.indexes as I
on T.object_id = I.object_id
where T.name = 'CustomerCategory'
GO

감사합니다.

참조 

https://www.mssqltips.com/sqlservertip/3246/sql-server-performance-tuning-with-hypothetical-indexes/

https://www.simple-talk.com/sql/database-administration/hypothetical-indexes-on-sql-server/