T-SQL 一次性查询计划优化

如果内存不足，检查一次性查询计划缓存：

SELECT SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)'
FROM sys.dm_exec_cached_plans;
--总缓存          
SELECT objtype AS 'Type',
COUNT(*) AS '# Plans',
SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)',
AVG(usecounts) AS 'Avg uses'
FROM sys.dm_exec_cached_plans
WHERE objtype='adhoc'
GROUP BY objtype;
--临时性缓存
SELECT SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)'
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1;--一次性缓存

如果临时性缓存占总缓存比例高，一次性缓存占临时性缓存高，可以考虑：

SP_CONFIGURE 'show advanced options',1;
RECONFIGURE;
GO
SP_CONFIGURE 'optimize for ad hoc workloads',1;
RECONFIGURE;
GO
DBCC FREESYSTEMCACHE('SQL Plans');

适用SQL SERVER 2008，及以后版本。