Enhanced Index Rebuild Script

  • Comments posted to this topic are about the item Enhanced Index Rebuild Script

    Lead level SQL DBA currently working for a well established Insurance organisation in London.

  • Took me a while to get the database and filegroup references worked out and up and running. Not the worst code I've ever seen but very close to it. Commas at the beginning of lines makes my skin crawl. I'd be embarrassed to have my name on this in a corporate setting. Breaking out the table creates might make it easier for newbies. Seems to run quickly in non-execute mode. Later I'll dive into the log tables and actual results to see if any magic is going on.


    I've encountered an error for my indexes named 128 characters long. QuoteName returns nvarchar(258) but your table columns are nvarchar(128). I simply removed QuoteName calls since I never use weird names.

    Also, I run the process and minutes later it wants to Reorganize a 1000 indexes again. What is going on?

    Reorg seems to not work on indexes with less than 300 pages.

    Rebuild seems to not work on indexes with less than 32 pages.

    I'm just going to force Rebuild all the time and rip out the reorg coding.

    The @ver variable check is inconsistent throughout and obviously an error.

    The deprecated != is used throughout.

    I question the fill factor 80% since that lowers your data cache density. I'd probably run at 95% if reindexing weekly.

    After two days of working on this proc, I'm realizing what a hunk of junk it was.

    I've found more semantic errors so proc will act unexpectedly.

    I seriously question why clustered indexes are not rebuilt before non-clustered ones.

  • What does "Redistribution or sale of dba_indexDefrag_sp, in whole or in part, is prohibited." mean?

    I don't want to sell the script.

    But I cannot share/redistribute this script with another DBA? Should the script have been shared/redistributed on SQL Server Central?

    I am all for protecting yourself and the code how you want. I am not sure what to say when the lawyers ask me where I got this and why am I running it on company servers. (yes, they have already started asked about the ola scripts and seem happy with the answers).

    Is this just an example script and it cannot be used as is?


  • Seems you almost got me in trouble. I was ready to throw out the whole script anyway. Whoever wrote it is just a hack and not a true programmer. The license should read "Nobody should run this garbage".

    Give me a month. I'll write a script from scratch and show you how it is done right.

  • Thanks for the script.

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

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