1

I have created some non-clustered index on my tables in SQL Server 2014.

Is there any way to Rebuild (and Reorganize) the indexes automatically? I mean is it possible to schedule them to be built daily, weekly, ... ?

  • 3
    These are very popular maintenance scripts that help you define what fragmentation threshold you should use. He has several scripts but here's the docs for the index maintenance one: ola.hallengren.com/… – Ali Razeghi Aug 21 '16 at 8:01
1

I have read (and re-read your question) - Your question is "Is there any way to Rebuild (and Reorganize) the indexes automatically? I mean is it possible to schedule them to be built daily, weekly, ... ?"

The answer is yes. You have several options.

You can choose to set up a Sql Maintenance plan (but I would not recommend this as you have very little control over what gets rebuilt/reorgd). The maintenance plan allows you to specify the scheduling criteria (daily, monthly, whatever) and generate a Sql Server job.

A better solution would be to use a customized script like the one that @Ali Razeghi mentioned (Ola's). This script (and others available) let you configure options regarding when to rebuild vs reorg and only do index maintenance on the tables 'that need it'. You can create a custom Sql Server agent job to run the custom script whenever you need to. If you don't want to use the Sql Agent, you can also use the Windows Task Scheduler to invoke a SQLCMD bat file process to run the index maintenance scripts.

Regardless of whether you choose the maint plan route or custom sql agent job, make sure you alter the 'notification' properties of the job to notify interested parties in the event of a failure. You'll need to create one or more Sql Operators for the notification to send error emails to. It's also wise to go into the job step properties (advanced) and check the box to include the step output in history or even log the output to a table. Including the step output in history may not show you everything because there is a limitation as to how much information can be stored. Saving the output to table has no limitation (that I'm aware of)

Index maintenance is an intense IO and CPU process and should be scheduled during non-peak-usage times

0

Is there any way to Rebuild (and Reorganize) the indexes automatically?

No right from SQL Server 7.0 to SQL Server 2016 there is no feature to rebuild index automatically. The simple reason which comes to my mind is rebuild should be done only when index is fragmented. SQL Server internally does not keeps in any system table the fragmentation value so how it is going to decide what criteria to select when rebuilding index, like rebuild when fragmentation is >30, ignore small indexes etc.

If you are talking about scheduling you have SQL Server agent which can schedule the script to run at particular time, you can also use windows scheduler to run batch script

  • Thanks Shanky :) I also changed the Question Title as you suggested – Nasseh Aug 21 '16 at 6:13
  • Well, based on the "current" title of the question, the answer would actually be yes - it's called a sql agent job (either generated by a maint plan or custom configured) or even Windows scheduler. Perhaps the question should be "Does Sql Server automatically rebuild or reorg indexes on its own?" Then, the answer would be no- and you probably wouldn't want it to do it anyway – Scott Hodgin Aug 21 '16 at 13:31
  • @ScottHodgin I guess OP has made it clear that he is not interested in SQL Server agent schedule he has written * I mean is it possible to schedule them to be built daily, weekly, ... ?* – Shanky Aug 21 '16 at 13:34
  • @Shanky - I did not see where the OP said they weren't interested in using sql agent - the sql agent can run jobs on any number of schedules - daily, weekly, monthly, quarterly, annually- take your pick - I still think the question is ambiguous – Scott Hodgin Aug 21 '16 at 13:39
  • @scottHodgin What part of the statement I mean is it possible to schedule them to be built daily, weekly, ... ? is not clear to you. He means, he knows he can schedule it daily, weekly, quarterly whatever. The question is perfectly clear. The only thing he has not used is name of SQL agent which does not adds any ambiguity when OP knows about scheduling. – Shanky Aug 21 '16 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.