Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

created a index rebuild job want to understand what happens when it runs Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 2:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:33 AM
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 )
Post #1568329
Posted Wednesday, May 7, 2014 3:04 AM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1568334
Posted Wednesday, May 7, 2014 4:31 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 13,890, Visits: 28,285
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1568362
Posted Wednesday, May 7, 2014 4:48 AM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1568371
Posted Friday, May 9, 2014 4:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:33 AM
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.


Post #1569193
Posted Friday, May 9, 2014 6:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 13,890, Visits: 28,285
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1569232
Posted Friday, May 9, 2014 2:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 2,207, Visits: 3,317
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1569444
Posted Monday, May 12, 2014 5:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1569784
Posted Monday, May 12, 2014 7:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 2,207, Visits: 3,317
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1569842
Posted Monday, May 12, 2014 7:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1569849
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse