Index rebuild on sql server

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 S开发者_如何学运维erver 2008 R2

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>

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/

If you have successfully re-indexed your tables, then the index fragmentation will be zero (or close to if you have hot tables). You can use this script to check the fragmentation level

DECLARE 
@IndexID int,
@TableID int,  
@IndexName varchar(256) 

--Enter index name here
SELECT @IndexName = '<index name>'
--Enter table name here
SET @TableID = OBJECT_ID('<table name>') 

SELECT @IndexID = IndID 
FROM sysindexes 
WHERE 
    id = @TableID 
    AND name = @IndexName 

DBCC SHOWCONTIG (@id, @IndexID)

What you are looking for in the output is the property called Scan Density. This should be close to 100%. If not, then your re-indexing is not complete/successful

If you have lots of tables/indices, this can get tedious, so short-circuit it by auto-generating the script like this:

SELECT 'DBCC SHOWCONTIG ' +
    '(' 
       + CONVERT(varchar(32), si.id) + ',' 
       + CONVERT(varchar(32), si.indid) + 
    ')--'  + so.name
FROM sysobjects so 
INNER JOIN sysindexes si 
ON (so.id = si.id) 
WHERE (
    so.type = 'U' AND
    si.indid < 2 AND
    si.id = object_id(so.name) 
    )

You can try this procedure. It will rebuild the index of all the tables in the database and print the result as it progresses to the message pane of your Management Studio:

CREATE PROCEDURE [dbo].[ReIndexDatabase]
AS
DECLARE @MyTable VARCHAR(255)
DECLARE myCursor

CURSOR FOR
SELECT table_name
FROM   information_schema.tables
WHERE  table_type = 'base table'

OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable

WHILE @@FETCH_STATUS = 0    BEGIN
  PRINT 'Reindexing Table:  ' + @MyTable
  EXEC('ALTER INDEX ALL ON '+@MyTable+'
  REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = OFF,
                STATISTICS_NORECOMPUTE = ON)');

  FETCH NEXT FROM myCursor INTO @MyTable
END

CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats

You can see this link for more on re-indexing or this link.

Note the information at the top of the page.