Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

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
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PadawanDBA
PadawanDBA

This should give you what you're looking for:

select
	*
from
	sys.dm_db_index_physical_stats( db_id( N'<databaseNameHere>' ), null, null, null, null );

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.com/en-us/library/ms188917.aspx

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 =)
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/
Avatar of bibi92

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.