테스트 1
테이블을 생성하고 바로 인덱스를 생성하고 데이터를 입력할 경우

SET NOCOUNT ON
GO

-- 테스트 테이블 생성
IF OBJECT_ID('Table1') IS NOT NULL
    DROP TABLE Table1
GO

CREATE TABLE Table1
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Filed1 CHAR(50)
)
GO

-- 인덱스 생성
CREATE INDEX IDX_Table1 ON Table1 (Filed1)
GO

-- 테스트 데이터 입력
DECLARE @i INT
SET @i = 1
WHILE @i < 1000
BEGIN
    INSERT INTO Table1 VALUES (LTRIM(STR(@i)))
    SET @i = @i + 1
END
GO

-- Table1 테이블의 IDX_Table1 인덱스의 통계정보 확인
DBCC SHOW_STATISTICS(Table1, IDX_Table1)
GO




통계 정보가 없다.

-- 데이터 SELECT
SELECT * FROM Table1 WHERE Filed1 = '1'

-- Table1 테이블의 IDX_Table1 인덱스의 통계정보 확인
DBCC SHOW_STATISTICS(Table1, IDX_Table1)
GO




테이블을 생성하고 바로 인덱스를 만들고 데이터를 입력할 경우
통계 정보가 생성되는 시점은
인덱스가 걸린 컬럼의 데이터를 엑세스(SELECT, UPDATE, DELETE) 할 때인 듯하다.



테스트 2
테이블을 생성하고 데이터를 입력한 후 인덱스를 생성할 경우


-- 테스트 테이블 생성
IF OBJECT_ID('Table1') IS NOT NULL
    DROP TABLE Table1
GO

CREATE TABLE Table1
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Filed1 CHAR(50)
)
GO

-- 테스트 데이터 입력
DECLARE @i INT
SET @i = 1
WHILE @i < 1000
BEGIN
    INSERT INTO Table1 VALUES (LTRIM(STR(@i)))
    SET @i = @i + 1
END
GO

-- 인덱스 생성
CREATE INDEX IDX_Table1 ON Table1 (Filed1)
GO

-- Table1 테이블의 IDX_Table1 인덱스의 통계정보 확인
DBCC SHOW_STATISTICS(Table1, IDX_Table1)
GO




테이블을 생성하고 데이터를 입력하고 인덱스를 생성할때
통계 정보가 생성되는 시점은
인덱스를 생성할 때 데이터가 있음으로 인덱스 생성 시점에 통계 정보가 생성된다.

조건

분포

>, >=, <, <=

30%

=

10%

Between

25%

조건

분포

>, >=, <, <=

30%

=

All Density[%]

Between

9%

-- 테스트 테이블test 생성/테스트 데이터 입력
IF OBJECT_ID('test') IS not null
     DROP TABLE test ;
GO

WITH temp1(num )
AS
(
    SELECT 1 num
    UNION all
    SELECT num + 1 FROM temp1
    WHERE num + 1 <= 5000
),
temp2(num )
AS
(
    SELECT 1 num
    UNION all
    SELECT num + 1 FROM temp2
    WHERE num + 1 <= 600
)
SELECT
      IDENTITY(INT,1,1) seq
    , CONVERT(CHAR(8), GETDATE() + b.num, 112) dt
    , LEFT(newid(), 12) dumy1 --데이터를 채워넣기 위함
    , newid() dumy2 --데이터를 채워넣기 위함
    INTO test
FROM temp1 a, temp2 b
OPTION (maxrecursion 0 );

--인덱스 생성
CREATE CLUSTERED INDEX cix_seq ON test(seq );

CREATE INDEX nix_dt ON test(dt );



테스트 1 ( <=, 30% )

SET STATISTICS IO ON

select min(dt), count(*) from test






--실행 계획 비교
-- 쿼리 1
DECLARE @dt CHAR(8 )
SET @dt = '20090418'

SELECT *
FROM test
WHERE dt <= @dt

-- 쿼리2
SELECT *
FROM test
WHERE dt <= '20090418'


쿼리1/2의 실행 계획이 서로 다르다.




쿼리1의 Clustered Index Scan 정보



실제 행 수는 5000 인데 예상 행 수는 900000 이다.

미확정적인 매개 변수의 <= 검색에 대해서 옵티마이저가 사용하는 통계 정보는 전체 데이터의 30%
(위에 보면 표로 나와 있다)

전체 데이터 행 수는 3000000
3000000의 30% 는 90000
이제 이해가 된다. ㅡㅡ);



테스트2 (=All Density)

DECLARE @dt CHAR(8 )
SET @dt = '20090418'

SELECT *
FROM test
WHERE dt = @dt

쿼리의 Index Seek 정보


실제 행수와 예상 행 수가 같다.

--인덱스 통계정보 확인
DBCC SHOW_STATISTICS ('dbo.test', 'nix_dt');




dt 컬럼의 선택도는 0.001666667 전체 데이터 3000000 * 0.001666667 = 5000.001000000
예상 행수가 5000 건이 나온다 .




테스트 3 (Between 9%)

--테스트 테이블 생성/데이터 입력
SELECT DISTINCT * INTO test2
FROM test

--인덱스 생성
CREATE INDEX nix_dt
ON test2(dt )
Go

--쿼리 1
DECLARE @dt CHAR(8 )
DECLARE @dt2 CHAR(8 )
SET @dt = '20090417'
SET @dt2 = '20090418'

SELECT *
FROM test2
WHERE dt between @dt and @dt2

--쿼리2 (인덱스 힌트를 줌 )
SELECT *
FROM test2 WITH(INDEX=nix_dt )
WHERE dt between @dt and @dt2


쿼리1/2의 실행 계획


위의 실행 계획을 보면 쿼리2의 비용이 쿼리1에 비해 월등히 높다.


그런데 프로필러로 두 쿼리를 실행 시켜 보면..


위에서처럼 쿼리2의 성능이 더 좋다.
주의해야 한다.


Test2 테이블의 전체 데이터 수는
select count(*) from test2


3000000 건!!



쿼리 1의 실행계획 Table Scan을 보면


예상행수는 270000, 실제 행수는 5000
3000000 * 0.09 = 270000
왜 예상행수가 270000 이 나왔는지 알 수 있다.


쿼리2의 실행계획 Index Seek를 보면


똑같다..