Rebuilding Indexes

  • I am running SQL Server Express and we have to perform the rebuilding of indexes manually via query and verify that the indexes have been rebuild.

    I first run this query to rebuild all indexes in every table of the database

    DECLARE @TableName VARCHAR(255)

    DECLARE @sql NVARCHAR(500)

    DECLARE TableCursor CURSOR FOR

    SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName

    FROM sys.tables

    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO @TableName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = 90', SORT_IN_TEMP = ON, ONLINE = OFF)'

    EXEC (@sql)

    FETCH NEXT FROM TableCursor INTO @TableName

    END

    CLOSE TableCursor

    DEALLOCATE TableCursor

    GO

    I then used the following script to verify that the indexes are being rebuild by checking the date modified of the statistics.

    create table ##stats

    (

    OBJECTID INT

    ,STATID INT

    ,LASTUPDATED VARCHAR (100) NULL

    ,NAME VARCHAR (100) NULL

    )

    DECLARE @OBJECTID INT

    DECLARE @STATID INT

    DECLARE TABLECURSOR CURSOR FOR

    SELECT OBJECT_ID, STATS_ID FROM SYS.STATS

    OPEN TABLECURSOR

    FETCH NEXT FROM TABLECURSOR INTO @OBJECTID, @STATID

    WHILE @@FETCH_STATUS = 0

    BEGIN

    INSERT INTO ##STATS

    SELECT SP.OBJECT_ID, SP.STATS_ID, LAST_UPDATED, SN.NAME FROM

    SYS.DM_DB_STATS_PROPERTIES(@OBJECTID,@STATID) SP

    JOIN SYS.STATS SN ON

    SN.OBJECT_ID = SP.OBJECT_ID AND

    SN.STATS_ID = SP.STATS_ID

    FETCH NEXT FROM TABLECURSOR INTO @OBJECTID, @STATID

    END

    CLOSE TABLECURSOR

    DEALLOCATE TABLECURSOR

    GO

    SELECT * FROM ##STATS ORDER BY LASTMODIFIED DESC

    DROP TABLE ##STATS

    Q: Why is it that when there is only one table in the database there are a few hundred statistics listed (is it because I am also seeing statistics for index views

    Q: Why are not all indexes updated.

    Q: How can we determine what indexes/statistics are part of the tables/columns and what are part of the indexed views.

    Any help is appreciated.

    Jeff

  • jayoub (6/26/2016)


    Q: Why is it that when there is only one table in the database there are a few hundred statistics listed (is it because I am also seeing statistics for index views

    Index views could certainly be a part of the problem. There's also a thing known as "Column Statistics" that are created when criteria isn't covered by an index.

    Q: Why are not all indexes updated.

    Because some indexes may be in mixed extents or the system decides that a table is too small to bother with.

    Q: How can we determine what indexes/statistics are part of the tables/columns and what are part of the indexed views.

    That's a bit more difficult but you can start by looking at the sys.indexes table and the sys.stats table (views, really). See "Books Online" for more information on those and related tables, views, DMVs. and system functions.

    Your turn, please. Why on this good green Earth do you have to prove that indexes were rebuilt? Also, what are you doing to keep from blowing out the log files and the MDF file during index rebuilds on larger tables?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • That is serious overkill. It's extremely unlikely that you need to rebuild all indexes every time. Look up Ola Hallengren's index maintenance script (or go to http://ola.hallengren.com/) and use that.

    You don't need to run them manually. While Express doesn't have SQL Agent, the OS it's installed on has the Windows Scheduler.

    Your script will rebuild all indexes, no matter what, but your 'check' script checks index and column statistics and column statistics aren't updated as part of an index rebuild, hence won't show a changed updated date

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you for the feedback

    I don't know why the team lead asks to verify. I think he just wants to know that the script work.

    Your help is appreciated

    Jeff

  • Thanks your help is appreciated

    Jeff

  • You're welcome but Gail (Gila Monster) is spot on. Rebuilding all indexes is a lot of overkill and causes a huge amount of logging unless you're in the BULK LOGGED or SIMPLE recovery model (if in SIMPLE, you have bigger problems).

    Also, just arbitrarily assigning a 90% Fill Factor can be a huge waste for anything that has a proper clustered index where the key is narrow, unique, immutable, and ever increasing. It means that you've wasted 10% of the space for all data older than right about now and have slowed your queries and caused them to use 10% more IO on their best days. It's especially important to NOT waste such space on SQL Express.

    Since the code rebuilds the indexes in an OFFLINE manner, the extended run time caused by rebuilding indexes that don't actually need it means that the underlying tables aren't as available as they could be if you only rebuilt indexes that needed it. AND, rebuilding of indexes uses a shedload more resources because any index over 128 extents (that's only 8MB) will be rebuilt and committed before the old index is dropped. For the clustered indexes on large tables, that can be a huge investment in disk space used/database size especially on something small like SQL Express.

    You should also look into selectively reorganizing indexes instead of doing carpet bombing by rebuilding every thing. As Gail, also pointed out, Ola Hallengren has a good backup system that's well documented and downloadable for free.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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