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 
        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 OFF;

The script is almost self-explanatory. I want to point out that I put in one cross-database query to imply the possibility of cross-database ownership or access, but also to show that regardless of what’s referenced, all queries from this database are flushed from cache.

The first of the simple DMO queries returns two rows, the second returns no rows because everything is out of the cache because of the change to the database. It’s a little thing, but since it wasn’t explicitly stated in the Microsoft documentation, I thought I’d toss this out there.