September 2, 2010 at 4:23 pm
I am running Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) on a Windows Server 2008 x64 EE server.
I wondered if anyone has come across this issue or knows why this may be happening. I have a nightly job that runs ALTER INDEX REBUILD or ALTER INDEX REORGANIZE for each index in a user database. The SP called by the job determines if the table has any underlying LOB datatypes and if so will not attempt an online rebuild, it also determines whether to rebuild based off the fragmentation percentage of the index.
A couple of months after porting the database to SQL 2008 from SQL 2005 and switching servers, the transaction log in the user database started blowing up when this SP was run. The trouble definitely occurs with online rebuilds and I'm not sure about offline rebuilds. Each online rebuild executes this code:
ALTER INDEX [IX_NAME] on [tbl_TABLENAME] REBUILD WITH ( STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = on, maxdop = 4)
go
(IX_NAME is just a generic index name and tbl_TABLENAME is a generic table name, both generalized for posting this on a public forum.)
This MSDN article states that "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." http://msdn.microsoft.com/en-us/library/ms184246(v=SQL.100).aspx
The user database operates under the FULL recovery model. There are almost no concurrent user transactions happening when this runs. The size of the index is ~20GB, the size of the user database transaction log is ~60GB, the size of the tempdb transaction log is ~60GB, and each t-log usually starts out with between 1 and 3% full, as we have a log backup job running every 10 minutes and there are almost no concurrent transactions running during this time. The SP that runs this uses no explicit transactions.
Still, the user database's transaction log blows up during this operation, reaching 99% full in a matter of minutes, while the tempdb transaction logs fill by less than 1%. I have also tried running the same index operation manually in SSMS and the same results happen.
Any ideas on what may be causing this? Is it because the operation is online? I can't seem to find any documentation that specifically mentions what happens in the transaction log for online index operations that use SORT_IN_TEMPDB.
Any help or clues to the puzzle will be greatly appreciated.
-Charley
September 2, 2010 at 5:00 pm
You are attempting to run this against just one index or is a bunch of indexes (when you run it manually in SSMS)?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 2, 2010 at 5:24 pm
Interesting one. You said that you migrated from 2005, is the user database in 2008 compatability mode?
September 2, 2010 at 11:19 pm
Jason,
I'm running this against just one index when I do so manually.
Henners,
Yes the user database is run in SQL Server 2008 (100) compatibility mode.
Thanks for the responses.
September 3, 2010 at 1:16 am
If this issue reported is on production box, I would recommend upgrading MSSQL server to the latest and greatest SP and HF on test box just to check if the issue is reproduceable.
- SAMJI
If you marry one they will fight with you, If you marry 2 they will fight for you 🙂
September 3, 2010 at 2:56 am
I'm also having the same problem as Charley using SQL Server 2008 R2 & SQL Server 2005 SP3 (both x64).
Any help would be greatly appreciated!
September 3, 2010 at 7:10 am
As far as I'm aware, regardless of where the data is sorted, the transaction log of the user database must fully log the operation to enable Rollback.
In an Online Index Rebuild, this would involve:
1) Logging the creation and all updates to the Temporary Mapping Index (this is the temporary index that records all changes made to the index being rebuilt during the operation to enable the new index to be updated again at the end of the operation) - the size of this is dependent on how many INSERT/UPDATE/DELETE's are performed against the base table during the rebuild
2) The creation of the new Target Index
3) The de-allocation of the Source Index
The logging you're potentially avoiding by sorting in Tempdb is just the temporary write to enable a sort to be performed before it can be written back to the Target index (incidentally, MS has a note that says this option is ignored if it can perform the sort in memory).
I don't believe there's been any fundamental change between 2005 and 2008 though, so I'm not so sure this behaviour should have substantially changed. The only thing I can think is that they may now have made the sort operation more minimally logged as it's not required for recovery so you may see less TLog growth in Tempdb...
Have you got an identical copy of the database running on 2005 that you can test side by side?
September 5, 2010 at 9:50 am
Hi Howard, thanks for your response.
No, we don't have any servers that still run 2005, but your explanation makes the most sense to what is happening on the server. I know for a fact that the transaction log file in the user database was considerably larger and had been shrunk (due to a poor DBA decision) when we ported our production server from our old machine to the new machine. This would indicate why we hadn't seen this problem in years.
We've negotatiated the problem by simply introducing more log space, though I'd like to see Microsoft retract their statement that when SORT_IN_TEMPDB is used "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 is obviously not the case.
Thanks to everyone on their input here. I think the only thing to do from this point would be to ask MSFT to clarify the quoted text from this article: http://msdn.microsoft.com/en-us/library/ms184246(v=SQL.100).aspx. Perhaps I'll open a support request with them for this (though support requests opened with MSFT have often been a disappoinment for me, due to the seeming lack of dedication from the MSFT support engineers.)
Cheers,
Charley
September 7, 2010 at 10:47 am
Hi charles,
Just a though, but haven't you tried to switch to BULK_LOGGED before you run the index rebuild ? As you don't mention it, I'm wondering if there are any restriction that would prevent you going to bulk_logged (db mirroring first guess) ?
David B.
September 7, 2010 at 11:11 am
Hi David, Thanks for the post, I have considered switching to bulk-logged, but our business model requires us to be in full at all times. I suppose I should have mentioned that. And of course, that would require the addition of two whole lines of code to the SP, which is simply too much work for me to do. 😀 I'd much rather spend hours of time trying to figure out why something the documentation from MSFT says doesn't appear to be true.
FYI for those who might be interested, I reposted this issue a few hours ago over on the Technet SQL Server forum to see what folks over there might say. So far, no replies. Here's the link: http://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d2f4d5c5-4d4d-4e85-a3b1-e1cff2f2ec91
September 7, 2010 at 1:22 pm
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.
September 7, 2010 at 10:29 pm
Charley,
I don't think that switching to bulk-logged will solve your problem. We experienced similar situation, not as bad as your but close, we had the T-log grew to 100GIGs after index rebuilt, our database was 70 GIGs, I'm not sure how big our Index file was. One thing for sure is that index rebuild generates big T-log. I would look for the biggest cluster index file, trying to to see if rebuilding this particular index will increase the size of T-log. After identifying it, rewrite your index maintenance job excluding it and place is in a different time frame or step of your index maintenance job, make sure to truncate T-log after each index maintenance steps.
Hope it helps.
Hai
September 8, 2010 at 2:28 am
charles evans (9/7/2010)
Hi David, Thanks for the post, I have considered switching to bulk-logged, but our business model requires us to be in full at all times.
Maybe you need to be able to do point in time recovery at any time including during your maintenance windows ?
David B.
September 8, 2010 at 8:14 am
HI
I have a server with 8 Gb Ram I installed Sql server 2008 R2 on 64 bit platform Enterprise edition,in that for Sql server I allocated 6 GB of Ram I enable the AWE oprtion as well but in processing time it taking above 6 Gb ram like 7.gb or more after query completing its not release the memory how can I resolve this issue.
Actually when we running quries it taking the memory and memory increasing gradually after executing the query its not releasing the memory per example I set maximum 6 GB per Sql server then I run the query memory will increase gradually like 3.4 gb,3.7 gb, 4.5gb,and so on it will reach 7.56gb then after it steadily over there not come to down I am using X64 bit windows server 2008 sql server 2008 R2.
My system configuration is sql server 2008R2(10.0.3798) windows server 2008 X64 Ram is 8GB
September 8, 2010 at 8:38 am
Maybe you need to be able to do point in time recovery at any time including during your maintenance windows ?
Yes David, we do have to be able to perform point in time recovery at any time. Our maintenance windows are scheduled for when there is the least amount of user transactions hitting the database. We only have one production database server, so we're unable to take one offline while the maintenance is performed and switch back once maintenance is complete. So, our maintenance runs concurrently with some user transactions. I'm encompassing our ETL processing under the umbrella term "user transactions." Whether it's content import, scheduled reports, et cetera, we have user transactions occurring 24/7. Thus, we have to be able to restore to any point in time withing +/- ten minutes.
Thanks,
Charley
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply