I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the experts in the field.
One of my questions is:
What does a best-practice SQL Server maintenance plan look like? Do you use the SQL Server maintenance plans for this, or do you use custom scripts?
You have to plan it with the business needs in mind. Sometimes it's a maintenance plan (like when a shop doesn't have full time DBAs or wants a minimum of complexity.) Other times it's Ola Hallengren's maintenance scripts. Whatever you do, it has to take into account the business needs for that data. I discussed it in my post Ozar's Hierarchy of Database Needs.
Setting up a maintenance plan is a more complicated than simply following a given "best practice". There's too much that plays into it all.
To give you an idea, Brad's Sure Guide to Maintenance Plans is a free ebook that's 269 pages long. If this were a simpler question, the book would be much, much shorter.
If I hit that question in an interview, for example, I would say that it completely depends on the resources the company has available, the business needs, the data inside the database, how often (and how) that data changes, etc. There's not one quick answer to the question, unfortunately.
There are several suggestions for maintenance tasks practices. Maintenance tasks are not applicable for all SQL Server environment scenarios
It's not recommended to use the Shrink Database task frequently, as it may lead to performance degradation and fragmentation of disks and indexes
Reorganize, update statistics, and rebuild tasks can also lead to issues on performance and CPU and hard disk usage overheads
You must regularly check the available disk space and remove obsolete files on time when using the full database backups
Schedule during off hours any database integrity check to avoid performance issues too
The scheduling, recommendations, and practices on maintenance tasks vary - they depend on a company policy, usage and SQL Server environment
