Index rebuild on sql server

Question

I am doing the index rebuilding on database. I need to verify if it is done or not. Can somebody please guide me. I am using SQL Server 2008 R2

Answer 1

If you are looking for details on all indexes and tables in your database you can use.

SELECT OBJECT_NAME(object_id),* 
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'SAMPLED')

It just occurred to me that you might also be asking how to know the progress of the reindexing. For this you can use

SELECT percent_complete 
from sys.dm_exec_requests 
where session_id= <spid of interest>

Answer 2

A key thing would be to run the "Index Physical Statistics" report and "Disk Usage by Top Tables" reports before and after you rebuild the indexes.

On the "Index Physical Statistics" report, you can see how fragmented each index is.

To see these reports... * Right Click on database in Sql Server Management Studio * Mouse over "Reports", then "Standard Reports", then select the report you want.

For a script you can set up to identify fragmented indexes and rebuild them (and for more info), check this out:

http://www.foliotek.com/devblog/sql-server-optimization-with-index-rebuilding/