August 24, 2016 at 1:00 am
Hi Experts,
We have a weekly rebuild index task which includes SORT_IN_TEMPDB=ON , when we create index do we need to specifically mention SORT_IN_TEMPDB or what mentiioned in rebuild task is fine also ONLINE=ON.
August 24, 2016 at 4:33 am
Depends whether you want the index creation to use TempDB for sorting or not.
That option isn't persisted anywhere, it's effective only for the create/alter that it's specified on and no other.
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
August 25, 2016 at 1:16 am
GilaMonster (8/24/2016)
Depends whether you want the index creation to use TempDB for sorting or not.That option isn't persisted anywhere, it's effective only for the create/alter that it's specified on and no other.
Thanks Gail. It is used only when the index is created,right?
When you say ALTER will the ALTER index Rebuild \reorg have effect on that? say I have not specified SORT_IN_TEMPDB=ON ,online=on option in Index maintenance task?
August 25, 2016 at 5:13 am
If you specify SORT_IN_TEMPDB = on for an index CREATE or ALTER, then that CREATE or ALTER will sort in TempDB. If you do not specify it for an index CREATE or ALTER, then that CREATE or ALTER will not sort in TempDB.
The option is NOT persisted anywhere.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply