question on Index Rebuilds

Question

Post reply

question on Index Rebuilds

Bruin

SSCrazy

Points: 2071
More actions
December 29, 2020 at 12:34 am

#3824919

I have 3 large tables with 133 million rows in 2 of the tables and over 570 million in the other. I have a 12 hour window of downtime
and would like to rebuild the Indexes on these tables, but concerned on the length of time it may consume. Are there any diagnostics
I could run to maybe give me an idea of the time the rebuilds may consume. The server has 32gig of memory and 4 processors and would
be solely dedicated to the Index rebuild during this time period.
Thanks, any suggestions would be appreciated.

Viewing 15 posts - 1 through 15 (of 47 total)

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

Bruin SSCrazy Points: 2071 More actions · Answer 1

Bruin

SSCrazy

Points: 2071

December 29, 2020 at 12:34 am

#3824920

sql2012 - sp4

ScottPletcher SSC Guru Points: 101121 More actions · Answer 2

It shouldn't take anything remotely close to 12 hours to rebuild indexes on tables that size with that much RAM available.

Unless maybe the table's incredibly wide. What is the average row length for each table?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Bruin SSCrazy Points: 2071 More actions · Answer 3

nbr-of-columns avg-row-length records

15 905 133 million

4 412 570 million

4 6 133 million

The DB is in "SIMPLE" mode, and other things -- space - ?

Who can you monitor progress ?

Thanks.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

I don't know of a way to predict index REBUILD times. I can only tell you that I have a similar table that has a similar row count and slightly larger average row size that weighs out at 147GB and it took 12 minutes on one of my DEV boxes in the Simple Recovery Model for the Clustered Index. YMMV depending on your I/O system performance.

As a bit of a sidebar, if the indexes are page-compressed, multiply that duration by 4. I don't know what the multiplication factor will be for row compression because I've not tried that yet.

Also be aware that for any index over 128 extents (that's just 8MB in size), that you'll need room for the largest index to temporarily double in size because, for such indexes, a totally new index is created with all the data in place before dropping the old index (data).

And, to ask the question, you're actually planning on using REBUILD and not REORGANIZE, correct? I ask because REORGANIZE is not the tame little kitty that everyone claims it is. With just 12% fragmentation on the table I spoke of, it took almost two hours and it caused the log file to blow out at 227GB even in the Simple Recovery Model. The log file for the rebuild never got over 20GB on a standalone machine nor over 37GB on an active machine during the REBUILD.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2071 More actions · Answer 5

I'm going to do a REBUILD. If I have 3+ indexes along with PK, is there any rule on thumb to rebuild each individually or

REBUILD ALL on the table?

Any trade-offs for doing individually or ALL. Do you just run a script or thru SSMS?

Thanks.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

If you need rebuilding indexes it’s probably a good time to rethink the indexing strategy.

you need to figure out what causes fragmentation and redefine the indexes in such a way which will minimise the fragmentation as tables get populated.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Sergiy wrote:

If you need rebuilding indexes it’s probably a good time to rethink the indexing strategy. you need to figure out what causes fragmentation and redefine the indexes in such a way which will minimise the fragmentation as tables get populated.

Amen to that!

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101121 More actions · Answer 8

Use REBUILD ALL, it will (should) be more efficient.

But pre-grow the log first to the total size you will need for the REBUILD. It's a very slow process when the log has to grow dynamically. If you're unsure of what the total size will be, and can afford the space, just pre-grow the log so its available space is the size of the table plus all its nonclustered indexes.

During the REBUILD you can look to see how much of the log is used via DBCC LOGINFO. On SQL 2016+, we can use sys.dm_db_log_info ( database_id ) for complete info, even after the REBUILD completes. But before 2016, sadly not available.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Bruin wrote:

I'm going to do a REBUILD. If I have 3+ indexes along with PK, is there any rule on thumb to rebuild each individually or REBUILD ALL on the table?
Any trade-offs for doing individually or ALL. Do you just run a script or thru SSMS?
Thanks.

I never use the ALL option because, a lot of times, there will be one or more indexes that don't need to be rebuilt and so doing unnecessary rebuilds is a total waste of time, CPU, I/O, and disk space (log file).

As for how to run it, a stored procedure is best. I'm changing all of my stuff to do some magic with determining the proper Fill Factor and minimize disk usage (including a thing that a fellow DBA and I call "external REBUILDs") and it's not yet ready for prime time or I'd share it with you.

So, my rule of thumb is that, since each index has it's own personality, I rebuild them one at a time. There's also the issue of having disabled indexes for one reason or another. ALL will have some consequences there. I've not researched them all because I simply don't use ALL.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101121 More actions · Answer 10

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.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Bruin SSCrazy Points: 2071 More actions · Answer 11

does anyone have there SP they run and like to share ?

Thx.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

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?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2071 More actions · Answer 13

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

ScottPletcher SSC Guru Points: 101121 More actions · Answer 14

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

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".