Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cgunner
Yak Posting Veteran
95 Posts |
 Posted - 2005-01-29 : 13:14:02
|
| I have been trying to find out information about the SORT_IN_TEMPDB option when creating indexes. I have tried to use the option but it does not seem to work or I am not getting the syntax right. Here is my example:CREATE CLUSTERED INDEX [IndexName] ON [dbo].[Table]([Field]) with SORT_IN_TEMPDB, FILLFACTOR = 100 ON [PRIMARY] GO |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-01-29 : 13:29:34
|
| How do you know it is not being used? How large is your table, how much RAM do you have? Maybe the sort is occuring in memory instead.btw, your syntax looks correct.-ec |
 |
|
|
cgunner
Yak Posting Veteran
95 Posts |
Posted - 2005-01-29 : 13:47:25
|
| The table has 310,000 records, I have 1 Gig of Ram and tembdb does not grow 1k. If you specify the option, I would think that it would use tempdb regardless. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-01-29 : 16:51:15
|
| It will use available RAM first, then the tempdb.As a test, you could reduce the amount of RAM given to SQL Server. Then you will see it use Tempdb.-ec |
|
|
|
cgunner
Yak Posting Veteran
95 Posts |
Posted - 2005-01-30 : 11:37:47
|
It works. I just created a cluster index on a table with 17 Million rows. It took 45 Min instead of the usual 2 Hrs. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-01-30 : 17:47:06
|
| one thing that I have done is to put tempdb on RAID10 disk instead of RAID5. another way of speeding up yoru indexes is to put them on another filegroup (instead of PRIMARY). You would then locate the index filegroup on RAID10 disk as well. Doing this is more expensive, but the performance gains are substantial with larger databases.-ec |
|
|
|
|
|
|
SORT_IN_TEMPDB