June 18, 2012 at 1:24 am
if you rebuild an index using the SORT_IN_TEMPDB option, do you need tempdb size of at least the size of largest table being build?
for example, we have a 90GB table with a clustered index that must be rebuilt. do we need the full 90GB for all table data?
thanks in advance
June 18, 2012 at 1:49 am
No. It's roughly 20%, but you'll need to test on your largest tables to see exactly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2012 at 1:49 am
OLDCHAPPY (6/18/2012)
if you rebuild an index using the SORT_IN_TEMPDB option, do you need tempdb size of at least the size of largest table being build?for example, we have a 90GB table with a clustered index that must be rebuilt. do we need the full 90GB for all table data?
thanks in advance
Hi
You must have enough space i.e. something the size of the index for the table on you disk. However, the free disk size required varies depending on the type of the index. As you need a clustered index, then you'll need at least the same size your index is (data + B tree). You can ensure this here http://msdn.microsoft.com/en-us/library/ms188281.aspx
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
June 18, 2012 at 2:15 am
Ibelieve you would need approx size of the index your re/building in tempdb - you can find on msdn formula for index size calcs, but how about dbcc filestatats.or.sp space used... would those.work to give estimate?
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
June 19, 2012 at 7:43 am
GilaMonster (6/18/2012)
No. It's roughly 20%, but you'll need to test on your largest tables to see exactly.
Hey Gail - do you have a reference for that number you can point me at? Thanks in advance!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 19, 2012 at 7:53 am
TheSQLGuru (6/19/2012)
GilaMonster (6/18/2012)
No. It's roughly 20%, but you'll need to test on your largest tables to see exactly.Hey Gail - do you have a reference for that number you can point me at? Thanks in advance!
In print, no. From Paul's comments at IE 1 last year.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2012 at 7:59 am
GilaMonster (6/19/2012)
TheSQLGuru (6/19/2012)
GilaMonster (6/18/2012)
No. It's roughly 20%, but you'll need to test on your largest tables to see exactly.Hey Gail - do you have a reference for that number you can point me at? Thanks in advance!
In print, no. From Paul's comments at IE 1 last year.
It's actually a very important number to know for certain. I have advised some clients to disable it for some of their larger tables because I assumed it to be a much larger number than 20% of the table. I really like having sort_in_tempdb on whenever possible for several obvious reasons.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 19, 2012 at 8:16 am
TheSQLGuru (6/19/2012)
GilaMonster (6/19/2012)
TheSQLGuru (6/19/2012)
GilaMonster (6/18/2012)
No. It's roughly 20%, but you'll need to test on your largest tables to see exactly.Hey Gail - do you have a reference for that number you can point me at? Thanks in advance!
In print, no. From Paul's comments at IE 1 last year.
It's actually a very important number to know for certain.
Never needed to know it closer than the estimate I have from Paul. If you do, maybe calculate it for a few indexes and write an article
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2012 at 10:23 am
The official guidance is to be found in the document here, Table 2. It is basically size-of-index-plus-a-bit operation for the sort, no matter if it is in the database or in tempdb. PLEASE read this document however if you are interested in this topic - there are some important caveats, provisos, hidden gems, etc.
Thanks to SQL_Kiwi for the link!
http://technet.microsoft.com/en-us/library/cc966402.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 20, 2012 at 12:32 am
Hi,
Actually you can check the size of your index. It can be 10%,20%, ... even100% (theoretically) of the table size.
You can use this code
select si.name 'Index Name', sum(sd.used_page_count)*8 'Pages size in KB' from sys.dm_db_partition_stats sd
join sys.indexes si on si.object_id = sd.object_id and si.index_id = sd.index_id
where sd.object_id = object_id('dbo.Tablename')
group by si.name
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
June 20, 2012 at 2:13 am
TheSQLGuru (6/19/2012)
Thanks to SQL_Kiwi for the link!
http://technet.microsoft.com/en-us/library/cc966402.aspx%5B/quote%5D
wow, great article. seems paul is way off with his 20% estimate.
according to the OnlineIndex.doc:
Example
To further illustrate the temporary space requirements shown in Table 2, the following example extrapolates the values for a three-column index (10 bytes) shown above for a specified table. For this example, we will create a clustered index from a heap with the SORT_IN_TEMPDB option set to ON and ONLINE set to ON. The heap and resulting clustered index have the following size characteristics:
•Table size: 1,159,633 pages (8.85 GB)
•Number of rows in the table: 179,743,073
•Row size: 46 bytes
•Number of columns in the index: 3 (10 bytes total)
•Size of the clustered index created in the user database: 1,159,650 pages (8.85 GB)
Given that information, we can use the values in Table 2 to determine the approximate temporary space required to create the clustered index.
•The size of the temporary mapping index in tempdb will be approximately 52% of the index size or 603,848 pages (4.6 GB).
•The space used in tempdb for sort runs will be approximately 112% of the index size, or 1,293,728 pages (9.88 GB).
•Total temporary space required is approximately 164% of the index size, or 1,897,576 pages (14.48 GB).
Therefore, for the duration of the online index operation, you need at least 8.85 GB of free space in the user database to store the clustered index and 14.48 GB of free space in tempdb. After the clustered index is created, the space used by the table is freed up, but during the creation of the clustered index, the table and the index coexist so you need free space in the user database equal to the size of the index being created. If SORT_IN_TEMPDB is set to OFF, you need a total of 23.33 GB of temporary space (14.48 + 8.85 GB) in the user database.
In addition, if there are concurrent transactions on the table during the index operation, additional space in tempdb is required for the version store. In one test, we updated 5% of the rows in the table while creating the clustered index online, and the version store was measured to be 143,408 pages, or 1.1 GB for this operation.
June 20, 2012 at 3:17 am
OLDCHAPPY (6/20/2012)
wow, great article. seems paul is way off with his 20% estimate.
Highly unlikely, since he wrote the index rebuild functionality.
My notes however could very well be in error. I have the space down as roughly 120% of the size of the index, with the roughly 20% being either user DB or tempdb and the 100% being user DB. Will have to check that again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2012 at 3:25 am
120% is a better figure. 😉 i've always known the space needed as 120% the size of the index (for user db space, wasn't clear on tempdb) but seems even this is incorrect now after reading the article. there are a lot of "depends".
seems like in general, a safe target for sizing tempdb would be 200% the size of your largest index. that would account for the worst-case (most space needed) scenario plus a bit more for extra wiggle room.
July 10, 2012 at 10:39 am
GilaMonster (6/20/2012)
OLDCHAPPY (6/20/2012)
wow, great article. seems paul is way off with his 20% estimate.Highly unlikely, since he wrote the index rebuild functionality.
My notes however could very well be in error. I have the space down as roughly 120% of the size of the index, with the roughly 20% being either user DB or tempdb and the 100% being user DB. Will have to check that again.
The article states that it's actually fairly situational, depending on clustering, ONLINE and SORT_IN_TEMPDB.
"You must also take into consideration the extra temporary space requirements of the online operation.
•To use the least amount temporary space while rebuilding a clustered index, set ONLINE to OFF.
•To use the least amount of temporary space while rebuilding a nonclustered index, set ONLINE to ON."
And in the article, Table 1, p.13 has quite the grid.
Table 2, p.14 has approx percentages for one, three, and "all" column indexes (4, 10, and 46 bytes, respectively). The "20%" ties in with one ".17" numbers in this table for one column indexes, for online clustered index rebuilds. However, "It depends" seems to be a pretty solid answer regardless.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply