将 SQL Server 2012 中的参数化选项从简单更改为强制

它会释放您更改选项的数据库的计划缓存，而不是其他任何东西。

在我的服务器上，我运行这些查询：

USE StackOverflow;

SELECT TOP 1000 * FROM dbo.Users AS u WHERE u.Reputation = 2;

USE StackOverflow2010;

SELECT TOP 1000 * FROM dbo.Users AS u WHERE u.Reputation = 1;

然后我使用sp_BlitzCache查看我的计划缓存：

之后，更改一个数据库以使用强制参数化：

ALTER DATABASE StackOverflow SET PARAMETERIZATION FORCED

再次运行sp_BlitzCache ：

仅保留来自 StackOverflow2010 的计划。

全面披露：我为开源 sp_BlitzCache 存储过程贡献了很多主要工作代码。

编辑：用 Erics 评论重新测试。进一步的测试显示了预期的结果，但它没有遵守计划。
我们可以看到bucketID 4969更改DBAdmin数据库后计划被删除。

验证这一点的过程保持不变，但我将更新脚本以包含我的测试运行结果。

原帖： 我刚刚对其进行了测试，奇怪的是，从我所看到的情况来看，它符合计划。我将进一步审查这一点，但这是我到目前为止的测试。 我希望它能刷新结果。我会做更多测试并发布，但您可以按照此处的逻辑进行操作。

在非产品服务器上：

USE DBAdmin
GO
SELECT name, is_parameterization_forced FROM sys.databases
/*
name    is_parameterization_forced
master  0
tempdb  0
model   0
msdb    0
ReportServer$xxxx   0
ReportServer$xxxxTempDB 0
dbadmin_Central 0
DBAdmin 0
*/


DBCC FREEPROCCACHE
/* DBCC execution completed. If DBCC printed error messages, contact your system administrator. */
GO

SELECT * --plan_handle, query_plan, objtype   
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_query_plan(plan_handle)   
GO
--26904 2   1   57344   0x000001D8BE2C6060  Compiled Plan   Adhoc   0x06000800AF0A5E2F400A98DBD801000001000000000000000000000000000000000000000000000000000000

select top 10 * from dbadmin.dbo.Databases
GO
--(10 row(s) affected)


SELECT * --plan_handle, query_plan, objtype   
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_query_plan(plan_handle)  
GO
/*
34706   2   1   65536   0x000001D8C6D54060  Compiled Plan   Proc    0x0500050035F9F31A80D12FDBD801000001000000000000000000000000000000000000000000000000000000
29213   2   1   40960   0x000001D8C6D7C060  Compiled Plan   Proc    0x0500050029B1F43320CA2FDBD801000001000000000000000000000000000000000000000000000000000000
4969    2   1   57344   0x000001D8C6D66060  Compiled Plan   Adhoc   0x060008004ABF810AE0C82DBDD801000001000000000000000000000000000000000000000000000000000000
22197   2   1   40960   0x000001D8C414A060  Compiled Plan   Adhoc   0x0600080034126A2B60C12DBDD801000001000000000000000000000000000000000000000000000000000000
25346   2   3   253952  0x000001D8C4D9E060  Compiled Plan   Adhoc   0x06000500AACD810E401998DBD801000001000000000000000000000000000000000000000000000000000000
19925   2   3   147456  0x000001D8D15A8060  Compiled Plan   Adhoc   0x060005001BA31F37C01198DBD801000001000000000000000000000000000000000000000000000000000000
26904   2   1   57344   0x000001D8BE2C6060  Compiled Plan   Adhoc   0x06000800AF0A5E2F400A98DBD801000001000000000000000000000000000000000000000000000000000000
*/

ALTER DATABASE [DBAdmin] SET PARAMETERIZATION FORCED
GO



SELECT * --plan_handle, query_plan, objtype   
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_query_plan(plan_handle)   
GO
/*
34706   2   1   65536   0x000001D8C6D54060  Compiled Plan   Proc    0x0500050035F9F31A80D12FDBD801000001000000000000000000000000000000000000000000000000000000
29213   2   1   40960   0x000001D8C6D7C060  Compiled Plan   Proc    0x0500050029B1F43320CA2FDBD801000001000000000000000000000000000000000000000000000000000000
26904   2   1   57344   0x000001D8CFF82060  Compiled Plan   Adhoc   0x06000800AF0A5E2F400A98DBD801000001000000000000000000000000000000000000000000000000000000
25346   2   6   253952  0x000001D8C4D9E060  Compiled Plan   Adhoc   0x06000500AACD810E401998DBD801000001000000000000000000000000000000000000000000000000000000
19925   2   6   147456  0x000001D8D15A8060  Compiled Plan   Adhoc   0x060005001BA31F37C01198DBD801000001000000000000000000000000000000000000000000000000000000
*/
select top 10 * from dbadmin.dbo.Databases
GO
SELECT * --plan_handle, query_plan, objtype   
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_query_plan(plan_handle)  
/*
34706   2   1   65536   0x000001D8C6D54060  Compiled Plan   Proc    0x0500050035F9F31A80D12FDBD801000001000000000000000000000000000000000000000000000000000000
29213   2   1   40960   0x000001D8C6D7C060  Compiled Plan   Proc    0x0500050029B1F43320CA2FDBD801000001000000000000000000000000000000000000000000000000000000
4969    2   1   57344   0x000001D8C43D6060  Compiled Plan   Adhoc   0x060008004ABF810AE0D72DBDD801000001000000000000000000000000000000000000000000000000000000
32469   2   1   40960   0x000001D8CA498060  Compiled Plan   Adhoc   0x060008008F27113060D02DBDD801000001000000000000000000000000000000000000000000000000000000
30470   2   1   229376  0x000001D8C410A060  Compiled Plan   Adhoc   0x060001004765200BE0C82DBDD801000001000000000000000000000000000000000000000000000000000000
21758   2   1   327680  0x000001D8C4DF0060  Compiled Plan   Prepared    0x06000100962E9C1160C12DBDD801000001000000000000000000000000000000000000000000000000000000
26904   2   1   57344   0x000001D8CFF82060  Compiled Plan   Adhoc   0x06000800AF0A5E2F400A98DBD801000001000000000000000000000000000000000000000000000000000000
25346   2   7   253952  0x000001D8C4D9E060  Compiled Plan   Adhoc   0x06000500AACD810E401998DBD801000001000000000000000000000000000000000000000000000000000000
19925   2   7   147456  0x000001D8D15A8060  Compiled Plan   Adhoc   0x060005001BA31F37C01198DBD801000001000000000000000000000000000000000000000000000000000000
*/


USE DBAdmin
GO
SELECT name, is_parameterization_forced FROM sys.databases
/*
master  0
tempdb  0
model   0
msdb    0
ReportServer$xxxx   0
ReportServer$xxxxempDB  0
dbadmin_Central 0
DBAdmin 1
*/