October 10, 2007 at 12:34 am
Hi All,
I'm just a newbie for this technology and I would really need your input.
I would like to implement the re-indexing for all our databases together with the monthly server reboot. The monthly server reboot is initiated by our infra team and they want my assurance that the database reindexing would not cause any problem.
I've been re-indexing selective database without any problem for few months and appreciate if you could share with me what kind of problem that you have encountered so far based on your experience due to database re-indexing.
Thanks
October 10, 2007 at 10:03 am
Hi Larry,
I would say the biggest problem with reindexing a database is the CPU, IO and time cost. It is a pretty low risk command, but it definitely needs to be run off hours when no other commands are running. Aside from that, reindexing, when needed, is a very good thing. It keeps the IO and CPU costs of using an index to a minimum. Does your infrastructure team have specific concerns with doing a standard maintenance task?
Thanks,
Eric
October 10, 2007 at 9:40 pm
Hi Eric,
The infra intention is to have seperate exercise for the database reindexing and shall not be done together with our monthly server reboot. During the monthly server reboot, infra will install the microsoft patches and they can't afford to have delay or don't buy the idea that they shall wait until the reindexing activity completed.
My intention is to let the application users to do the functional testing after the patches installed and after I reindex the database.
If I require to schedule another downtime exclusively for reindexing purpose, shall I arrange with the application users to perform another functional test? Would you be able to share with me, what kind of activity or check list that DBA team should do after we perform the reindexing. I've pre-empted the infra team to do backup before the reindexing starts and standby for us, will this sufficient enough?
Thanks a lot
October 11, 2007 at 12:34 pm
I don't think it's necessary to schedule downtime and have user acceptance testing for reindexing. If you have a 24 x 7 application database, you may need explicit downtime, but you can always do index defrags as an online operation (i.e. it can be run without taking the app down). At any rate, you should be able to just find a low use period (weekends, early morning, etc.) to do this operation. I would treat it with the same risk as check db or full database backups. Neither are always good to run when the databases are active, but they can be run off-hours without taking the application down. Users like this because it means less down time! 😉
At any rate, I think you can set up a job that runs every day, or week, based on availability of your applications and how active your application is (update-wise). Let me know if you have any other questions.
Thanks,
Eric
October 11, 2007 at 1:52 pm
Hi Eric,
I managed to run the reindexing succesfully at night in our development box without a need to stop the application.
Thanks a lot for all the feedback and comments given on this case.
Cheers,
Lanny
October 11, 2007 at 2:36 pm
Not a problem, Lanny.
Thanks,
Eric
October 11, 2007 at 3:24 pm
If you're concerned about this, you might also consider only defragmenting/rebuilding the indexes which need it. Indexes which are very small in size or have little to no fragmentation might not really benefit from this. There are a few good scripts available to do this automatically for you (though admittedly it's MUCH easier in SQL 2005 with sys.dm_db_index_physical_stats()). If you search around you should be able to find them.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply