3월 30일 팀뷰강좌 스크립트
3월 30일 팀뷰강좌 스크립트
IF OBJECT_ID ('TBLX') IS NOT NULL
DROP TABLE TBLX
GO
-- 1000 개의 다른값 넣기
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) IDX
, CAST('A' AS CHAR(20)) COL1
, CAST('A' AS CHAR(50)) COL2
INTO TBLX
FROM SYS.OBJECTS A1
, SYS.OBJECTS A2
, SYS.OBJECTS A3
-- 같은값 1000개 넣기
INSERT INTO TBLX (IDX, COL1, COL2)
SELECT 1, COL1, COL2 FROM TBLX
-- 인덱스 만들기
CREATE CLUSTERED INDEX CL_TBLX ON TBLX (COL1)
CREATE NONCLUSTERED INDEX NC_TBLX_01 ON TBLX (IDX)
-- 프로시저 만들기
IF OBJECT_ID ('PARATEST') IS NULL
EXEC ('CREATE PROC PARATEST AS SELECT 1')
GO
DBCC SHOW_STATISTICS (TBLX, NC_TBLX_01)
SELECT 2000 * 0.001
-- 일반적인 테스트 (파라메터 스니핑 함)
ALTER PROC PARATEST (@PARA INT)
AS
SELECT DISTINCT IDX, COL1, COL2
FROM TBLX A
WHERE IDX = @PARA
GO
EXEC DBO.PARATEST 1
DBCC FREEPROCCACHE
EXEC DBO.PARATEST 2
-- 스니핑을 방지하는 방법 1
ALTER PROC PARATEST (@PARA INT)
AS
DECLARE @PARA_V INT = @PARA
SELECT DISTINCT IDX, COL1, COL2
FROM TBLX A
WHERE IDX = @PARA_V
GO
-- 스니핑을 방지하는 방법 2
ALTER PROC PARATEST (@PARA INT)
AS
SELECT DISTINCT IDX, COL1, COL2
FROM TBLX A
WHERE IDX = @PARA
OPTION (OPTIMIZE FOR (@PARA UNKNOWN))
GO
-- 스니핑을 방지하는 방법 3
ALTER PROC PARATEST (@PARA INT)
AS
SELECT DISTINCT IDX, COL1, COL2
FROM TBLX A
WHERE IDX = @PARA%@PARA+@PARA
GO
EXEC DBO.PARATEST 1
DBCC FREEPROCCACHE
EXEC DBO.PARATEST 2
-- 그래도 꼭 읽고 싶습니다.
ALTER PROC PARATEST (@PARA INT)
AS
SELECT DISTINCT IDX, COL1, COL2
FROM TBLX A
WHERE IDX = @PARA%@PARA+@PARA
OPTION (RECOMPILE)
GO
EXEC DBO.PARATEST 1
DBCC FREEPROCCACHE
EXEC DBO.PARATEST 2
-- 이건 또 안되요~
ALTER PROC PARATEST
(@PARA INT)
WITH RECOMPILE
AS
SELECT DISTINCT IDX, COL1, COL2
FROM TBLX A
WHERE IDX = @PARA%@PARA+@PARA
GO
EXEC DBO.PARATEST 1
DBCC FREEPROCCACHE
EXEC DBO.PARATEST 2
GO
-- 헉헉~ 끝~
그럼 오늘의 주제로 가봅시다.
IF OBJECT_ID ('TBLX') IS NOT NULL
DROP TABLE TBLX
GO
-- 1000 개의 다른값 넣기
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) IDX
, CAST(ABS(CHECKSUM(NEWID())) AS INT) % 100 COL1
, CAST('A' AS CHAR(50)) COL2
INTO TBLX
FROM SYS.OBJECTS A1
, SYS.OBJECTS A2
, SYS.OBJECTS A3
CREATE CLUSTERED INDEX CL_TBLX ON TBLX (COL2)
CREATE UNIQUE NONCLUSTERED INDEX NC_TBLX_01 ON TBLX (IDX)
SELECT DISTINCT IDX, COL1
FROM TBLX
DROP INDEX NC_TBLX_01 ON TBLX
SELECT DISTINCT IDX, COL1
FROM TBLX
-- 멘붕~ 생각하는 시간
SELECT DISTINCT IDX, COL1
FROM TBLX
OPTION
(
QUERYTRACEON 3604
, QUERYTRACEON 9292 -- 고려될 수 있는 대상 (HEADER 만 LOAD)
, QUERYTRACEON 9204 -- CARDINALITY DISTRIBUTION
, RECOMPILE
)
select * from sys.stats where object_id = object_id ('tblx')
dbcc show_statistics (tblx, _WA_Sys_00000002_25869641)
dbcc show_statistics (tblx, _WA_Sys_00000001_25869641)
-- 효과적으로 질문하기 위해서 이런 현상을 악의축에게 전달 하고자 할 때
-- 어떻게 해야 하는가? 쉬어~ 바바~