Index optimize

bibi92
bibi92 used Ask the Experts™
on
Hello,

I search a script for optimize index with this parameter

Execute ALTER INDEX..REORGANIZE when :
Avg_page_space_used_in percent <75 and >60 or
Avg_fragmentation_in_percent >10 and <15
Execute ALTER INDEX…REBUILD when :
Avg_page_space_used_in percent <60 or
Avg_fragmentation_in_percent >15

Thanks

Regards

Bibi
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Faiga DiegelSr Database Engineer

Commented:
What is your question?

Author

Commented:
Hello,

I search a script for defrag index with this parameter.

Thanks

Regards

Bibi
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
If avg_fragmentation_in_percent of sys.dm_db_index_physical_stats is less than 30 then Reorganize your Indexes else go for Index Rebuild..

>> I search a script for defrag index with this parameter.

Check out "Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes" in the link below:

http://msdn.microsoft.com/en-us/library/ms188917.aspx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial