Changing DB_CHAIN Can Clear the Plan Cache
If you make changes to the settings of a database, it can cause the procedure cache to be cleared. Microsoft has documented changes that cause this for all procs within a database (scroll down to just above the examples). But guess what, if you change the DB_CHAINING option, it clears the cache too. Here’s a sample script to show it in action. ALTER DATABASE Testing SET DB_CHAINING OFF; GO CREATE PROCEDURE x AS SELECT * FROM test.dbo.A AS a2; GO CREATE PROCEDURE y AS SELECT * FROM dbo.Table_1 AS t; GO EXEC dbo.x; EXEC dbo.y; SELECT deqs.creation_time FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text LIKE 'CREATE PROCEDURE x%' OR dest.text LIKE 'CREATE PROCEDURE y%'; ALTER DATABASE Testing SET DB_CHAINING ON; SELECT deqs.creation_time FROM sys.dm_exec_query_stats AS deqs…