Iterate and reorganize subset of database tables

  • Ahoi,

    we are using the ola hallengren scripts to run the weekly index optimizer.

    I was asked if i could write a script to run rebuilds of indexes for a subset of tables.

    Its not beatiful, but it seems to do the work in theory.

    This is the contents of the procedure i tried to make:

    • Create a cursor for relevant tables by going through information schema (todo but not import for this question is adding database)
    • Simple logging in a custom table (begin/end) (im aware of the flaw of it being ran multiple times a day, but the goal is to run it once a day)
    • Run ALTER INDEX ALL ON [prodmd01].[prodmd01].['+ @CURRENT_TABLE + '] REORGANIZE for all the cursor tables
    • Skip current table instead of killing the whole procedure if execution is killed due alter being killed as deadlock victim

    Questions:

    • Are there some inherant/critical flaws in the script, like

      • should i run COMMITS in between each while iteration
      • should i replace the alter statement with something more performant (already chose reorg over rebuild because daily)
      • .... ?

    I am asking because some of the alters take very long looking at my logging table. The ones that take very long are obviously those who have many indexes, rows, insert and updates (quel suprise). But i am assuming it might also be related to the way my procedure is executing the alter reorganize code.

    --#######################################################################
    --Get all "relevant" Table Names and Execute Rebuild/Reorganize
    --> optional: add Database and Schema to Cursor + REbuild SQL Code
    --> Todo: change information schema query to all relevant tables
    --> alter schema/databasename in alter of @sql_query
    --#######################################################################
    DECLARE @CURRENT_TABLE NVARCHAR(200) = ' '
    --DECLARE @CURRENT_DB NVARCHAR(200) = ''
    DECLARE @params nvarchar(4000);
    DECLARE @SQL_QUERY NVARCHAR(500);
    SET @params = N'@CURRENT_TABLE NVARCHAR(200)
    ,@CURRENT_DB NVARCHAR(200)'

    --List of Tables that need to be rebuild and placed into cursor
    DECLARE curs CURSOR
    FOR select TABLE_NAME--,Current_Database = DB_NAME()
    from INFORMATION_SCHEMA.TABLES
    where TABLE_TYPE != 'view'
    and (table_name like '&List_of_Search_terms%')

    OPEN curs;

    FETCH NEXT FROM curs INTO
    @CURRENT_TABLE;


    WHILE @@FETCH_STATUS = 0
    BEGIN
    --Try Catch, in case Deadlock on Table due to Access, skip table
    BEGIN TRY
    --Logging: Table + Startms
    SET @SQL_QUERY = 'insert into dbo.Index_Maintanance_Logging values('''+ @CURRENT_TABLE + ''',CURRENT_TIMESTAMP,NULL)'
    EXEC sp_executesql @SQL_QUERY

    --In every iteration execute query, redefine @sql variable, else it executes always the same
    --PRINT 'ALTER INDEX ALL ON [prodmd01].[prodmd01].['+ @CURRENT_TABLE + '] REORGANIZE' --REBUILD
    SET @SQL_QUERY = 'ALTER INDEX ALL ON [prodmd01].[prodmd01].['+ @CURRENT_TABLE + '] REORGANIZE' --REBUILD
    EXEC sp_executesql @SQL_QUERY

    --Annahme Job läuft nur ma ersten Tag, für den heutigen Tag, logge mir den Enzeitpunkt für: DATEDIFF(SECOND,StartTMS,EndTMS)
    update dbo.Index_Maintanance_Logging
    set EndTMS = CURRENT_TIMESTAMP
    where TableName = @CURRENT_TABLE
    and cast(StartTMS as date) = cast(CURRENT_TIMESTAMP as date)

    --GET Next Table for next iteration through Cursor
    FETCH NEXT FROM curs INTO @CURRENT_TABLE;
    END TRY
    BEGIN CATCH
    select 'Skip due to ERROR'
    END CATCH;
    END;

    CLOSE curs;

    DEALLOCATE curs;

    GO


    CREATE TABLE [dbo].[Index_Maintanance_Logging](
    [TableName] [nvarchar](100) NULL,
    [StartTMS] [datetime] NULL,
    [EndTMS] [datetime] NULL
    ) ON [PRIMARY]
    GO
  • I'll let Jeff do some chopping but WHY Reorganize? this should be avoided almost at all costs (very few exceptions)

  • frederico_fonseca wrote:

    I'll let Jeff do some chopping but WHY Reorganize? this should be avoided almost at all costs (very few exceptions)

    Not aware of that, also can't seem to find this opinion when on regular research of Rebuild vs Reorganize.

    But that is why i made this post, because i wanted to know if there are core issues, when executing the script.

  • have a look at the following links for info

    https://www.sqlservercentral.com/forums/topic/review-ola-hallengren-indexoptimize-parameters

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

    https://www.sqlservercentral.com/forums/topic/index-fragmentation-42

     

    regarding your particular need to do it on a subset of tables - don't worry about creating a custom script for it.

    Ola's script already allows you to do that by specifying the database and tables you wish the script to process.

    EXECUTE dbo.IndexOptimize
    @Databases = 'db1, db2, db3', -- or just 1 db
    @Indexes = 'db1.table1, db1.table2, db1.table3, db2.table1, db2.table2, db3.table2', -- specify the list of db/tables you wish to do independently of normal process. if required you can also specify the specific indexes per table db1.table1.index1
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REBUILD_OFFLINE', -- do not use the reorg options - and I avoid the rebuild online as it can be painfuly slow
    @FragmentationHigh = 'INDEX_REBUILD_OFFLINE', -- do not use the reorg options - and I avoid the rebuild online as it can be painfuly slow
    @FragmentationLevel1 = 5, -- change to a better value
    @FragmentationLevel2 = 30, -- change to a better value
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @LogToTable = 'Y'
  • Thanks for the reply.

    Weird that this is somehow not more public knowledge since the problem seems to be pretty impactful and somehow mentioned "no where" in most (popular) google results.

  • ktflash wrote:

    frederico_fonseca wrote:

    I'll let Jeff do some chopping but WHY Reorganize? this should be avoided almost at all costs (very few exceptions)

    Not aware of that, also can't seem to find this opinion when on regular research of Rebuild vs Reorganize.

    That's because of some poor wording in a couple of Microsoft documents a long time ago that caused 98% of the world to adopt supposed "Best Practices" that actually aren't and were never actually meant to be even closely considered to a "Best Practice" and a serious misread about what REORGANIZE actually does and a shedload of mis-information and flat out bad information that came out after that.

    I just gave (on GROUPBY.org on 24 May 2022) my latest version of the presentation that proves all of that using the proverbial "poster child" for fragmentation, Random Guides to test with.  It's not just about Random GUIDs, though.  I use those because if you can fix those, a lot of the techniques used can fix "normal" indexes.

    The video for that latest presentation I did on the subject should be a ready on YouTube in a few days.  They still have to render it out, insert and ad or two, and upload it.  I don't know exactly when it'll come out but watch for it at the link below.  If they don't post the zip file I included with it, you can ping me at the email address I provided in the video.

    This is where the video will appear.  You'll probably need to scroll through their playlist to find it.  Look for "Black Arts" Index Maintenance.

    https://www.youtube.com/channel/UCEHf_UKwG3GMkb9wIVBTeQA

    In the meantime, I recommend that you simply stop using Reorganize until you know what it's NOT doing and how it may actually be perpetuating fragmentation all day every day in your databases even if you don't have a single GUID in the entire server and how it can be and frequently is anything but "less resource intensive" than Rebuild.

     

    --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)
    Intro to Tally Tables and Functions

  • Left a subcription

  • Also, if you need something "right away", they do have a previous presentation of mine on the subject.  I have a couple of "mis-speaks" that I made during the "heat of a very fast presentation" but those are covered in the "errata" notes I made for the post.  There's also one small formula change where I forget to add the 2 bytes for the slot array position for calculating row length but, if that's not a concern (it actually isn't for most folks) and you don't mind having an "errata sheet", see the link for the presentation I made in the October of 2021 on Group_By below.

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

    I also have posted a warning there...  here's that warning.

    If you're listening with a headset, be aware that they added 3 advertisements at the 15:00, 30:15, and 45:25. The advertisements are important to help support this event but I wanted to let you know that they're sudden and they're loud! They WILL lift your headset!

    --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)
    Intro to Tally Tables and Functions

  • Ok i am confused, i was replacing my "alter index reorganize" part with the "execution  dbo.indexoptimize" within my script that iteratates through the for me relevant tables, job seems to be quickly running through, alot faster than the index previous reorganize.

    Is it intended that the index fragmentation is not affected by the execution of the the below, i tried different fragtmentation levels.

    Even when i run the script below with a static @CURRENT_DB_SCHEMA_Table for a certain table (instead of iteration through all the tables) ,  the index fragmentation of the table indexes is uneffected at 99%.

    Date and time: 2022-05-31 06:47:46
    Database: [prodmd01]
    State: ONLINE
    Standby: No
    Updateability: READ_WRITE
    User access: MULTI_USER
    Recovery model: FULL
    Is accessible: Yes

    Date and time: 2022-05-31 06:48:02
    Database context: [prodmd01]
    Command: UPDATE STATISTICS [prodmd01].[SEARCH] [PK__SEARCH__D6E6400EC8A7B454]
    Comment: ObjectType: Table, IndexType: Index, IndexType: Clustered, Incremental: N, RowCount: 119, ModificationCounter: 37
    Outcome: Succeeded
    Duration: 00:00:00
    Date and time: 2022-05-31 06:48:02

    Date and time: 2022-05-31 06:48:02
    Database context: [prodmd01]
    Command: UPDATE STATISTICS [prodmd01].[SEARCH] [b84344d60da464bb3d]
    Comment: ObjectType: Table, IndexType: Index, IndexType: NonClustered, Incremental: N, RowCount: 119, ModificationCounter: 37
    Outcome: Succeeded
    Duration: 00:00:00
    EXECUTE [MASTER].dbo.IndexOptimize
    @Databases = 'prodmd01', -- or just 1 db
    @Indexes = @CURRENT_DB_SCHEMA_Table, -- specify the list of db/tables you wish
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REBUILD_OFFLINE', -- do not use the reorg options - and I
    --@FragmentationHigh = 'INDEX_REBUILD_OFFLINE', -- do not use the reorg options - and I
    @FragmentationLevel1 = 10, -- change to a better value
    --@FragmentationLevel2 = 50, -- change to a better value
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @LogToTable = 'Y'
  • It is normal on the case of that table - it is a very small table and even if you rebuild it manually the fragmentation may not change - or if it changes it really will not have an impact on performance.

    One of the parameters for IndexOptimize  (omitted by default) has a default value of 1000 (see the code for the proc)

    @MinNumberOfPages int = 1000

    that index with just 119 records likely has less than 1k pages hence is not selected to be rebuild - and may not be an issue either.

    But if you do find its an issue then change the parameter to a lower value.

     

    (And I would suggest you get your developers to explicitly name the constraints on the tables - nothing worst than seeing a name like "PK__SEARCH__D6E6400EC8A7B454")

  • Referring to the following page, search for the number 128.  It turns out that's a threshold and its 128 extents.  Since an extent is 8 pages and there 128 of them, that's 1024.

    In some cases where I want to get into some small stuff that someone has whacked out, I'll sometimes go with as little as just > 8 pages (helps avoid mixed extents that won't defrag)  or > 128.

    Whenever I can, if the database is in FULL Recovery, I'll take a log file backup first, shift into the BULK LOGGED Recovery Model, and the shift back to FULL and take another log file backup to decrease the exposure of non-Point-in--Time restores.  That also serious helps the peformance of the REBUILDs and seriously decreases the log file usage.  And multi-threaded rebuilds rock... if you have the CPU's for it, Set MAXDOP to 8 for your REBUILDs.  That seems to be the "sweetspot" on my system (1 Numa node for me).  More or less makes things slower for me.  YMMV.

    Use sp_IndexDNA™ to look at your larger and more troubling indexes about a week after you do a rebuild and you'll, see that they might not be behaving like you think.  And "exploding broom-tip" is a sure sign of "ExpAnsive" updates in the hot-spot of ever-increasing indexes.  If you can't repair the "ExpAnsive" update issue, the rebuild them a 97% to flag them for what they are.

     

    --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)
    Intro to Tally Tables and Functions

  • ktflash wrote:

    Thanks for the reply.

    Weird that this is somehow not more public knowledge since the problem seems to be pretty impactful and somehow mentioned "no where" in most (popular) google results.

    Even MS didn't/doesn't understand, even though Paul Randal tried to tell them back when he first made the numbers up when MS held a gun to his head about 2 decades ago.   And, with that, he may also not know the full impact of what REORGANIZE actually doesn't do (it's one of the main reasonw why people think that Random GUIDs are bad).  MS finally got the message and changed the page that used to hold the 10/30 recommendation but they still don't get it about Reorganize.  Some folks had been talking a bit about it for a while but they didn't have really good proof.  I didn't develop sp_IndexDNA™ until early 2017 and started teaching it at SQL Saturday's but not many bought into it and those that did haven't been real verbal about it.

    I'm considering submitting a white paper to MS with a copy of sp_IndexDNA™ and a copy of the flick I posted the link to (with the errata sheet).

    I'm also slowly developing and testing an auto-magic way to identify what type of index an index is by pattern and using only the Fill Factor to identify the index.  Rowstore only for now, which is what most people have a majority of.

    --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)
    Intro to Tally Tables and Functions

  • This was removed by the editor as SPAM

  • HMHMHMH

    Job has been doing fine for the few days i used the dbo.IndexOptimize

    Now the job has been stuck for 4 hours on a single index rebuild while the Job previously took 30mins for the whole thing including all tables.

    Dumb me should have taken a look at why he was stuck before i killed the job, but since im not a DBA and i got yelled at why the system isnt running anymore i stopped the execution of the job, instead of checking what the alter/job currently does.

    Defenite lesson learned...

    According to the log in the dbo.CommandLog from OLLA Hallengreen, he was doing the following:

    ALTER INDEX [PK__EVENT__556FC09B9CFE71DE] ON [prodmd01].[EVENT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)

    • I know the job does different amount of work, since he only does rebuilds if the AVG/Index fragmentation threshhold is met, so its either that the job has not touched the index before
    • Or how does the REBUILD over there interact with action thats happening on the server? from what ive been told its a very active table even at night when the job is running (select, delete, update, insert), could it have been deadlocked somehow?

    Gonna keep an eye on that.

     

     

     

     

     

  • Why are using this setting: SORT_IN_TEMPDB = OFF?

    That's generally a very bad idea overall, esp. for larger indexes.

     

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 15 posts - 1 through 15 (of 19 total)

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