December 30, 2020 at 10:52 pm
Even if the db is not normally in SIMPLE mode, you would certainly want it to be in SIMPLE for these rebuilds, to drastically reduce logging requirements.
If the db is normally in FULL mode, then the index rebuild sequence should be:
- take a differential backup
- put the db in SIMPLE mode
- do all needed index rebuilds
- put the db back in FULL mode
- take a full backup of the db
You don't need to break the log file chain with the SIMPLE Recovery Model. Indexes will rebuild with Minimal Logging in the BULK LOGGED Recovery Model just fine.
You only need to take a log file backup before and after the excursion to the BULK LOGGED Recovery Model to limit the time frame where you won't be able to do a mid-log file PIT restore because of the Minimal Logging.
--Jeff Moden
December 30, 2020 at 10:55 pm
I was thinking of using this script on my fragmented index rebuilds. USE [xxxx] GO ALTER INDEX [PK_EmployeeJobHistory_1] ON [dbo].[EmployeeJobHistory] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) GO
Why do you think you need a 90% Fill Factor for a History table that should only ever suffer Inserts and no Updates or Deletes?
--Jeff Moden
December 30, 2020 at 11:08 pm
ScottPletcher wrote:With a table that large, say you have a total of 6 indexes, 1 clus and 5 nonclus.
I'd expect it to be faster to do a REBUILD ALL than to REBUILD the clus index and then 2 nonclus indexes each separately. At least as I understand SQL's processing, the REBUILD ALL can rebuild all indexes with only a single scan of the table.
Do you have a link that explains that, Scott?
Really looking forward to a link for your claim, Scott. The closest thing I can find in the MS Documentation is that the use of ALL causes all indexes to be rebuilt in a single transaction. The problem there is that if any fault occurs on any of the indexes, ALL of the rebuilds will be rolled back.
I don't know for sure because I don't use ALL but I'm thinking that a single transaction might be a little tough on the log file, as well.
--Jeff Moden
December 30, 2020 at 11:30 pm
Can you please show the definition of that PK_EmployeeJobHistory_1?
and probably of the other indexes too?
On such tables indexes should never require defragmenting/rebuilding.
_____________
Code for TallyGenerator
December 31, 2020 at 12:06 am
I sent just an example rebuild, sorry.
December 31, 2020 at 3:54 am
Jeff Moden wrote:ScottPletcher wrote:With a table that large, say you have a total of 6 indexes, 1 clus and 5 nonclus.
I'd expect it to be faster to do a REBUILD ALL than to REBUILD the clus index and then 2 nonclus indexes each separately. At least as I understand SQL's processing, the REBUILD ALL can rebuild all indexes with only a single scan of the table.
Do you have a link that explains that, Scott?
Really looking forward to a link for your claim, Scott. The closest thing I can find in the MS Documentation is that the use of ALL causes all indexes to be rebuilt in a single transaction. The problem there is that if any fault occurs on any of the indexes, ALL of the rebuilds will be rolled back.
I don't know for sure because I don't use ALL but I'm thinking that a single transaction might be a little tough on the log file, as well.
Partly just common sense as well. After you've directed SQL to rebuild ALL the indexes, why would it not prep all of them with one pass of the table? It'd be rather bizarre to read the table once for each index when SQL knows, in advance, that ALL indexes need rebuilt. Can you provide a link that says it does NOT prep all indexes at once?
I'm dealing with this specific situation, not rebuilds generically. Celko gets fixated on general rules too ignoring the specific details of the current q.
These are stand-alone rebuilds and I've already stated that the log should be pre-grown so that it doesn't have to grow dynamically. Since nothing else is running, and the db is in SIMPLE mode, and SQL can do minimal logging, I don't really care how much is written to the log.
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!
December 31, 2020 at 3:55 am
ScottPletcher wrote:Even if the db is not normally in SIMPLE mode, you would certainly want it to be in SIMPLE for these rebuilds, to drastically reduce logging requirements.
If the db is normally in FULL mode, then the index rebuild sequence should be:
- take a differential backup
- put the db in SIMPLE mode
- do all needed index rebuilds
- put the db back in FULL mode
- take a full backup of the db
You don't need to break the log file chain with the SIMPLE Recovery Model. Indexes will rebuild with Minimal Logging in the BULK LOGGED Recovery Model just fine.
You only need to take a log file backup before and after the excursion to the BULK LOGGED Recovery Model to limit the time frame where you won't be able to do a mid-log file PIT restore because of the Minimal Logging.
Not me. The time required to apply that log would be extraordinarily long, if it can be applied at all. Do you really want to re-apply rebuilds of multiple 100M+ row tables, even if possible? Knock yourself out, but I'm going back to a backup from after the rebuilds are complete. Since I plan to do a full backup anyway, I prefer go to SIMPLE mode to make sure I reduce logging as much as possible.
Edit:
In reality, you'd likely just go back to before the REBUILDs occurred because it's just impractical to apply a log that lengthy. But that means you've lost the REBUILDs themselves and all activity that occurred after the REBUILDs. So, yes, in your approach, "the log chain is not broken", but that's a Pyrrhic victory.
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!
December 31, 2020 at 5:03 am
Say you have a 10GB table with 1 clus and 14 nonclus indexes, each with 1 column. You "tell" SQL to rebuild ALL indexes. Would SQL scan the table 15 times, a separate scan for every index? Geez, I hope not.
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!
December 31, 2020 at 9:09 pm
Say you have a 10GB table with 1 clus and 14 nonclus indexes, each with 1 column. You "tell" SQL to rebuild ALL indexes. Would SQL scan the table 15 times, a separate scan for every index? Geez, I hope not.
It scans the table 1 time - when rebuilding the clustered index.
And it also scans 14 other "little tables" which make up the 14 nonclusterd indexes, each one on its own time.
To rebuild a nonclustered index you don't need to access the table pages at all.
_____________
Code for TallyGenerator
December 31, 2020 at 10:26 pm
ScottPletcher wrote:Say you have a 10GB table with 1 clus and 14 nonclus indexes, each with 1 column. You "tell" SQL to rebuild ALL indexes. Would SQL scan the table 15 times, a separate scan for every index? Geez, I hope not.
It scans the table 1 time - when rebuilding the clustered index.
And it also scans 14 other "little tables" which make up the 14 nonclusterd indexes, each one on its own time.
To rebuild a nonclustered index you don't need to access the table pages at all.
Again, hopefully not, as that's wasted I/O too ... unless maybe if SQL can avoid sorting by reading index rows in key order. But, as fragmented as all these indexes are, I wouldn't see ordered=true scans being efficient at all in this case. It would be interesting to know exactly what SQL was doing. I'd have to lookup the trace flags needed to get more details on the actual rebuild plan(s).
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!
January 1, 2021 at 6:36 pm
Jeff Moden wrote:ScottPletcher wrote:Even if the db is not normally in SIMPLE mode, you would certainly want it to be in SIMPLE for these rebuilds, to drastically reduce logging requirements.
If the db is normally in FULL mode, then the index rebuild sequence should be:
- take a differential backup
- put the db in SIMPLE mode
- do all needed index rebuilds
- put the db back in FULL mode
- take a full backup of the db
You don't need to break the log file chain with the SIMPLE Recovery Model. Indexes will rebuild with Minimal Logging in the BULK LOGGED Recovery Model just fine.
You only need to take a log file backup before and after the excursion to the BULK LOGGED Recovery Model to limit the time frame where you won't be able to do a mid-log file PIT restore because of the Minimal Logging.
Not me. The time required to apply that log would be extraordinarily long, if it can be applied at all. Do you really want to re-apply rebuilds of multiple 100M+ row tables, even if possible? Knock yourself out, but I'm going back to a backup from after the rebuilds are complete. Since I plan to do a full backup anyway, I prefer go to SIMPLE mode to make sure I reduce logging as much as possible.
Edit: In reality, you'd likely just go back to before the REBUILDs occurred because it's just impractical to apply a log that lengthy. But that means you've lost the REBUILDs themselves and all activity that occurred after the REBUILDs. So, yes, in your approach, "the log chain is not broken", but that's a Pyrrhic victory.
I'm not sure what you're thinking, Scott. REBUILDs are Minimally Logged and the log file isn't going to be huge. There's just no reason to break the log file chain to do index rebuilds.
--Jeff Moden
January 1, 2021 at 9:14 pm
ScottPletcher wrote:Jeff Moden wrote:ScottPletcher wrote:Even if the db is not normally in SIMPLE mode, you would certainly want it to be in SIMPLE for these rebuilds, to drastically reduce logging requirements.
If the db is normally in FULL mode, then the index rebuild sequence should be:
- take a differential backup
- put the db in SIMPLE mode
- do all needed index rebuilds
- put the db back in FULL mode
- take a full backup of the db
You don't need to break the log file chain with the SIMPLE Recovery Model. Indexes will rebuild with Minimal Logging in the BULK LOGGED Recovery Model just fine.
You only need to take a log file backup before and after the excursion to the BULK LOGGED Recovery Model to limit the time frame where you won't be able to do a mid-log file PIT restore because of the Minimal Logging.
Not me. The time required to apply that log would be extraordinarily long, if it can be applied at all. Do you really want to re-apply rebuilds of multiple 100M+ row tables, even if possible? Knock yourself out, but I'm going back to a backup from after the rebuilds are complete. Since I plan to do a full backup anyway, I prefer go to SIMPLE mode to make sure I reduce logging as much as possible.
Edit: In reality, you'd likely just go back to before the REBUILDs occurred because it's just impractical to apply a log that lengthy. But that means you've lost the REBUILDs themselves and all activity that occurred after the REBUILDs. So, yes, in your approach, "the log chain is not broken", but that's a Pyrrhic victory.
I'm not sure what you're thinking, Scott. REBUILDs are Minimally Logged and the log file isn't going to be huge. There's just no reason to break the log file chain to do index rebuilds.
I see advantages to using my method. I don't see any disadvantage to breaking the log chain here. You can't apply the log that was created during the rebuilds, so I see no reason to have to back it up. And after having put the db into non-full mode, you'll want to take a full backup after any processing that adds pages anyway.
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!
January 2, 2021 at 3:43 am
That's not true, Scott. The log can be applied. You just have to use the whole thing. You can't stop in the middle for a PIT restore. If something happens during the rebuilds and the log file persists even though the MDF flew south, you can "tail log" it and use it in the restore. If you increase the frequency of the log file backups during the rebuilds, you can actually get real close to a PIT restore.
--Jeff Moden
January 2, 2021 at 6:39 am
That's not true, Scott. The log can be applied. You just have to use the whole thing. You can't stop in the middle for a PIT restore. If something happens during the rebuilds and the log file persists even though the MDF flew south, you can "tail log" it and use it in the restore. If you increase the frequency of the log file backups during the rebuilds, you can actually get real close to a PIT restore.
How is that possible, since with minimal logging the data is not on the log. You can't do a forward recovery with that log since the data doesn't exist on the log. At least I don't see how it could ever be done. What point would there be to applying it? The page allocations by themselves aren't all that valuable.
In cases when it could be applied -- where some things were logged more fully -- it would be a huge amount of time, as I stated above. Typically even forward recovering to the point before the rebuilds start is very lengthy. That's why I prefer to start with a differential and end with a full backup. That way, you have an easy recovery to either the point right before the rebuilds or the point right after.
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!
January 2, 2021 at 3:06 pm
Those are really good questions, Scott. First, I agree that the differentials are a great idea whether you're in the BULK LOGGED or SIMPLE Recovery Models. Better to be safe than sorry.
Your great questions would make for a really good article especially since I don't know of anyone that has written such an article to demonstrate that it works and maybe how it even works. I only know that it hasn't been a problem with the stuff I do at work with the reduction of data for DB moves to small environments. I'll see if I can setup a demonstration but I have a pretty full dance card for the next couple of weeks and it's not likely that I'll get to it soon.
--Jeff Moden
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply