Forcing sorts to memory instead of tempdb

[bloke]

Hi There,

Is it possible to have SQL Server try to sort in memory first before looking at tempdb as an option. I have a relatively small database table ( in comparison to RAM) and it hits tempdb with many sorts. I was hoping to be able to have it look at memory as an option similar to say sort_area_size in Oracle.

Many Thanks

Chris

 

[sguslan]

For SQL 6.0 and 6.5 run the following sql commands from Query Analyzer: [not sure about 7.0 and 2000]

(where X is the value of tempdb in RAM, in MB)

sp_configure 'tempdb in ram', X
go

reconfigure
go
shutdown
go

Now restart SQL Server normally.

Please read on for more info on possible problems with re-configuring tempdb in RAM:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q166350&

 

[sguslan]

Searched SQL BooksOL and found that the above "tempdb in ram" configuration option is no longer supported in sql2000...