3

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

Makach
  • 251
  • 1
  • 10
  • Only 60 MB? There are a number of ways to make such a small database perform poorly, but I'm guessing index fragmentation and outdated statistics aren't among them. You may want to spend some time tuning the application's queries, if possible, and also rule out lock contention. – db2 Mar 24 '11 at 14:11
  • @db2 yes, there is definitively room for optimizations in the queries. what triggered this question is that that the test system running against a restored backup of the live database is much much faster than the production database. – Makach Mar 24 '11 at 21:35

2 Answers2

3

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.

Kyle Brandt
  • 83,619
  • 74
  • 305
  • 448
0

Enterprise edition? Rebuild indeices without dropping the old ones first.

Cheaper edition: you can, but the indices will be dropped first. Can you deal with this?

TomTom
  • 51,649
  • 7
  • 54
  • 136
  • deal with this means system will be sluggish until rebuilt? – Makach Mar 24 '11 at 13:44
  • Depends. Note that until rebuild is more on the funny side here. a Database like that should possibly rebuild the index in seconds. 60 mb is a joke of a database, normally. I suggest the server is also very underpowered or something else is fishy. – TomTom Mar 24 '11 at 17:50