How to check database index status ? whether database index requires rebuild or reorganize ?

Take connection to SQL Server and execute below query

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType,indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind  ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 and indexstats.page_count >1000 ORDER BY indexstats.avg_fragmentation_in_percent DESC

Above query will give result-set with % of fragmentation on what table and its Index-name.

Anything over 30% is bad but on a small table still would not have a major impact.

Microsoft guidelines is

5% and < = 30% ALTER INDEX REORGANIZE

30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

For reference click here

How to Fix it ?

Execute Below Query

For example to rebuild all the indexes on the SalesOrderDetail table, use the following statement:

ALTER INDEX ALL ON Sales.SalesOrderDetail REBUILD

To reorganize the index you can use below command.

ALTER INDEX ALL ON Sales.SalesOrderDetail REORGANIZE

Note : you can change table name as per your need.

For more interesting database related updates you can follow me on below link.

Facebook | Twitter | Google+ | Instagram | YouTube | LinkedIn | Tumblr | Wikipedia | Website (e.g. profile page on company site)