Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

DBCC FREEPROCCACHE doesn't work in SQL Azure. 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 me-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!!

share|improve this question
    
Can you do an sp_recompile in Azure? –  mrdenny Apr 10 '13 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 '13 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 '13 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 '13 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 '13 at 20:48
show 4 more comments

4 Answers 4

up vote 4 down vote accepted

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.

share|improve this answer
add comment

There isn't an explicit way to do this today, but that isn't a permanent scenario (can't reveal more due to NDA). 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 sp_executesql @sql;
share|improve this answer
    
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 '13 at 21:04
    
That is also classified, sorry. :-) –  Aaron Bertrand Apr 10 '13 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 '13 at 21:23
1  
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. –  Aaron Bertrand Apr 10 '13 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 '13 at 21:34
add comment

Removing Execution Plans from the Procedure Cache

SQL Azure currently doesn’t support DBCC FREEPROCCACHE (Transact-SQL), so you cannot manually remove an execution plan from the cache. However, if you make changes to the to a table or view referenced by the query (ALTER TABLE and ALTER VIEW) the plan will be removed from the cache. Ref: here

share|improve this answer
    
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 '13 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 Apr 10 '13 at 21:03
    
That ended up being the solution, as recommended by @mrdenny. Thanks for the help!! :-) –  Jaxidian Apr 10 '13 at 21:10
1  
Thanks ...Just few secs short in time .. Was answering some other post on stackexchange ... –  Kin Apr 10 '13 at 21:14
add comment

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/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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