Currently Sql Azure does not support
As a normal SqlServer instace would. So how can we clear the execution plan cache if we suspect that we have a bad one cached or for whatever other reason?
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
What this basically does is add a temporaly bit column to each table on the database and then remove it (so we dont leave trash). Why do this? Because this we have a post in the official Sql Azure Team Blog that states that:
“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”
We must use a cursor because SqlAzure also does not support sp_MSforeachtable
I got the cursor code to loop all tables from this link (but I had to modify it ’cause it didn’t do anything in Sql Azure)