Rebuild Indexes on SQL Server 2016

Vijay
Vijay used Ask the Experts™
on
Hi,

What is the fastest way to rebuild indexes on SQL Server 2016 Ent.? This is standalone server. no other applications are running.

We are planning to rebuild indexes on around 70 DBs, each DB contains 3 tables and each table having billions of rows.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
There's no miracle solution. You'll need to reindex index by index.
I recommend you to download Ola's maintenance script and run it in your SQL Server instance. It will create a reindex job and only thing you have to do is to run the job manually or schedule it to run during no activity period (usually night and weekends).
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
and amongst Ola's wonderful work are scripts to monitor the indexes so you minimize these major rebuild events :)

no points please
Absolutely the BEST way, Ola's scripts! The BIG advantage compared with the SQL Maintenance plan is that it will NOT do this fragmentation if it is not necessary! Disclaimer: I am not aware if this was introduced to versions greater than SQL 2012!
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Is it necessary recompile all Stored Procedures after index rebuild/reoranize?
IT Engineer
Distinguished Expert 2017
Commented:
Is it necessary recompile all Stored Procedures after index rebuild/reoranize?
No.

Author

Commented:
Good information.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial