April 15, 2014 at 11:17 pm
What is SORT_IN_TEMPDB ? what is the use of it?
BOL says
When SORT_IN_TEMPDB is not specified, the sort runs are stored in the destination filegroup. During the first phase of creating the index, the alternating reads of the base table pages and writes of the sort runs move the disk read-write heads from one area of the disk to another. The heads are in the data page area as the data pages are scanned. They move to an area of free space when the sort buffers fill and the current sort run has to be written to disk, then move back to the data page area as the table page scan is resumed. The read-write head movement is higher in the second phase. At that time the sort process is typically alternating reads from each sort run area. Both the sort runs and the new index pages are built in the destination filegroup, meaning that at the same time the database engine is spreading reads across the sort runs, it has to periodically jump to the index extents to write new index pages as they are filled.
If the SORT_IN_TEMPDB option is specified and tempdb is on a separate set of disks from the destination filegroup, then during the first phase the reads of the data pages occur on a different disk than the writes to the sort work area in tempdb. This means the disk reads of the data keys tend to proceed more serially across the disk, and the writes to the tempdb disk also tend to be serial, as do the writes to build the final index. Even if other users are using the database and accessing separate disk addresses, the overall pattern of reads and writes are more efficient when SORT_IN_TEMPDB is specified than when it is not.
1) Say i am having my tempdb in seperate drive
Ok what will happen if the SQL Server is restarted?Will again the indexes are recreated or how?
2) will there be gain if i put SORT_IN_TEMPDB?
3) Is it is good to put in the command in production server.
4) how much space is needed for a 5 GB Database for tempdb if SORT_IN_TEMPDB is enabled?
5) say for eg Table created and index created with SORT_IN_TEMPDB option.
SORT_IN_TEMPDB will not have any impact when there is a call on a where clause in a table.
April 15, 2014 at 11:55 pm
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."
April 16, 2014 at 12:35 am
Will it improve my query performance?
Say where condition is index column which is "SORT_IN_TEMPDB" option
April 16, 2014 at 1:37 am
What will happen when the server is restarted will index be recreated?
April 16, 2014 at 1:49 am
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
April 16, 2014 at 2:42 am
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."
April 16, 2014 at 2:49 am
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
April 16, 2014 at 3:16 am
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?
April 16, 2014 at 3:19 am
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.
April 16, 2014 at 3:19 am
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
April 16, 2014 at 3:20 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply