problem using sort_in_tempdb | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

problem using sort_in_tempdb

Hi, I’ve a table with huge data. I wanted to find out the time difference in creating the index on it using SORT_IN_TEMP_DB Option and with using SORT_IN_TEMP_DB option. When I’m creating the Indexes using the SORT_IN_TEMP_DB option, getting the shortage of memory error in TempDB. TempDb files are exist in C:/ drive and have very less memory in C:/ drive which is insufficient to create the Index. Is there any work around to create the Indexes using SORT_IN_TEMP_DB..? ok
As then error indicates this is a space issue.
Go to BOL and read the SPACE REQUIREMENTS section under the WITH SORT_IN_TEMP_DB option’s article.
That will clearly explain as to why you are getting the error.
Everything happens for your own good.

What is the edition of SQL used here?
If its standard then you may not have more than 2GB, if its Enterprise then it would help in higher memory configuration. The process involved in the SORT_IN_TEMP_DB option is:
PAD_INDEX tells SQL Server to leave some space on each page in the intermittent level of the index. PAD_INDEX is normally used in connection with FILLFACTOR, because PAD_INDEX is going to use a percentage specified by FILLFACTOR. FILLFACTOR is a much more important option when you’re creating an index. It specifies a percentage that indicates how full SQL Server should make the leaf level of an index page during an index creation.
Another important concept about fill factor is that if you are going to specify a new fillfactor, a FILLFACTOR of 0 is going to be used, which means your pages are going to differ up to 100 percent. An important concept to keep in mind using FILLFACTOR is to be sure of what kind of operation you’re using and that those updates are using SELECT statements. So it’s going to depend from business to business and operation to operation. Satya SKJ
Contributing Editor & Forums Moderator
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.