March 20, 2009 at 12:45 pm
I have an automated process that generates and runs the alter index rebuild statements for each index in my database based on fragmentation. It has been running fine for over one year. I am now looking into utilizing the SORT_IN_TEMPDB options since some of these tables are large. According to the documentation, you can utilize this option with the ONLINE = ON.
I tried some tests on a stand alone db monitoring the tempdb usage. I set the trigger levels to capture tempdb data from the dmv's using sys.dm_db_file_space_usage when the freespace dropped below 98% and it did not change.
I searched the web and came across this technical Article, SQL Server 2005 Online Index Operations. Reading the article it basically stated that using online operations would not trigger any activity in TEMPDB event if the SORT_IN_TEMPDB option was set.
Does anybody know why this is this way. Why allow the combination of ONLINE and SORT_IN_TEMPDB both ON if it does nothing?
The article mentioned that it does utilize TEMPDB when processing an non-clustered index with ONLINE = OFF. This I was able to verify.
March 20, 2009 at 2:31 pm
Because SQL Server devs assumed that we'd know what we're doing and would feed the commands only meaningful procs. In my case, boy were they mistaken!
There are lots of places where various SQL Server commands, procs, etc., will take mutually exclusive parameters, and will decide which one to ignore. They documented them, and left them that way, because it really doesn't cause any harm, I guess.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 20, 2009 at 2:49 pm
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/754a003f-fe51-4d10-975a-f6b8c04ebd35.htm
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b796c829-ef3a-405c-a784-48286d4fb2b9.htm
SORT_IN_TEMPDB & ONLINE=ON are independent of each other. If SORT_IN_TEMPDB is ON, then tempdb would be used if the operation cannot be completed in memory (refer above link). This would make the operation faster but tempdb would get bloated during this operation, so it would need space.
ONLINE=ON, in SQL 2005 EE only, Rebuild operation can be done online if this is set. If the index has BLOB data type , then ONLINE=ON will give an error otherwise it will work.
March 20, 2009 at 3:51 pm
OK, but ONLINE = ON and SORT_IN_TEMPDB = ON does sound like a valid combination that someone would want to try to improve performance. Even the Technical Paper does not say it is invalid, it just explains why it would not be used. No where in the help does it say it could only work with no clustered indexes with online = off. The whitepaper shows a table that displays tempdb activity only for this type of processing.
I am just trying to confirm that this is the case snd I am not missing anything .
March 20, 2009 at 3:55 pm
Regarding the index definitions and online processing. I am aware of the restrictions and have code to process those indexes only in ONLINE = NO mode.
The technical paper has a little more information than BOL. But I am just trying to ensure that I am not missing something.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply