SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Harnessing SQL Server Metadata- Disabling and Rebuilding Indexes


Harnessing SQL Server Metadata- Disabling and Rebuilding Indexes

Author
Message
Joshua Feierman
Joshua Feierman
SSC Eights!
SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)

Group: General Forum Members
Points: 845 Visits: 305
Comments posted to this topic are about the item Harnessing SQL Server Metadata- Disabling and Rebuilding Indexes

Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software

Follow me at http://sqljosh.com
Iwas Bornready
Iwas Bornready
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67266 Visits: 886
Makes total sense, good to know. Thanks.
Gerald Britton
Gerald Britton
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12353 Visits: 1828
In the WHERE clause you have:

(sobj.name = @i_Table_Name or @i_Table_Name = '')



Since @i_Table_Name is defined as sysname, you might want to use N'' instead of just ''.

BTW did you consider using

(sobj.name like @i_Table_Name  )



instead, thus allowing for wildcards and then covering the empty string with N'%' instead of just N''?

I often find that explicit is better than implicit.

Gerald Britton, MCSE-DP, MVPToronto PASS Chapter
Joshua Feierman
Joshua Feierman
SSC Eights!
SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)

Group: General Forum Members
Points: 845 Visits: 305
Hi Gerald, two excellent suggestions, which I'll implement.

Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software

Follow me at http://sqljosh.com
pcindex
pcindex
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 202
will this enable ALL disabled indexes?

if so, there is a flaw, as if we already have a few disabled indexes (out of use)
and we try the disable/re-enable routine,
it will re-enable those ("out of use") ones too.

we somehow need to "save" which ones we are disabling in the routine,
and then re-enable just those.

regards,
Makis
Joshua Feierman
Joshua Feierman
SSC Eights!
SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)

Group: General Forum Members
Points: 845 Visits: 305
Hi Makis,

That's an interesting suggestion. I'll have to think about that one a bit, as it would require having a table somewhere where the indexes that the procedure initially disables are stored, and I was trying to avoid having any footprint beyond a procedure. Do you envision it as keeping that information stored until you run it in enable mode, then deleting it? What about if multiple processes are calling it at the same time, say for different databases or sets of tables; how do you think it should handle that?

Thanks for the feedback in any case.

-Josh

Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software

Follow me at http://sqljosh.com
Stephen Randolph
Stephen Randolph
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 129
If you’re using any type of version control be careful with doing anything more than enabling the index or risk having that change removed and the index rebuilt during your next deployment.
Rick-153145
Rick-153145
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2209 Visits: 736
Joshua Feierman - Friday, October 23, 2015 5:54 PM
Hi Makis,That's an interesting suggestion. I'll have to think about that one a bit, as it would require having a table somewhere where the indexes that the procedure initially disables are stored, and I was trying to avoid having any footprint beyond a procedure. Do you envision it as keeping that information stored until you run it in enable mode, then deleting it? What about if multiple processes are calling it at the same time, say for different databases or sets of tables; how do you think it should handle that?Thanks for the feedback in any case.-Josh


Why not have a table with all disabled indexes in? You could even write this into a database or server trigger so it wouldn't be a manual operation, then just don't re-enable any that are in the table.

EDIT: You would obviously need to disable the trigger when you run this process, but it only needs to be for the initial disable of the indexes.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)

Group: General Forum Members
Points: 896111 Visits: 48248
I didn't do a deep dive on this older article but I do disable indexes not only for deletes but also for index maintenance. It's a good article to explain how to do that but, just like the author suggested, it's an introduction to what's possible and you do have to use your head.

Re-enabling previously disabled indexes are certainly one of those but, like the author said, "caveat emptor". Pay attention. If you have such things, then you need to add to the author's good base code to make sure that disabled indexes that are meant to continue to be disabled, stay that way (who the heck does such a thing for the long term anyway? Blink)

The other thing to be aware of is that you shouldn't disable unique indexes because they may be the target of FKs and disabling those indexes could screw FKs up (not to mention they could allow bad data in while they're disabled). "Must look eye". BigGrin

Once the caveats are understood, disabling indexes for deletes can really help performance a lot. Disabling indexes can also help a whole lot if you have large indexes to maintain. It won't help so much for the amount of time it takes but can keep you from unnecessarily blowing out your MDF file because, for any index of 128 extents (that's only 8MB, folks), the old index will persist until the new index is created and committed. Once the new one is committed, the old one is dropped and creates possibly a huge amount of unwanted free space. That's also a part of the reason why I tell folks to rebuild the index with the largest page count first... that will create enough reusable free space for the other indexes (thinking mostly clustered indexes here, which are typically not disabled prior to rebuilds).

There's also the trick of doing a double-rebuild for the largest clustered index onto a separate file-group and back to keep huge amounts of unwanted free space from occurring in the MDF but the details of how to do that would make a better (and longer) article than a post.

@Joshua... nice article. Thanks for taking the time to write it even if I'm seriously late in saying so.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
alen teplitsky
alen teplitsky
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27597 Visits: 4925
Has anyone done this on servers with numerous applications accessing them? How do you handle applications slowing down due to the loss of indexes? And how does Always On react to rebuilding several large indexes at once? Long ago I used to use maintenance plans and quit because rebuilding every index of a large table created a storm of AlwaysOn replication that also screwed up replication.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search