Can I Run a maintenance plan that does Index Maintenance anytime?
I've got a slow database right now and I have a index maintenance plan that is set to run every week. I did database update yesterday and today system is very sluggish.
Can I run this maintenance plan now, while there are people connected to the database
- rebuild index task
- update statistics task
the database is about ~60MB and right now there is about 10 users connected to it.
what is the proper procedure for running this maintenance plan? it is scheduled to run each sunday 02:00
Best Answer
The Enterprise version of SQL server includes the option for rebuilding index while they are live by using
OPTION ONLINE(ALTER INDEX REBUILD WITH (ONLINE = ON)) during rebuild, where as non-enterprise will require that you take the index offline to rebuild it.So best practice is to run rebuilds during off hours, even more so when you don't have online rebuilding.
Note that if the indexes are not too fragmented, you also may want to just reorganize instead of rebuild. See this msdn page for more details on this.