September 8, 2010 at 9:25 am
Derrick Smith (9/7/2010)
What is the size of all indexes together/size of the table?sort_in_tempdb does in fact move the logging process for indexing to tempdb, but if it's on full recovery, then the database also logs it. If your DB is on Full (or bulk logged I believe), this option just ends up being faster, but not necessarily using less space. If your tempdb isn't on a separate drive/spindle, it won't even make it faster, as a matter of fact...it's mostly just to reduce IO contention.
Hi Derrick,
The index + table size rounded to the nearest whole number is 52GB. The transaction log in the user database is about twice that size.
We have a fairly fast disk subsystem in place on the production server, so speed isn't an issue for us. The t-logs for tempdb and the t-logs for the user database do in fact reside on the same RAID 10 logical drive, but I'm unsure if there's a way to tell which spindles each reside on. Anyhow speed isn't the issue.
What I was more interested in is performing all index rebuild transactions in the tempdb, as log truncation occurs in a more organic way in the tempdb as it is always in SIMPLE recovery mode (Though I have heard tell of tempdb's transaction log filling due to a lack of disk space or restricted, or very small auto-growth settings.) Neither of those situations should occur for us, as the total free space on the logical drive is much more than the user databases themselves and the log is set to autogrow at a reasonable rate that will result in minimal fragmentation, with no restriction on maximum size.
BTW, did you used to work at Llesiant for like 2 weeks about 3 years ago, maybe 4? We had a DBA here name Derrick Smith (I realize Derrick Smith is not the most unique of names, so this is a shot in the dark) for a couple of weeks, but then he had to move on for personal reasons.
September 8, 2010 at 9:33 am
Thanks Hai,
We actually do exclude the largest index in our database from the rebuild/reorganize job and have been doing so for some time. The index in question though was causing trouble until we increased the size of the transaction log.
I'm mostly interested in why MSFT says "2.Consider setting the SORT_IN_TEMPDB option to ON for the index operation. This separates the index transactions from the concurrent user transactions. The index transactions will be stored in the tempdb transaction log, and the concurrent user transactions will be stored in the transaction log of the user database. This allows for the transaction log of the user database to be truncated during the index operation if it is required. Additionally, if the tempdb log is not on the same disk as the user database log, the two logs are not competing for the same disk space. "
What's happening in our database directly contradicts that information.
Thanks,
Charley
September 8, 2010 at 10:16 am
Charles,
Can you confirm that you are only running the REBUILDs and not the REORGANIZEs when you see this logging behaviour? Reorganize operations are obviously always fully logged in the user database.
Has there been a change to enable (not necessarily use) one of the row-versioning isolation levels for the user database at any point? This as it is important.
Bear in mind that logging the changes to the user database (when the sorted and rebuilt index is replaced) must necessarily be logged in the user database for recovery - only the building and sorting logging ops are performed and logged in tempdb.
If you really cannot use bulk-logged recovery to optimize rebuilds, you probably need to accept that index maintenance will be slow and require very significant log space somewhere.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 8, 2010 at 12:34 pm
Paul White NZ (9/8/2010)
Charles,Can you confirm that you are only running the REBUILDs and not the REORGANIZEs when you see this logging behaviour? Reorganize operations are obviously always fully logged in the user database.
Has there been a change to enable (not necessarily use) one of the row-versioning isolation levels for the user database at any point? This as it is important.
Bear in mind that logging the changes to the user database (when the sorted and rebuilt index is replaced) must necessarily be logged in the user database for recovery - only the building and sorting logging ops are performed and logged in tempdb.
If you really cannot use bulk-logged recovery to optimize rebuilds, you probably need to accept that index maintenance will be slow and require very significant log space somewhere.
Paul
Hi Paul, thanks for the response.
Yes, I can confirm that I am only running a REBUILD when this behavior occurs as I've tested this manually with one of larger indexes. Funny you mention that REORGANIZE is fully logged, because until I got down to testing and ran the REBUILD manually, I thought for sure the behavior occurred during the REORGANiZE/defrag phase of the stored procedure because the documentation states that the operation is fully logged in the user database during a REORGANiZE and of course SORT_IN_TEMPDB can't be specified in the WITH clause of the ALTER INDEX REORGANIZE command.
We have never made a row-versioning isolation level change (I assume you're speaking about the various SNAPSHOT isolation levels and the large amount of transaction logging that they do?)
The current index maintenance is actually not very slow. The job completes a rebuild/reorganize (depending on the fragmentation level) of 107 indexes in a 483GB database that has at least a clustered primary key on each table in about 10 minutes with most of the indexes being rebuilt online and with other maintenance jobs and load operations happening concurrently.
When you state
Bear in mind that logging the changes to the user database (when the sorted and rebuilt index is replaced) must necessarily be logged in the user database for recovery - only the building and sorting logging ops are performed and logged in tempdb.
, do you mean that the user transactions will need to be stored in the user database, whilst the index rebuild and sort operation is done in tempdb? Or are you saying that the index rebuild will (in addition to the user transactions) take up space in the user database transaction log once the index operation is rebuilt and sorted in tempdb?
Thanks,
Charley
September 8, 2010 at 9:28 pm
charles evans (9/8/2010)
We have never made a row-versioning isolation level change (I assume you're speaking about the various SNAPSHOT isolation levels and the large amount of transaction logging that they do?)
Talking about the two row-versioning isolation levels SNAPSHOT and READ_COMMITTED_SNAPSHOT, yes. They use tempdb for row versioning (and online index operations use a separate internal-use-only version store also in tempdb) but that was not what I was getting at. When a clustered index is rebuilt after enabling snapshot, every row is modified to add the 14-byte row versioning info - and this is fully logged of course. In addition, an online non-clustered index rebuild adds one byte to every row for internal use - again fully logged (see https://connect.microsoft.com/SQLServer/feedback/details/488761/online-index-rebuild-adds-one-byte-to-the-index-row).
The current index maintenance is actually not very slow. The job completes a rebuild/reorganize (depending on the fragmentation level) of 107 indexes in a 483GB database that has at least a clustered primary key on each table in about 10 minutes with most of the indexes being rebuilt online and with other maintenance jobs and load operations happening concurrently.
That's great - though it is all relative. Fast hardware can help a lot, but minimally logged index rebuilds do less work, so they're usually much faster.
On the last point: the logging associated with sorting and building the index structure in tempdb is logged in tempdb as you would expect. Nevertheless, once the index is rebuilt, the changes have to be 'copied across' to the user database. Whenever a modification occurs in a database, it must be logged in that same database to allow for recovery.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2010 at 7:02 am
On the last point: the logging associated with sorting and building the index structure in tempdb is logged in tempdb as you would expect. Nevertheless, once the index is rebuilt, the changes have to be 'copied across' to the user database. Whenever a modification occurs in a database, it must be logged in that same database to allow for recovery.
Paul
Thanks Paul for clarifying this. That makes sense.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply