Azure Managed Instance Maintenance

Question

Azure Managed Instance Maintenance

Tom Uellner

SSCrazy

Points: 2954
More actions
April 28, 2021 at 9:12 pm

#3877069

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Tom Uellner SSCrazy Points: 2954 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

I realize this thread is a couple of years old now but, I have to ask the question, can either of you share some links to articles where someone states that Reorganize is basically useless? Thanks for the help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

The one I've been basing my own advice from is years old, by Brad McGehee. Frankly, I did a search, and I can't find it. That stinks, because lots of people still think REORGANIZE does positive things other than burn up CPU & I/O (outside Columnstore, have to always put that caveat in there).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

Thanks, Grant. I remember you speaking of Brad's input on the subject before. I've not found it, yet, either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 6

Tom Uellner ,

If you're still around, do you have any links on the subject at hand?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tom Uellner SSCrazy Points: 2954 More actions · Answer 7

Hi Jeff,

I'm still around. I've been digging through stuff trying to find links on REORG and failing badly. Perhaps more coffee will help. Even if it doesn't it's still more coffee. 🙂

-Tom