April 28, 2021 at 9:12 pm
Hi all. I’m looking for some guidance on maintenance tasks in Azure SQL Managed Instance. I’ve been working with SQL Server on prem in VMs for years, but Azure is new territory for me. I’m quite happy to have Microsoft keep the server evergreen. And I’m starting to get used to the idea that they will be handling backups, but the rest of the maintenance stuff is what I’m concerned about. What I have found says that it’s not necessary to run DBCC CHECKDB because Microsoft is checking things behind the scenes and will raise an alert if there is an issue. That makes me a bit nervous, but I’ll go with it. The real question is, what about index and/or statistics maintenance? I’m seeing suggestions that it should be done and that it doesn’t need to be done. The information that I saw suggesting index maintenance should be done was about 6 years old when SSDs were not as prevalent as they are now. My bigger concern would be if I’m not doing index maintenance, should I be doing statistics maintenance or is that something that Azure has stepped up behind the scenes as well. I haven’t really found anything definitive yet and I’d love to hear your thoughts.
Thanks for your time.
April 29, 2021 at 3:02 pm
Hey Tom!
So, for Managed Instance, and for Azure SQL Database, while Microsoft is handling things like consistency checks and backups, you are responsible for what goes on inside the database. You'll need to plan for statistics maintenance, same as any other system. Index maintenance, well, there's a debate. I think most people are in concurrence that REORGANIZE is a waste of time. However, there's still discussion over how important, and how frequent, if at all, REBUILD has to occur. I'd simply say, in Azure, do what what you're comfortable with on the REBUILD side.
Hope that helps.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 29, 2021 at 4:53 pm
Hi Grant,
Thanks for the response. That sounds like a good course. I've seen several articles about how useless REORG is. I'll definitely skip that. I know that Ola has added statistics updates to his maintenance package so I'll probably go that route and setup index REBUILDs and statistics updates and skip the other jobs. I appreciate the input.
-Tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply