1

I want to rebuild my indexes and statistics on DB.

How can I select this option on production maintenance.

SQL Server Version : Microsoft SQL Server 2012 - 11.0.2100.60 (X64) (Build 7601: Service Pack 1)

  • Which RDBMS do you use? – erhun Mar 26 '15 at 9:41
  • @erhun Microsoft SQL Server 2012 - 11.0.2100.60 (X64) (Build 7601: Service Pack 1) – Oguzhan Mar 26 '15 at 9:43
  • This blogpost contains the scripts and maintainance plan mssqltips.com/sqlservertip/1367/…, it will be useful for you. – erhun Mar 26 '15 at 9:48
  • 2
    Consider using Ola Hallengren's maintance scripts: ola.hallengren.com. These are easy to use and powerful. – Dan Guzman Mar 26 '15 at 12:41
  • 2
    I agree with @dan. For the "accidental DBA", Ola's scripts are a godsend. Heck, even for me (16 year professional), I still go to them first before I ever consider creating my own... or worse yet, relying on MS's built in maintenance plans <<shudder>>. – Chris Woods Mar 26 '15 at 12:54
1

I would prefer to rebuild the indexes where the fragmentation percent of the indexes is greater than 30% and a Re-org of indexes where fragmentation percent is in between 9% and 30%.

Coming to update stats, I would prefer to do it with Full Scan depending on the size of the databases.

While these are resource intensive database operations, I would prefer to do these operations over the weekends or after business hours.

For Production Environments, I would usually setup jobs and allow them to run over the weekend.

Please go through the below link for maintenance plans(very useful) and customize accordingly to your environment.

https://ola.hallengren.com/

| improve this answer | |
0

I would go with detailed if it finishes in a timely basis. I run a check index statistics script every night and if it can rebuild within the time allocated for low activity I would go with detailed because the resulting statistics seem to produce better query plans.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.