| Author |
Topic |
|
poratips
Posting Yak Master
105 Posts |
 Posted - 2013-07-12 : 01:28:07
|
| I will be planning to do the Rebuild/ Reorg Indexes as my client never had done this and never run update statics.But my problem is that we have replication DB and it's runs very fw minutes as we are subscriber.If I do the Rebuild Index and it will lock the table during maintenance.We are running some evening and nightly jobs too.Replication DB is Big around 200 GB, will be a problem if i do the Index maintenance during week end and publication running?I have already ran the script to identify the Index fragmentation and know which index needs to be Rebuild or Reorg.Thanks, |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-07-12 : 09:30:26
|
| To prevent locking, you can use the online = on option when you issue the ALTER INDEX command.http://technet.microsoft.com/en-us/library/ms188388(v=sql.90).aspxReplication isn't really a concern. |
 |
|
|
poratips
Posting Yak Master
105 Posts |
Posted - 2013-07-12 : 17:05:58
|
| Thanks Russel. |
|
|
|
poratips
Posting Yak Master
105 Posts |
Posted - 2013-07-12 : 17:47:39
|
| We have sql 2005 and it's not a Enterprize edition so can't use ONLINE. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-07-15 : 08:06:59
|
| Then you'll want to rebuild indexes at off-peak hours. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-07-15 : 13:10:31
|
| I would suggest only running reorg. If you aren't experiencing a performance or space issue, then rebuild is basically a waste of downtime/time. Due to the huge headache that rebuild was causing, we just disabled the job and haven't needed to run it in many months. We disabled it at the advice of a very senior MS engineer. We do have Enterprise edition and were using ONLINE=ON, but it still was causing way too many production issues and really no benefit.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
poratips
Posting Yak Master
105 Posts |
Posted - 2013-07-17 : 17:22:19
|
| Thanks Russell.Thanks Tara.I am completely agreed with you but I believe my client is not running any Index Rebuild or ReOrg from long time and also they never run Update Stats so I thought let me run once a Rebuild script and Update the stat as we are experiencing long wait from User perspective and it's slowing whole process and normally it's happens when Replication runs or any batch job runs.Please advice, appreciate your help! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
poratips
Posting Yak Master
105 Posts |
Posted - 2013-07-18 : 14:13:12
|
| I noticed after running Index Rebuild and update stats (we ran only few Indexes), performance issue as use having slow performance, don't know why but it should improve the performance, right? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Index Maintenance during Replication