/********************************************************************************************
-- Title : [2k5] EXEC와 SP_EXECUTESQL의 실행계획(캐시) 재사용 확인
-- Key word : exec sp_executesql 실행계획 재사용 cache 캐시
********************************************************************************************/
USE Tempdb;
GOCREATE TABLE ttt
(a INT NOT NULL PRIMARY KEY
, b INT NOT NULL);
GOINSERT INTO ttt
SELECT 1, 100 UNION
SELECT 2, 200 UNION
SELECT 3, 300 UNION
SELECT 4, 400 UNION
SELECT 5, 500;
GO
/* * * * * * * * * * * * * * * * * * * * *
-- EXEC 실행(매개변수 없음)
* * * * * * * * * * * * * * * * * * * * */
CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
DECLARE @i AS INT, @str AS NVARCHAR(200);
SET @i = 1;
WHILE @i <= 5
BEGIN
SET @str = N'SELECT * FROM ttt WHERE a = ' + CAST(@i AS NVARCHAR(1));
EXEC (@str);
SET @i = @i + 1;
END;
SELECT cacheobjtype, usecounts, objtype, sql FROM sys.syscacheobjects;
/*
Compiled Plan 1 Adhoc SELECT * FROM ttt WHERE a = 5
Compiled Plan 1 Adhoc SELECT * FROM ttt WHERE a = 4
Compiled Plan 1 Adhoc SELECT * FROM ttt WHERE a = 2
Compiled Plan 1 Adhoc SELECT * FROM ttt WHERE a = 3
Compiled Plan 1 Adhoc SELECT * FROM ttt WHERE a = 1
Compiled Plan 4 Prepared (@1 tinyint)SELECT * FROM [ttt] WHERE [a]=@1
*/
/* * * * * * * * * * * * * * * * * * * * * * * * *
-- SP_EXECUTESQL 실행(매개변수 없음)
* * * * * * * * * * * * * * * * * * * * * * * * */
CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
DECLARE @i AS INT, @str AS NVARCHAR(200);
SET @i = 1;
WHILE @i <= 5
BEGIN
SET @str = N'SELECT * FROM ttt WHERE a = ' + CAST(@i AS NVARCHAR(1));
EXEC SP_EXECUTESQL @str;
SET @i = @i + 1;
END;
SELECT cacheobjtype, usecounts, objtype, sql FROM sys.syscacheobjects;
/*
Extended Proc 5 Proc sp_executesql
Compiled Plan 1 Prepared SELECT * FROM ttt WHERE a = 5
Compiled Plan 1 Prepared SELECT * FROM ttt WHERE a = 4
Compiled Plan 1 Prepared SELECT * FROM ttt WHERE a = 2
Compiled Plan 1 Prepared SELECT * FROM ttt WHERE a = 3
Compiled Plan 1 Prepared SELECT * FROM ttt WHERE a = 1
Compiled Plan 4 Prepared (@1 tinyint)SELECT * FROM [ttt] WHERE [a]=@1
*/
/* * * * * * * * * * * * * * * * * * * * * * * * *
-- SP_EXECUTESQL 실행(매개변수 있음)
* * * * * * * * * * * * * * * * * * * * * * * * */
CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
DECLARE @i AS INT, @str AS NVARCHAR(200);
SET @i = 1;
WHILE @i <= 5
BEGIN
SET @str = N'SELECT * FROM ttt WHERE a = @iBar';
EXEC SP_EXECUTESQL @str, N'@iBar INT', @i;
SET @i = @i + 1;
END;
SELECT cacheobjtype, usecounts, objtype, sql FROM sys.syscacheobjects;
/*
Extended Proc 5 Proc sp_executesql
Compiled Plan 5 Prepared (@iBar INT)SELECT * FROM ttt WHERE a = @iBar
*/