March 31, 2019 at 1:06 pm
Hello All,
Can anyone give me the best practices for rebuilding indexes in sq server?
I got red gate monitoring alert saying many of table indexes have more than 90% fragmentation.
So i performed index rebuild and statistics.
After this the transaction log increased hugely. I was panicked and performed tlog backup and shrinking log file continuously. while doing this thank god the disk space came back to normal.
If i choose to go with maintenance plan, the rebuilding indexs for some of the tables is not possibe. How to go with rebuild and reorganize indexes same time?
also if i go for rebulding indexes online then will it impact on production?
Generally what is the best practice to for index maintenance?
In which scenario i can go for online and offline index.
when to go rebuild and reorganise?
if i perform rebuilding indexes on all tables will this be wrong?
As i am new to dba activities. i need help to understand this all deeply.
Thanks in advance for all your help and guidance.
April 1, 2019 at 12:16 pm
This was removed by the editor as SPAM
April 1, 2019 at 12:19 pm
This was removed by the editor as SPAM
April 2, 2019 at 10:57 am
This was removed by the editor as SPAM
April 4, 2019 at 7:43 am
This was removed by the editor as SPAM
April 24, 2019 at 9:53 pm
This was removed by the editor as SPAM
April 24, 2019 at 9:54 pm
Quite a few people use Ola Hallengrens solution for this, if you google that name it will come right up.. just schedule the job to run during your maintenance window weekly. If you do this often enough most time you will only need to reorganize not rebuild.
April 24, 2019 at 10:00 pm
If you don't have mirroring or log shipping or anything else running that would be damaged by changing the Recovery Model, do a log backup, change the recovery model to BULK-LOGGED, do your REBUILDs (NOT REORGANIZE... it's useless except for LOB compression and not very good at that, either and it's always fully logged), change back to the FULL recovery model, and take another log backup.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 25, 2019 at 1:25 am
Quite a few people use Ola Hallengrens solution for this, if you google that name it will come right up.. just schedule the job to run during your maintenance window weekly. If you do this often enough most time you will only need to reorganize not rebuild.
I strongly recommend against the use of REORGANIZE except for LOB maintenance and you have to be careful even then. It removes free space from the index at the most critical of times... just when you need free space. It's also not the timid beastie that some claim it to be. I used it on a 146GB clustered index that had only 12% logical fragmentation... it caused the 20GB log file to explode to 227GB, took an hour and 21 minutes to complete, caused T-Log backups to go crazy, still had to rebuild stats, and left thousands of pages with page densities much less than the fill factor because it will not create new pages ever.
Thank goodness that was on a test box.
I restored the same database from the same file that I had done for the test above and did a REBUILD. I did "cheat" a bit... I set the database to the "BULK_LOGGED" Recovery Model (which you can't do in certain cases) and the REBUILD took less than 13 minutes, inherently rebuilt statistics, cause the log file to only grow to 37 GB, and left no straggler pages when it came to page density.
The REBUILD in the FULL Recovery Model wasn't much worse except it did cause the log file to grow to a bit more than the original 146GB, which is still about 80GB less log file than what REORGANIZE did.
Your mileage will vary but now you know that you should at least take a look at what REORGANIZE may be doing to you.
And that REORG after 10% and REBUILD after 30% general recommendation that most of the world seems to be stuck on? Yeah... don't go by that. Even the guy (Paul Randal) that wrote that recommendation will tell you that he came up with that because MS kept pounding on him for a "general" recommendation. I you do a much deeper dive on the documentation for sys.dm_db_index_physical_stats (which Paul also wrote and did a great job on) well past the 10/30 "general" recommendation, you'll find much better clues as to what to do with different indexes on how to defrag them.
Through some serious testing of both contrived and real data, I've found that there are 5 different "Insert Pattern" categories (and one of those has 2 subcategories). Ed Wagner added a 6th and I've recently and totally by accident discovered a rare but very real 7th "Insert Pattern". All of that is compounded by "ExpAnsive" Updates and absolute horror that MS created when they decided to default LOBs to "In-Row" rather the "Out of Row" (like they used to be) way back in 2005 when they came out with the MAX datatypes.
Ola's code is absolutely the "Gold Standard" when it come to index maintenance but it (nor any tool in existence, yet) will help you make the right decisions about how and when to do index maintenance on any given index. If you just follow the 10/30 "standard" that most people follow, plan on seeing a lot of blocking that you might not yet be aware of on the day after you run your index maintenance. That's what happened to me (actually, it reached a tipping point where it materialized as massive blocking on Mondays, which is the day after I did supposed "Best Practice" index maintenance every week) and a couple of other people I've been working with on the problems associated with index fragmentation.
Heh... yeah... I know. I sound like a cuckoo to a lot of people... until I demonstrate and explain to them the actual reasons why their carefully planned indexes with carefully planned Fill Factors fragment so quickly especially after supposed "Best Practice" index maintenance. There is no simple "Best Practice" panacea when it comes to index maintenance, especially if you believe that REORGANIZE is a good thing for most indexes or you make the horrible mistake of waiting for 10% or {gasp} 30% fragmentation to occur (most of the damage in the form of bad page splits and excessive log file usage is done well below even 10% for a lot of indexes).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply