Rebuild Indexes

  • I would like to rebuild indexes.Will the tables be automatically available for use available once the rebuild completes or do I have to do anything to make them available?I am thinking of creating a maintenance plan in MS SQL server management studio to rebuild indexes.I have come across 2 different thoughts regarding rebuilding indexes and updating statistics .One thought says updating statistics is not necessary after rebuilding statistics as a rebuild automatically updates statistics and another thought says we need to update statistics.Under what circumstances do these 2 different thoughts hold true?

    I ran this query:Select

    DB_NAME(ips.database_id) DBname,ips.object_id,

    OBJECT_NAME(ips.object_id) ObjName,

    i.name InxName,

    ips.avg_fragmentation_in_percent

    FROM sys.dm_db_index_physical_stats(db_id('Ntier_NWHMC'),

    default, default, default, default) ips

    INNER JOIN sys.indexes i

    ONips.index_id = i.index_id AND

    ips.object_id = i.object_id

    WHERE

    ips.object_id > 99 AND

    ips.avg_fragmentation_in_percent >= 10

    --ips.index_id > 0

    order by ips.avg_fragmentation_in_percent desc

    which gave the list of object names with their fragmentation.But when I actually see their fragmentation levels in Management studio it says a different story.The results hardly match with a wide variation

    Ex:Query says 99% fragmented on the objects but the management studio says 2%.

    Which one should I trust?And what should I do to make these 2 match in the database?

    Thanks

  • Use this script and the results will match ..

    SELECT

    db_name() AS DbName,

    SCHEMA_NAME(B.schema_id) AS SchemaName

    , B.name AS TableName

    , C.name AS IndexName

    , A.INDEX_DEPTH as Index_depth

    , C.fill_factor AS IndexFillFactor

    , D.rows AS RowsCount

    , A.avg_fragmentation_in_percent

    , A.page_count

    , GetDate() as [TimeStamp]

    FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A

    INNER JOIN sys.objects B

    ON A.object_id = B.object_id

    INNER JOIN sys.indexes C

    ON B.object_id = C.object_id AND A.index_id = C.index_id

    INNER JOIN sys.partitions D

    ON B.object_id = D.object_id AND A.index_id = D.index_id

    WHERE C.index_id > 0 and A.INDEX_DEPTH >2

    ORDER BY A.avg_fragmentation_in_percent DESC

    Tables will be automatically available for use once the rebuild completes. No extra steps needed.

    Instead of using Maintenance Plans try to use Index Maintenance solution from Ola Hallengren which is customizable and gives you more control and better logging.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    It depends on your environment. Even though Rebuild Index will update statistics it doesn't update NonIndex-column statistics. May be if you can update statistics on a nightly basis you need not do it immediately after the index rebuilds.

    --

    SQLBuddy

  • Yes, when you rebuild an index the table/index becomes available as soon as the index rebuild is completed.

    Statistics based on the index rebuilt are updated when an index rebuild occurs, but SQL Server also allows you to create statistics on non-indexed columns and, if left at the default, will automatically create statistics on columns the optimizer thinks will help. These column level statistics are NOT updated when an index is rebuilt thus you need to update them.

    I recommend using Ola Hallengren's maintenance scripts[/url] for this type of work. Michelle Ufford also has a good index maintenance script [/url]as well.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply