Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
rajsin7786
rajsin7786
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 151
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 )
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2879 Visits: 2235
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
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17576 Visits: 32260
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45018 Visits: 39887
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rajsin7786
rajsin7786
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 151
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
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17576 Visits: 32260
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3939 Visits: 6674
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45018 Visits: 39887
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3939 Visits: 6674
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45018 Visits: 39887
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search