Database SQL Server, time for defragmentation

Keywords you will need to know: fragmentation/defragmentation
The DB server which I use is Microsoft SQL Server Enterprise Edition
SQL commands:

select * from sys.dm_db_index_physical_stats(DB_ID(ecommerce_vh10),NULL,NULL,NULL,NULL)

The above command will get you all the index values and descriptions in the database. But it shall give you only the object needed for verification of your indexes, but not the names. Now what you got to do is:

select top 100 i.object_id,,avg_fragmentation_in_percent,avg_page_space_used_in_percent from sys.indexes i join sys.dm_db_index_physical_stats(DB_ID(ecommerce_vh10),NULL,NULL,NULL,NULL) ips on (i.object_id=ips.object_id)


What this does is to join the indexes table and index description table. This will give you the index and index name. You will need to keep an on avg_fragmentation_in_percent which should be on the lower side. If its reaching 100% you got a problem there. This can happen when you have lot of insert,update,delete commands on the table and the space is not utilized properly.


Leave a Reply

Your email address will not be published. Required fields are marked *