SORT_IN_TEMPDB

Question

SORT_IN_TEMPDB

Viewing 10 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27164 More actions · Answer 1

When SORT_IN_TEMPDB option is ON SQL Server uses tempdb to store the intermediate sort results which are used to build the index while reindexing.

It is always good to have TEMPDB on different drive.

Howmuch space required is depend on the database structure specifically indexes.

you need to execute it in test environment to have rough idea.

Also it depends on the environment if it can be used this option in PROD or not.

On query it will not have any impact as this is related to index.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

yuvipoy SSChampion Points: 10469 More actions · Answer 2

Will it improve my query performance?

Say where condition is index column which is "SORT_IN_TEMPDB" option

yuvipoy SSChampion Points: 10469 More actions · Answer 3

What will happen when the server is restarted will index be recreated?

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 4

If your tempdb is on separate disks, then use it as much as possible.

You can also add some files each with equal size and equal auto-growth, and it will alleviate possibly existing allocation contention.

Well configured tempdb brings performance gains.

If you read and understand http://technet.microsoft.com/en-us/library/ms345368(v=sql.105).aspx and https://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/

you'll see the big benefit of a well configured tempdb

Regards,

Igor

Igor Micev,My blog: www.igormicev.com

free_mascot One Orange Chip Points: 27164 More actions · Answer 5

yuvipoy (4/16/2014)
What will happen when the server is restarted will index be recreated?

NO. There is no relation between server restart and index.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

yuvipoy (4/16/2014)
Will it improve my query performance?
Say where condition is index column which is "SORT_IN_TEMPDB" option

No.

The option affects where SQL allocated sort space during and only during an index rebuild. It has no effect at all after the rebuilds has completed, it has no effect on the resultant index. Hence it has nothing to do with query performance.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

yuvipoy SSChampion Points: 10469 More actions · Answer 7

free_mascot (4/16/2014)
yuvipoy (4/16/2014)
What will happen when the server is restarted will index be recreated?
NO. There is no relation between server restart and index.

where does these index will resides if we set SORT_IN_TEMPDB?

yuvipoy SSChampion Points: 10469 More actions · Answer 8

GilaMonster (4/16/2014)
No.
The option affects where SQL allocated sort space during and only during an index rebuild. It has no effect at all after the rebuilds has completed, it has no effect on the resultant index. Hence it has nothing to do with query performance.

Then why peoples are giving suggestion to have SORT_IN_TEMPDB option.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

yuvipoy (4/16/2014)
where does these index will resides if we set SORT_IN_TEMPDB?

In the user database, where indexes always reside.

The Sort in TempDB option changes where SQL allocates temporary work space from, during the index rebuild. It is only applicable during the index rebuild. The resultant index is identical to one built without that option. It has no permanent effect on the index, database or anything else.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

yuvipoy (4/16/2014)
Then why peoples are giving suggestion to have SORT_IN_TEMPDB option.

Because it means that SQL uses TempDB to allocate that temporary work space, not your user database. It means you need less free space in your user database for the index rebuild (and more free space in TempDB)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass