2

In Microsoft SQL Server, does DBCC TRACEON() invalidate cached execution plans, same as SP_Configure invalidates all stored procedure plans ?

2

sp_configure doesn't invalidate all stored procedure plans. sp_configure used to configure certain values which do cause plans to be invalidated. For example on my test system with nothing else running, and filtering just for cached procedure plans:

--Purposely empty all buffers and cache. DO NOT RUN ON PROD!!!!!!
dbcc dropcleanbuffers;
--Just run something to put something in the cache
exec sp_who;
exec sp_who2;
exec sp_configure;

select count(1) from sys.dm_exec_cached_plans where objtype='Proc';
--returns 14
EXEC sp_configure 'media retention', '3';
reconfigure with override;

select count(1) from sys.dm_exec_cached_plans where objtype='Proc';
--returns 14

--This assumes maxdop is at the default of 1
EXEC sp_configure 'max degree of parallelism', '1';
reconfigure with override;

select count(1) from sys.dm_exec_cached_plans where objtype='Proc';
--returns 0

Which is expected as it's only the last sp_configure usage that would invalidate an execution plans (other config value changes will do this as well).

DBCC may work the same way for some Trace flags, but having run through the same test using all of the ones I usually use I can't find any that do.

If you have a particular trace flag you want to use, and a test system, then this should be easy to test using something like this:

select count(1) from sys.dm_exec_cached_plans 
--this number of cached objects before turning on trace flag
dbcc traceon (3604,-1)
select count(1) from sys.dm_exec_cached_plans 
--If this number is lower, then something's been removed!
  • Is there any list of commands over the internet, which invalidate the cache plans ? – AA.SC Feb 19 '15 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.