wrong index name

  • Hi ,

    I have 2 databases 1)staging 2)prod. I am using following query to get index stats for rebuild. I will create that procedure in Prod database. it takes Database name as input and generate sql statements. it looks like following sql is not giving correct index names. either it skip some indexs (active one) or give name of index in other database. e,g. when I pass @DatabaseName = 'Staging' it is giving an index name that belongs to prod database. Do I need to male anyother join or I am missing or using incorrect tables ?

    SELECT i.[name],

    o.name,

    sch.name,

    s.avg_fragmentation_in_percent,

    CASE WHEN s.avg_fragmentation_in_percent > 30

    THEN 'Alter index [' + i.[name] +'] on ['+@DatabaseName+'].['+sch.name+'].[' + o.name +'] REBUILD WITH (ONLINE = OFF)'

    ELSE 'Alter index [' + i.[name] +'] on ['+@DatabaseName+'].['+sch.name+'].[' + o.name +'] REORGANIZE'

    END,

    row_number() OVER ( ORDER BY s.avg_fragmentation_in_percent DESC) ,

    0

    FROM sys.dm_db_index_physical_stats (DB_ID(@DatabaseName), NULL, NULL, NULL, NULL) AS s

    INNER JOIN sys.indexes AS i

    ON s.object_id = i.object_id

    AND s.index_id = i.index_id

    INNER JOIN sys.objects AS o

    ON i.object_id = o.object_id

    INNER JOIN sys.schemas AS sch ON o.schema_id=sch.schema_id

    WHERE (s.avg_fragmentation_in_percent > 15 )

    AND i.name is not null

    AND i.is_disabled = 0

     

  • all your sys.... tables are specific to the database you run this code on.

     

    do do what you need you either use full dynamic sql (where you add "use [@DatabaseName]" before running the code above , or you manually change to that db before executing the code.

     

    and more importantly - DO NOT use regorganize - and why reinvent the wheel? look at Ola Hallengren scripts for this type of things (https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html)

     

  • Or, don't rebuild the indexes at all. Find a good fill factor, set that, and leave them alone. Just maintain statistics.

    Different rules for columnstore indexes though.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Or, don't rebuild the indexes at all. Find a good fill factor, set that, and leave them alone. Just maintain statistics.

    Different rules for columnstore indexes though.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I STRONGLY second the notion that index maintenance on rowstore indexes is pretty useless if based on logical fragmentation.  I'll also tell you that it can cause major blocking on the proverbial "morning after" because you're using the generic supposed "Best Practices" methods, which were never meant to be considered a "Best Practice" and are actually a "Worst Practice" when used generically, especially with REORGANIZE in the mix.  It doesn't work the way you think it does and will actually do things to the indexes that actually cause fragmentation and perpetuate the problem of pages splits.

    The best thing to do is to rebuild statistics, if they need it.

    Watch the following 'tube (and stay until after the QnA for an interesting outtake) and understand that, despite the title, it's NOT just about GUIDs.  I just use those because they're the "Poster Child" for indexes that fragment.

    https://www.youtube.com/watch?v=rvZwMNJxqVo

     

     

    --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)

  • thank you All for valuable  directions. I will try to follow correct path as suggested.

    one more question about locking, OFFLINE rebuild lock table for upcoming transactions. What about current transactions, does it rollback any DML or wait for them to complete ?

  • thbaig wrote:

    thank you All for valuable  directions. I will try to follow correct path as suggested.

    one more question about locking, OFFLINE rebuild lock table for upcoming transactions. What about current transactions, does it rollback any DML or wait for them to complete ?

    Rebuilding indexes does not rollback anything.  Like everything else, it'll wait it's turn to lock the index/table.

    Just another warning... if you are rebuilding indexes that are fragmenting and they have a zero fill factor and the fragmentation is occurring mid-index, you're setting yourself up for MASSIVE blocking due to page splits on the proverbial morning after.  Like I've said before, it's better to do no index maintenance that it is to do it wrong. 😉

    --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 7 posts - 1 through 6 (of 6 total)

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