created a index rebuild job want to understand what happens when it runs

Question

created a index rebuild job want to understand what happens when it runs

rajsin7786

Right there with Babe

Points: 718
More actions
May 7, 2014 at 2:33 am

#290982

Dear Experts,
I've created an index rebuild maintenance job for one table which has 9 indexes, the TSQL generated by the job is below, wanted to understand if all these command run in parallel or sequentially (one after other) when then job runs? We use SQL Server 2008 standard
Also this table is 400 GB in size, so need you help to understand what i have to set for SORT_IN_TEMPDB option ?
Thanks for your help.
USE [DB]
GO
ALTER INDEX [idx_1] ON sales REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
USE [DB]
GO
ALTER INDEX [idx_date] ON sales REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
USE [DB]
GO
ALTER INDEX [idx_multi] ON sales REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
USE [DB]
GO
ALTER INDEX [idx_op] ON sales REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
USE [DB]
GO
ALTER INDEX [idx_prod] ON sales REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
USE [DB]
GO
ALTER INDEX [idx_prom] ON sales REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
USE [DB]
GO
ALTER INDEX [idx_store] ON sales REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
USE [DB]
GO
ALTER INDEX [idx_supp] ON sales REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
USE [DB]
GO
ALTER INDEX [idx_time] ON sales REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )

Viewing 10 posts - 1 through 9 (of 9 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

SORT_IN_TEMPDB option will use tempdb while reindexing, due to this your tempdb will grow. Also reindexing may have blocking depending on the database activity. Other option is if fregmentation is low you can use reorganize index instead of rebuild.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

Those are going to run directly in sequence, not in parallel.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

Since this is SQL Server 2008, you can't rebuild a partitioned table (implied by the "Partition = ALL") and this table will be unavailable during the rebuild of all the indexes.

On the SORT IN TEMPDB thing... It'll only affect TEMPDB by 10 or 20% of what the largest index is. If THATs a problem, then you need to stop an make sure you have enough room for that in TEMPDB. If you decide to not sort in TEMPDB, then you need to make sure that the database the table lives in has enough room to grow.

If you're operating in the FULL recovery mode, this will NOT be a minimally logged operation and you need to make sure that you have enough log file space.

Of course, I'm basing all of the file size worries on the fact that you have "Partition = ALL" in the code so I'm assuming that it's a fairly large paritioned table.

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

rajsin7786 Right there with Babe Points: 718 More actions · Answer 4

Thanks a lot friends, your replies have been very helpful.

The table is not partitioned because we use SQL Server 2008 standard so this version does not have partitioning feature. I don't really know what 'Partition = ALL' means as i am new to SQL Server. I will be reading through this parameter now.

I am thinking to avoid the SORT_IN_TEMPDB option unless it could be of a great help (performance perspective)?

This table is 400GB in size, has 9 indexes which are approx. 200 GB in size. The fragmentation is pretty high on all these indexes (>50) that's why i choose to rebuild them.

i am quite happy to know that these statements will run in sequence so that means after one statement completes the next will start right? (sorry if i am repeating my question again)

Managed to find a 10 hour window to run the rebuild job so there will be no user activity at this time and server is fully available for maintenance.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 5

Yes, one will finish then the next one will start.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 100942 More actions · Answer 6

Jeff Moden (5/7/2014)
On the SORT IN TEMPDB thing... It'll only affect TEMPDB by 10 or 20% of what the largest index is.

I don't understand the rationale for that statement. I don't see how the total tempdb space used can be any smaller than the largest index size.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

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

ScottPletcher (5/9/2014)
Jeff Moden (5/7/2014)
On the SORT IN TEMPDB thing... It'll only affect TEMPDB by 10 or 20% of what the largest index is.
I don't understand the rationale for that statement. I don't see how the total tempdb space used can be any smaller than the largest index size.

I agree that the documentation that MS provides states that you must have enough space available to TEMPDB to hold the leaf level of the entire index if the SORT_IN_TEMPDB option is used. I've never seen that happen, though, despite rebuilding some rather large clustered indexes. In fact, the TEMPDB space used for an index rebuild with the SORT_IN_TEMPDB option turned on can be 0. From http://technet.microsoft.com/en-us/library/ms188281.aspx...

Note
If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

--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: 100942 More actions · Answer 8

Jeff Moden (5/12/2014)
ScottPletcher (5/9/2014)
Jeff Moden (5/7/2014)
On the SORT IN TEMPDB thing... It'll only affect TEMPDB by 10 or 20% of what the largest index is.
I don't understand the rationale for that statement. I don't see how the total tempdb space used can be any smaller than the largest index size.
I agree that the documentation that MS provides states that you must have enough space available to TEMPDB to hold the leaf level of the entire index if the SORT_IN_TEMPDB option is used. I've never seen that happen, though, despite rebuilding some rather large clustered indexes. In fact, the TEMPDB space used for an index rebuild with the SORT_IN_TEMPDB option turned on can be 0. From http://technet.microsoft.com/en-us/library/ms188281.aspx...
Note
If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

Sure, if it can be performed in memory it won't take disk space, just as with a temp table.

I thought SQL created the new index completely before overwriting the old index. To me, that would require most of the space the full index would in the sort files. Perhaps SQL doesn't really need to do that, even for large indexes.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

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

You're correct. But I've never seen it do that in TempDB even with the Sort_In_TempDB option turned on. It seems to always do it in whatever the source FileGroup is. Of course, that's a bit annoying if you're trying to "pack" a FileGroup for a given partition.

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