October 19, 2015 at 12:07 am
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
October 19, 2015 at 4:58 am
This was removed by the editor as SPAM
October 21, 2015 at 6:58 am
Makes total sense, good to know. Thanks.
October 21, 2015 at 8:38 am
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, Pluralsight courses
October 21, 2015 at 8:45 am
Hi Gerald, two excellent suggestions, which I'll implement.
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
October 23, 2015 at 2:27 am
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
October 23, 2015 at 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
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
May 18, 2018 at 5:03 am
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.
May 18, 2018 at 5:29 am
Joshua Feierman - Friday, October 23, 2015 5:54 PMHi 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.
May 18, 2018 at 7:00 am
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". 😀
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
Change is inevitable... Change for the better is not.
May 21, 2018 at 1:09 pm
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.
October 31, 2021 at 12:26 pm
This was removed by the editor as SPAM
October 31, 2021 at 11:19 pm
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.
I know it's an older post but here goes.
Yes. I've done this with multiple applications that access common tables.
We have the luxury of a "quiet time". Disabling and then rebuilding indexes makes the underlying tables unavailable during the rebuild unless the ONLINE option is used. Dropping them and recreating them doesn't appear to have the same problem. Note that's ONLY for non-clustered indexes! Disabling a clustered index will make the table unavailable until it's rebuilt. Dropping a clustered index will definitely put you in a world of hurt in more ways than one not to mention causing a rebuild of all non-clustered indexes as a result of the drop and then again we you rebuild the clustered index. This is because either the CI Key or the RID (for Heaps) is an integral part of ALL non-clustered indexes. If you don't have a "quiet time", then don't do the rebuilds. I also strongly recommend that you NOT use REORGANIZE except to compress LOBs because REORGANIZE compresses an index and is not capable of creating any free space above the Fill Factor. It can and usually does cause an index to do massive page splits on the morning after just as if you had rebuilt it with a 100% Fill Factor if the reason for the Fill Factor was to help alleviate the pages splits... which is almost always the only reason to have other than the default Fill Factor of "0", which is identical to a 100% Fill Factor in all cases unless one was foolish enough to change the default Fill Factor for the system.
If you don't have such a quiet time to rebuild large indexes, then you're going to have to figure something else out.
Even with the mods they made, you shouldn't use a built in maintenance plan for index maintenance especially but not limited to if you still think using REORGANIZE is generally a good idea. It's not and here's a link to a 'tube that proves it.
https://www.youtube.com/watch?v=qfQtY17bPQ4
If you have "Always On" or anything else that requires data from the transaction log file, that seriously changes the game in a whole lot of ways but I have to tell you that it's STILL NOT A REASONABLE JUSTIFICATION FOR USING REORGANIZE because it perpetuates page splits and fragmentation and takes a whole lot more log file usage than what has been advertised. I did a whole lot of testing. One of the tests was to restore a database and then either rebuild or reorganize a 147GB Clustered Index that was only 12% fragmented. The rebuild did as expected and used a little more than 147GB of log file. Reorganize caused the log file to explode to more than a QUARTER TERA-BYTE!
Going back to what has already been stated, there are a whole lot of great ideas in the article but, like any other article, "IT DEPENDS" comes into play and it's almost impossible to cover everywhere it depends in a single article. Instead, you must use the ancient DBA art of "Must Look Eye" and figure out the dependencies.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 11, 2022 at 1:40 pm
This was removed by the editor as SPAM
August 20, 2022 at 3:27 pm
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply