Database deployment is wiping off the plan cache

Question

Database deployment is wiping off the plan cache

Babji Talluri 26

Database deployment is flushing all the cached execution plans and causing slowness and performance issues in the application until its built up again. Trying to find options to retain the cache as-is or warm up the cache through some kind of job. Any recommendations will be appreciated,Thank you in advance!

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Alberto Morillo 35,506 MVP Volunteer Moderator

If your deployments make changes to a table or view (ALTER TABLE and ALTER VIEW) referenced by queries/stored procedures/functions then all plans related to those tables or views will be removed from the cache.

In addition, on Azure SQL the procedure plan cache is optimized for the benefit of the machine and not for an individual database. That means we have very little control over the procedure cache on an specific Azure SQL database, as the procedure cache is managed as a whole and there is no a portion of the memory pool set aside for each database. SQL Azure removes plans from the cache, regardless of the plan owner; all plans across all databases existing on the server are evaluated for removal.

My suggestion, at the end of your deployment you can a run a SQL Task to run a group of your top most commonly used stored procedures.

Babji Talluri 26 Reputation points

2022-06-07T18:00:01.283+00:00

That makes sense Alberto, Thank you for the answer. I was also exploring options to warm up the cache manually by running top executed SELECTS or hitting the GET API calls via a postman right after the deployment, do you think that's a possibility without any potential repercussion's on plan cache bloating, DML statements and the new database code that was deployed ? if so what would be right approach in achieving that.

Thanks again for your time!
Alberto Morillo 35,506 Reputation points MVP Volunteer Moderator

2022-06-07T21:31:42.887+00:00

Thank you for visiting Microsoft QA today and discuss with us this important topic.

My suggestion, at the end of your deployment you can a run a SQL Task to run a group of your top most commonly used stored procedures.

QUestion, Are you seen any schema wait like Sch-M when the stored procedure try to execute the first time or you are completely sure the poor performanceis related only to rebuild the procedure cache again.? If you see schema waits then try this ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;
Babji Talluri 26 Reputation points

2022-06-07T22:34:07.847+00:00

You bet Alberto! and thanks a lot for your time and patience. i will need to check if its safe to run the stored procedures as most of our business logic is in them.

I haven't monitored that granular, will do that after one of the upcoming deployments and change the configuration, We were inclined on cache because Users stop complaining slowness after couple of days. Now that you mentioned schema waits, I wonder how QDS is dealing with plan cache and deployments,Any thoughts there?
GeethaThatipatri-MSFT 29,597 Reputation points Microsoft Employee Moderator

2022-06-08T00:54:35.577+00:00

Hi, @Babji Talluri if you find the above reply useful. If yes, do click on 'Mark as answer' link in the above reply. This will help other community members facing similar queries to refer to this solution.

Regards
Geetha
Alberto Morillo 35,506 Reputation points MVP Volunteer Moderator

2022-06-08T01:11:17.88+00:00

I have not tested the impact of QDS really. Are you updating statistics regulary, full scan? If not that may explain things, and you should see the schema waits. If you are not doing it please update stats and defrag indexes regularly
Babji Talluri 26 Reputation points

2022-06-08T18:47:40.817+00:00

Hi Alberto,

We do update stats with full scan every night and also right after the deployment, we have been using this stored proc from blog https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-maintain-azure-sql-indexes-and-statistics/ba-p/368787. I haven't tested this yet but exploring a little bit into update stats invalidating plan cache and it sounds like update stats will not flush cache until the data is changed in the table, is that your experience as well? Thank you!

Regards,
Babji
Alberto Morillo 35,506 Reputation points MVP Volunteer Moderator

2022-06-08T19:02:14.247+00:00

The database engine sometimes request auto update of statistics before generating a query plan, and that may delay the generation of query plans. That is the reason I suggested AUTO_UPDATE_STATISTICS_ASYNC ON to speed up the creation of query plans.
Babji Talluri 26 Reputation points

2022-06-08T20:27:46.257+00:00

Sounds good Alberto,i will make that change and it would be great if you could let me know your findings on leveraging QDS to retain or warm up cache and also any experiences from your other Customers on how they are managing the performance after deployments. Thanks again,Much appreciated!
Alberto Morillo 35,506 Reputation points MVP Volunteer Moderator

2022-06-08T20:43:32.183+00:00

You are always welcome. Thank you for visiting Microsoft QA.

Share via

Database deployment is wiping off the plan cache

0 additional answers

Your answer