Database Reference
In-Depth Information
SQL Server reuses plans for ad-hoc queries only in cases where the queries are exactly the same and a complete
character-for-character match with each other. For example, the four queries from Listing 26-16 would introduce
three different plans. The first and second queries are identical and share the plan. The two other queries would
not reuse that plan due to the keywords' upper- and lowercase mismatch and the extra space characters around the
equality operator in the
where
clause.
Due to the nature of ad-hoc queries, they do not reuse plans very often. Unfortunately, cached plans for ad-hoc
queries can consume a large amount of memory. Let's look at an example and run 1,000 simple ad-hoc batches,
as shown in Listing 26-17, checking the plan cache state afterwards. The script clears the content of the cache with
the
DBCC FREEPROCCACHE
command.
Listing 26-17.
Ad-hoc queries memory usage: Running ad-hoc queries
dbcc freeproccache
go
declare
@SQL nvarchar(max)
,@I int = 0
while @I < 1000
begin
select @SQL =
N'declare @C int;select @C=ID from dbo.Employees where ID='
+ CONVERT(nvarchar(10),@I)
exec(@SQL)
select @I += 1
end
go
select
p.usecounts, p.cacheobjtype, p.objtype, p.size_in_bytes,
t.[text]
from
sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) t
where
p.cacheobjtype like 'Compiled Plan%' and
t.[text] like '%Employees%'
order by
p.objtype desc
option (recompile)
As you can see in Figure
26-9
, there are 1,000 plans cached, each of which uses 32KB of memory, or 32MB total.
As you can guess, ad-hoc queries in busy systems can lead to excessive plan cache memory usage.
