--測試如下(Windows2003+SQL2005 sp3)
USE
tempdb
go
IF
OBJECT_ID
(
'Test'
)
IS
NOT
NULL
DROP
TABLE
Test
SELECT
*
INTO
Test FROM
syscolumns
go
--step1:
DBCC
FREEPROCCACHE --
清空緩存中的執行計劃
--step2:(
執行3次
)
DECLARE
@TabName sysname
,
@ID INT
,
@SQL NVARCHAR
(
4000)
SET
@TabName=
'Test'
SET
@ID=
103--@ID{101,102,103} --
輸入次值，執行3次
SET
@SQL=
N'SELECT * FROM '
+
@TabName+
' WHERE ID='
+
RTRIM
(
@ID)+
' order by ID asc'
EXEC
(
@SQL)
--
查看產執行計劃
SELECT
dbName=
DB_NAME
(
dbID
)
,
Cacheobjtype,
objtype,
objid
,
sql
FROM
sys.syscacheobjects
WHERE
cacheobjtype=
'Compiled Plan'
AND
objtype IN(
'Adhoc'
,
'prepared'
)
AND
sql
LIKE
'%Test%'
AND
sql
NOT
LIKE
'%syscacheobjects%'
AND
sql
NOT
LIKE
'%msdb.%'
--
執行計劃產生3次
/*
dbName
Cacheobjtype
objtype
objid
sql
tempdb
Compiled
Plan
Adhoc
319724907
SELECT * FROM
Test WHERE ID=102 order by ID asc
tempdb
Compiled
Plan
Adhoc
513201771
SELECT * FROM
Test WHERE ID=103 order by ID asc
tempdb
Compiled
Plan
Prepared
475976984
(@1
tinyint)SELECT * FROM [Test] WHERE [ID]=@1 ORDER BY [ID] ASC
tempdb
Compiled
Plan
Adhoc
674221447
SELECT * FROM
Test WHERE ID=101 order by ID asc
*/
go
--step1:
DBCC
FREEPROCCACHE --
清空緩存中的執行計劃
--step2:(
執行三次
)
DECLARE
@TabName sysname
,
@ID INT
,
@SQL NVARCHAR
(
4000)
SET
@TabName=
'Test'
SET
@ID=
101--@ID{101,102,103} --
輸入次值，執行三次
SET
@SQL=
N'SELECT * FROM '
+
@TabName+
' WHERE ID=@ID order by ID
asc'
exec
sp_executesql
@stmt=
@SQL,
@Params=
N'@ID int'
,
@ID=
@ID
SELECT
dbName=
DB_NAME
(
dbID
)
,
Cacheobjtype,
objtype,
objid
,
sql
FROM
sys.syscacheobjects
WHERE
cacheobjtype=
'Compiled Plan'
AND
objtype IN(
'Adhoc'
,
'prepared'
)
AND
sql
LIKE
'%Test%'
AND
sql
NOT
LIKE
'%syscacheobjects%'
AND
sql
NOT
LIKE
'%msdb.%'
--
執行計劃產生1次
/*
dbName
Cacheobjtype
objtype
objid
sql
tempdb
Compiled
Plan
Prepared
421211796
(@ID int)SELECT
* FROM Test WHERE ID=@ID order by ID asc
*/