SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Index Maintenance during Replication
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

poratips
Posting Yak Master

104 Posts

Posted - 07/12/2013 :  01:28:07  Show Profile  Reply with Quote
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

USA
5071 Posts

Posted - 07/12/2013 :  09:30:26  Show Profile  Visit russell's Homepage  Reply with Quote
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).aspx

Replication isn't really a concern.
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 07/12/2013 :  17:05:58  Show Profile  Reply with Quote
Thanks Russel.
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 07/12/2013 :  17:47:39  Show Profile  Reply with Quote
We have sql 2005 and it's not a Enterprize edition so can't use ONLINE.

Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 07/15/2013 :  08:06:59  Show Profile  Visit russell's Homepage  Reply with Quote
Then you'll want to rebuild indexes at off-peak hours.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 07/15/2013 :  13:10:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 07/17/2013 :  17:22:19  Show Profile  Reply with Quote
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!

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 07/17/2013 :  17:34:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
I would only update stats. Setup a nightly job to do it. Then go back and see if it resolved any issues. Rebuild/reorg are likely not going to help with any performance issues. If you have performance issues after update stats, you could try rebuild/reorg, however you'll likely find it didn't do anything and then will need to analyze code/indexes/execution plans/PerfMon/etc.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 07/18/2013 :  14:13:12  Show Profile  Reply with Quote
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?

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 07/18/2013 :  15:11:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well it depends why the performance is slow. Apparently it wasn't a stats or fragmentation issue. So now you've got to analyze other things, like I mentioned in my last post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Edited by - tkizer on 07/18/2013 15:14:08
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000