SQL Server Maintenance

Question

Hi All,

This is regarding the order of maintenance activity, I need to know the best and standard approach to do maintenance plan. Shall we follow below order in a sequence or do we need them in reverse? If so why.

1. Database Index Rebuild/Reorg

2. Database Integrity Check

Found below thread, but please note i need apart from backup here: 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9fd62168-a4e5-4e06-b1dc-5a7d9842de76/order-of-maintenance-program?forum=sqldisasterrecovery

Please advise, thanks in advance.

---

Regards, Pradyothana DP. Please Mark This As Helpful if it helps to solve your issue. ========================================================== https://social.technet.microsoft.com/Profile/pradyothanadp http://www.dbainhouse.blogspot.in/

Answer 1

I would recommend you that avoid using MP for those tacks but instead use Ola's script

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

PS. Have been using without problems for years

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

Thanks for reply, Yes we are using OLA Maintenance Solution. But during config and schedule which one we can do first, because after performing rebuild/reorg/update stats if we do the integrity check then we can be sure about the the health of the indexes.

---

Regards, Pradyothana DP. Please Mark This As Helpful if it helps to solve your issue. ========================================================== https://social.technet.microsoft.com/Profile/pradyothanadp http://www.dbainhouse.blogspot.in/

Answer 3

I fail to see the relevance for which to come first. Indexoptimize doesn't corrupt anything, problematic hardware and potentially bugs in SQL Server does. Do it in the order that feels good for you.

One could speculate that Doing indexoptimize first can make checkdb be quicker,in the sense that it will allow for more sequential /O (compared to random I/O). I doubt you will see a difference, with today's storage solutions.

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 4

Thanks for the reply, you mean to say once we run Index Optimize and then DBCC Integrity the IO performance for the job integrity check will be faster?

---

Regards, Pradyothana DP. Please Mark This As Helpful if it helps to solve your issue. ========================================================== https://social.technet.microsoft.com/Profile/pradyothanadp http://www.dbainhouse.blogspot.in/

Answer 5

I use rebuild/reorg/update stats  script  three times  a week and  and twice a  week dbcc integrity check 

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 6

There is no predefined order as a fact it would be very difficult to execute both on 24*7 system having 700+ GB of database simply because both will take lot of time and I doubt you can spare such downtime. It you have small database you can follow the what you have written on big database you can segregate it to different days, may Saturday index maintenance and Sunday checkdb 

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Answer 7

Theoretically: Yes. But as I said, there are many factors coming into play here. So whether you will actually see any practical difference, only you can tell by do it both ways and comparing the numbers.

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 8

Thanks to all for the prompt guidance. 

---

Regards, Pradyothana DP. Please Mark This As Helpful if it helps to solve your issue. ========================================================== https://social.technet.microsoft.com/Profile/pradyothanadp http://www.dbainhouse.blogspot.in/