통계와 매개 변수 스니핑

-- 이 표에 통계가 무엇이 있는지 봅니다.
SELECT s.name AS 통계, s.auto_created AS 자동생성,
       sp.rows AS 통계가아는행수, sp.steps AS 구간,
       sp.modification_counter AS 바뀐행
FROM sys.stats s
    CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('Board.POSTS_BIG')
ORDER BY s.stats_id;

SELECT COUNT(*) FROM Board.POSTS_BIG WHERE num <= 100 OPTION (RECOMPILE);

DBCC SHOW_STATISTICS('Board.POSTS_BIG', 'IX_POSTS_BIG_user') WITH HISTOGRAM;
DBCC SHOW_STATISTICS('Board.POSTS_BIG', 'IX_POSTS_BIG_user') WITH DENSITY_VECTOR;

-- 자동 갱신을 잠시 꺼서 낡은 상태를 만듭니다.
ALTER DATABASE MssqlLab SET AUTO_UPDATE_STATISTICS OFF;
GO

INSERT INTO Board.POSTS_BIG (board_num, category_num, user_num, parent_num,
    group_num, depth, sort_no, title, content, hit_count, reg_date)
SELECT TOP (100000) board_num, category_num, 7, NULL,
    group_num, depth, sort_no, title, content, hit_count, reg_date
FROM Board.POSTS_BIG ORDER BY num;
GO

SET STATISTICS PROFILE ON;
SELECT COUNT(*) FROM Board.POSTS_BIG WHERE user_num = 7;
SET STATISTICS PROFILE OFF;

-- 하나만 다시 만듭니다.
UPDATE STATISTICS Board.POSTS_BIG IX_POSTS_BIG_user;

-- 표의 통계를 모두 다시 만듭니다.
UPDATE STATISTICS Board.POSTS_BIG;

-- 표본이 아니라 전부 읽어 만듭니다. 정확하지만 오래 걸립니다.
UPDATE STATISTICS Board.POSTS_BIG WITH FULLSCAN;

-- 데이터베이스 전체를 훑어 낡은 것만 다시 만듭니다.
EXEC sp_updatestats;

ALTER DATABASE MssqlLab SET AUTO_UPDATE_STATISTICS ON;
GO

-- 3만 행을 더 넣습니다. 임계(약 2만 4천)를 넘습니다.
-- 그리고 같은 문장을 다시 조회합니다.
SELECT COUNT(*) FROM Board.POSTS_BIG WHERE user_num = 8;
GO

-- 이번에는 계획을 새로 만들게 합니다.
SELECT COUNT(*) FROM Board.POSTS_BIG WHERE user_num = 8 OPTION (RECOMPILE);

CREATE OR ALTER PROCEDURE Board.P_UPTO
    @n int
AS
BEGIN
    SET NOCOUNT ON;
    -- 화면에 40만 행을 뿌리지 않으려고 변수에 담습니다. 읽는 양은 같습니다.
    DECLARE @s nvarchar(200);
    SELECT @s = title FROM Board.POSTS_BIG WHERE num <= @n;
END
GO

-- 캐시를 비우고 작은 값부터 부릅니다.
DBCC FREEPROCCACHE;
GO
SET STATISTICS IO ON;
EXEC Board.P_UPTO @n = 100;
EXEC Board.P_UPTO @n = 400000;

SET SHOWPLAN_TEXT ON;
GO
DECLARE @s nvarchar(200); SELECT @s = title FROM Board.POSTS_BIG WHERE num <= 100;
GO
DECLARE @s nvarchar(200); SELECT @s = title FROM Board.POSTS_BIG WHERE num <= 400000;
GO
SET SHOWPLAN_TEXT OFF;

-- 아래는 P_UPTO 안의 SELECT 를 바꿔 적은 것입니다.
-- 그대로 실행하지 말고 프로시저 본문에 넣어 견주십시오.

-- (1) 문장마다 새로 컴파일합니다.
SELECT @s = title FROM Board.POSTS_BIG WHERE num <= @n OPTION (RECOMPILE);

-- (2) 정해 둔 값으로 계획을 만듭니다.
SELECT @s = title FROM Board.POSTS_BIG WHERE num <= @n
OPTION (OPTIMIZE FOR (@n = 400000));

-- (3) 값을 안 본 것으로 치고 평균으로 만듭니다.
SELECT @s = title FROM Board.POSTS_BIG WHERE num <= @n
OPTION (OPTIMIZE FOR UNKNOWN);

-- (4) 지역 변수에 옮겨 담습니다. (3)과 같은 결과가 됩니다.
DECLARE @local int = @n;
SELECT @s = title FROM Board.POSTS_BIG WHERE num <= @local;

-- 같은 프로시저를 1,000번씩 부릅니다.
DECLARE @i int = 1, @t datetime2(7) = SYSDATETIME();
WHILE @i <= 1000 BEGIN EXEC Board.P_UPTO @n = 100; SET @i += 1; END
PRINT CONCAT(DATEDIFF(millisecond, @t, SYSDATETIME()), ' ms');