bjennings
asked on
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
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
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/
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/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks,
Bill
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.
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.
ASKER
It worked great! Thank you so much!
If you query sys.dm_db_index_physical_s