Solved

sqlserver 2008 rebuild all indexes based on fragmentation

Posted on 2013-06-11
6
548 Views
Last Modified: 2013-06-18
Hello Everyone,

I have a sql 2008 database that has 400 indexes that over 30 percent fragmented.  Does anyone know of a way to rebuild only the indexes that are over 30 percent fragmented?  

Thanks,

Bill
0
Comment
Question by:bjennings
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39239843
There is no inbuilt way to do this with the ALTER INDEX ... REBUILD command so you need to code something.

If you query sys.dm_db_index_physical_stats() with average_fragmentation > 30 then send the results of this query to a table. You can then dynamically create the ALTER INDEX commands from the index names in this table and run the resulting statements...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39239914
here's some based on fragmentation (the last of these appears to be nicely simple):
http://stackoverflow.com/questions/10495367/automate-index-rebuild-based-on-fragmentation-results

this one doesn't take fragmentation into account, but may be a source of ideas for you, (i.e. the scope is broader than a single db):
http://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 39239964
Go here and get the script from SQL Server Index and Statistics Maintenance

It then becomes a very simple task of executing the script as follows:
EXECUTE dbo.IndexOptimize
@Databases = 'YourDatabase',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

Open in new window


Of course some of those parameters are Enterprise Edition only.  But you get the idea.
0
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

Author Comment

by:bjennings
ID: 39242130
Hello acperkins, Forgive me, I am a DBA novice....I have downloaded the script "MaintenanceSolution"...Do I run the script first or do I save it as a stored procedure.

Thanks,

Bill
0
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39242732
That is a great solution. If you run the MaintenanceSolution.sql script it will create the required stored procedures (and other objects) in the master database of the SQL instance.

You can change this easily if you want to create the objects elsewhere by finding the use [master] statement near the top of the script and changing to your desired database.
0
 

Author Closing Comment

by:bjennings
ID: 39257586
It worked great!   Thank you so much!
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question