question

Phani404 avatar image
Phani404 asked

Index rebuild and reorganize

Hi All, My environment is on AlwaysOn 2012 with 1 (Sync) and 1 (Async) replicas and holding 200 GB to 1.5 TB databases. Having only DBCC and Updatestats jobs I don't know why my previous DBA did not configure index rebuild or reorganize jobs while upgrading SharePoint 2010 databases to SharePoint 2013. I am planning to configure them now. Can any one advise what precautions I can take before configuring them, as these jobs have not been set up in the last 1 year. If I configure through maintenance plan, does it consider average fragmentation (above 30% rebuild and below 5% reorganize) or rebuild / reorganize all indexes? Please advise. Thanks Phani Phani404@gmail.com
sql-server-2012indexrebuildalwaysonreorganize
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·

2 Answers

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I would use Ola Hallengrens scripts for index maintenance. It also does UPDATE STATISTICS for you, and is highly configurable. Just make sure you make all the jobs aware of which replica they are working on (you can't do index maintenance on the secondary replicas, only on the primary). That can be done by adding a job step to the agent jobs, which checks to see if the instance is currently acting as primary replica for the availability group, and if it isn't, exit the job. Then install the job(s) on every instance within the Availability Group. My experience with Maintenance Plans is not a very happy one. I don't know if it has been improved with newer versions of SQL Server, but back in SQL Server 2005 they were (to my opinion) more or less useless, due to the lack of configuration options. (And please don't refer to your Availability Groups as simply AlwaysOn. AlwaysOn is also Failover Cluster Instance. Use AlwaysOn Availability Groups or simply Availability Groups for clarity.)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
+1 for not using the inbuilt maintenance plans. They rely too much on the undocumented sp_msforeachdb, and there are other bugs in there In addition to Ola's scripts (at http://ola.hallengren.com), you might want to consider the new "MinionWare" scripts from the Midnight DBA team - http://minionware.net/
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.