Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

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.

Comments

Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...