bibi92
asked on
script to check tables and indexes fragmentation
Hello,
I search a script to check tables and indexes fragmentation on MS SQL SERVER 2005.
Thanks a lot
I search a script to check tables and indexes fragmentation on MS SQL SERVER 2005.
Thanks a lot
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I actually need to complete my post. The query will create a tempt table and then will select from it. Only indexes > 1000 pages will be checked, teh smaller ones are irrelevant.
As per Microsoft recommandation there are actions for different thresholds:
1. Frag < 10% - ignore
2. Frag <=10 and < 30 - reorganize
3. Frag >=30 - rebuild.
If you want to nicely automate these operations I recommend you Ola Hallegren's solution, from where my script was derived:
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
For a complete solution including backup, index optimization, integrity check use the whole solution that will create all the objects for you including the jobs:
www.http://ola.hallengren.com/
Another nice way to see this is the free app from Idera:
http://www.idera.com/productssolutions/freetools/sqlfragmentationanalyzer
If you want a collection of useful scripts, including Ola's fragmentation you can get using Red Gate's Script Manager, also free:
http://www.red-gate.com/products/dba/sql-scripts-manager/
As per Microsoft recommandation there are actions for different thresholds:
1. Frag < 10% - ignore
2. Frag <=10 and < 30 - reorganize
3. Frag >=30 - rebuild.
If you want to nicely automate these operations I recommend you Ola Hallegren's solution, from where my script was derived:
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
For a complete solution including backup, index optimization, integrity check use the whole solution that will create all the objects for you including the jobs:
www.http://ola.hallengren.com/
Another nice way to see this is the free app from Idera:
http://www.idera.com/productssolutions/freetools/sqlfragmentationanalyzer
If you want a collection of useful scripts, including Ola's fragmentation you can get using Red Gate's Script Manager, also free:
http://www.red-gate.com/products/dba/sql-scripts-manager/
ASKER
But how can I check data fragmentation?
Data fragmentation of a table is given by its clustered index fragmentation. If you don't have a clustered index on some of your tables (called heaps in this case) you should create them.
Open in new window
You can play around with the mode option to fit your needs (it will take longer to run depending on how accurate you would like your detail). Description of this DMF: http://technet.microsoft.c
Edit: I had just realized that index names would probably be useful... i'm not going to bother with changing it, since Zberteoc's is more than sufficient for what you wanted =)