Solved

sqlserver 2008 rebuild all indexes based on fragmentation

Posted on 2013-06-11
6
547 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

839 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