Alright, it didn't go as expected. I set sort_in_tempdb = on and didn't get the results i expected. Looking over the logs, the index was reorganized and not rebuilt. This got me thinking about performance so I set up a test.

I have two tables, sourcedata and test, each with a clustered index.

Here are the steps I would take before each round:

-insert 500k records from test.sourcedata into test.test

-insert 500k records into test.test to create fragmentation (99% and 3300 pages)

-truncate transaction log for more accurate results

I would note the log sizes of tempdb and the test database after each truncate and again after each index operation. Here is what I got:

inserted 500k (total 1mil) and truncate log

--testdb 3.7%

--tempdb 9.5%

rebuild sort in tempdb on

--testdb 8.5%

--tempdb 9.5%

inserted 500k (total 1mil) and truncate log

--testdb 0.82%

--tempdb 9.5%

rebuild sort in tempdb off

--testdb 5.6%

--tempdb 9.6%

inserted 500k (total 1mil) and truncate log

--testdb 4.99%

--tempdb 9.5%

reorganize

--testdb 29.7%

--tempdb 9.5%

What gets me here is that reorganize had more of an effect on the log than a rebuild operation. It could be my own misunderstanding, but I thought a rebuild was more log intensive. I'm also unsure why the sort_in_tempdb = on didn't result in a larger tempdb log file, the testdb log actually grew more.