sqlserver 2008 rebuild all indexes based on fragmentation

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
bjenningsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
Matt BowlerDB team leadCommented:
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
 
PaulCommented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
bjenningsAuthor Commented:
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
 
Matt BowlerDB team leadCommented:
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
 
bjenningsAuthor Commented:
It worked great!   Thank you so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.