13

DBCC FREEPROCCACHE doesn't work in Azure SQL DB. How else can I force a plan to kick itself out of the cache in a way that won't hurt a production system (i.e. I can't just go alter tables willy nilly)? This is specifically for SQL created by Entity Framework, so these aren't self-managed stored procs - it's effectively dynamic SQL.

(Source was bad indexes -> bad stats, etc. That's all fixed, but a bad plan won't go away.)

UPDATE: I selected @mrdenny's solution as he got there first. I am, however, successfully using @Aaron Bertrand's script to perform the work. Thanks to everybody for the help!!

9
  • Can you do an sp_recompile in Azure?
    – mrdenny
    Apr 10, 2013 at 20:13
  • Yes. What precisely would I run it on? We have no stored procs. This is dynamic SQL ran in sp_executesql.
    – Jaxidian
    Apr 10, 2013 at 20:16
  • 2
    You can run it on the table it self and that should flush the plans which use that table. (If this works I'll make it an answer.)
    – mrdenny
    Apr 10, 2013 at 20:18
  • 1
    I just tried this on a table and it apparently locks the table in a transaction while processing. I tried it on a 10-column table with only 24 records and it took over a minute to finish. During this time, I was unable to query the table. I can't run something like that on our real tables in Production!
    – Jaxidian
    Apr 10, 2013 at 20:43
  • 1
    Damn, that's a bummer. Looks like you'll need to do a schema change like adding a nullable column, then drop it. That'll wipe the cache as well and should be quick'ish. Testing will tell for sure.
    – mrdenny
    Apr 10, 2013 at 20:48

5 Answers 5

14

Azure SQL now directly supports this

Azure SQL Database directly supports clearing the proc cache of the current user database without any hacks:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Additional Information

The following script (by Shannon Gowen) can be used to watch the process step-by-step:

-- run this script against a user database, not master
-- count number of plans currently in cache
select count(*) from sys.dm_exec_cached_plans;

-- Executing this statement will clear the procedure cache in the current database, which means that all queries will have to recompile.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

-- count number of plans in cache now, after they were cleared from cache
select count(*) from sys.dm_exec_cached_plans;

-- list available plans
select * from sys.dm_exec_cached_plans;
5
  • I have not tried this yet but if this is in fact functional, then this is probably the "best" answer as of the beginning of 2017. Thanks for this - I had no clue this was there! :-)
    – Jaxidian
    Jan 12, 2017 at 22:29
  • I have tried this (on a Premium DB) and it worked. Mar 9, 2017 at 15:45
  • I have flagged this as an updated "Accepted Answer" but I have not yet tested this myself. I am basing this directly on Todd and Remi's feedback. Thanks all!
    – Jaxidian
    Mar 9, 2017 at 20:02
  • Just to revisit, I have used this and it worked well for me! I'm adding some additional scripts to Todd's answer here to enrich it but his post hit the nail on the head.
    – Jaxidian
    Apr 19, 2017 at 14:14
  • This doesn't seem to work for me - it just executes but the lists are still full - I'm on SQL Azure - what can be wrong?
    – Dirk Boer
    Feb 18, 2019 at 9:35
12

There isn't an explicit way to do this today, but that isn't a permanent scenario (the DBCC command is still not supported, but read up on Query Store). Even when the schema change hit is acceptable, it may not be what you want, because it will invalidate all plans related to the underlying object, not just the bad one.

Not looking for credit for this, but building dynamic SQL to perform the same operation against multiple tables is pretty easy:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER TABLE '
  + QUOTENAME(SCHEMA_NAME([schema_id])) 
  + '.' + QUOTENAME(name) + ' ADD fake_column INT NULL;
  ALTER TABLE ' 
  + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name) + ' DROP COLUMN fake_column;'
FROM sys.tables
--WHERE name IN, LIKE, etc.

PRINT @sql;

-- if the command > 8K, you can see the second chunk e.g.

PRINT SUBSTRING(@sql, 8001, 8000);

--EXEC sys.sp_executesql @sql;

(I wrote a tip about this "length of dynamic SQL" issue...)

6
  • In my case, removing them all is much preferable to leaving the bad ones in there. Thanks for the heads up. I know you can't tell me features but can you tell me when you might no longer be restricted about talking about things you can't talk about? ;-)
    – Jaxidian
    Apr 10, 2013 at 21:04
  • That is also classified, sorry. :-) Apr 10, 2013 at 21:06
  • Not sure what you mean by the link. What I meant is that your nvarchar(max) variable hits a limit after 4000 chars, 8000 chars if I change it to varchar(max). Running that exact script. We have ~450 tables, so we hit that easily (~30/60 tables in). varchar(max) is valid syntax, it's just identical to varchar(8000), and nvarchar(max) is identical to nvarchar(4000).
    – Jaxidian
    Apr 10, 2013 at 21:23
  • 3
    Well yeah, when you PRINT the command, it only shows 8000 bytes. That is a limitation of the PRINT command, not Azure. If you run the command it will work even if you can't visually inspect the whole thing. Apr 10, 2013 at 21:26
  • ... doh, sorry, I think you're right! Thanks for correcting me! That's what happens when your wife was expecting you to leave 25 minutes ago... ;-) This script works perfectly for me!
    – Jaxidian
    Apr 10, 2013 at 21:34
7

Add a nullable column to the table then drop the column. That'll force SQL to flush the cache for that object.

As for doing all the tables, a cursor should do the trick. Just use a column name that'll never exist in any table like 'zzzzzz_go_away' or something.

4

Azure SQL Database currently doesn't support DBCC FREEPROCCACHE, so you cannot manually remove an execution plan from the cache. However, if you make changes to a table or view referenced by the query (ALTER TABLE / ALTER VIEW) the plan will be removed from the cache. (Reference.)

4
  • I already knew everything you posted here. This is neither a stored procedure nor a view, so I cannot modify one of those. How could I modify my tables in an insignificant way, while under load and without causing any downtime or locking of the table, to trigger this?
    – Jaxidian
    Apr 10, 2013 at 20:39
  • 1
    You can possibly add a dummy column and then drop it. This will remove the plan from the cache. How big is the table ?
    – Kin Shah
    Apr 10, 2013 at 21:03
  • That ended up being the solution, as recommended by @mrdenny. Thanks for the help!! :-)
    – Jaxidian
    Apr 10, 2013 at 21:10
  • 1
    Thanks ...Just few secs short in time .. Was answering some other post on stackexchange ...
    – Kin Shah
    Apr 10, 2013 at 21:14
1

To clear all the execution plan, use this:

    SET NOCOUNT ON

DECLARE @lcl_name VARCHAR(100)
DECLARE @addcolumnSql nVARCHAR(MAX)
DECLARE @dropcolumnSql nVARCHAR(MAX)

DECLARE cur_name CURSOR FOR
SELECT name
FROM sysobjects
WHERE type = 'U'
OPEN cur_name
FETCH NEXT FROM cur_name INTO @lcl_name
WHILE @@Fetch_status = 0
BEGIN
set @addcolumnSql = 'alter table [' + @lcl_name + '] add temp_col_to_clear_exec_plan bit'
EXEcute sp_executesql @addcolumnSql
print @addcolumnSql
set @dropcolumnSql = 'alter table [' + @lcl_name + '] drop column temp_col_to_clear_exec_plan'
EXEcute sp_executesql @dropcolumnSql
print @dropcolumnSql
--  EXEC (@lcl_name )
FETCH NEXT FROM cur_name INTO @lcl_name
END
CLOSE cur_name
DEALLOCATE cur_name
SET NOCOUNT OFF

If you alter a table or view referencing it, the execution plan is cleared.

A bit more explained here http://christianarg.wordpress.com/2013/08/22/remove-execution-plans-from-the-procedure-cache-in-sql-azure/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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