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)

-- 효과적으로 질문하기 위해서 이런 현상을 악의축에게 전달 하고자 할 때
-- 어떻게 해야 하는가? 쉬어~ 바바~