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, ... ?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
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