SORT_IN_TEMPDB ON as default

Question

SORT_IN_TEMPDB ON as default

jsqldba

SSChampion

Points: 11257
More actions
April 27, 2016 at 2:47 am

#307044

is there a way to make all new indexes default to SORT_IN_TEMPDB = ON?
can i change this option on existing indexes without actually rebuilding the index?
i've searched but can't find the right trick. :crazy:
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Not as far as I know. It's a property of the create or rebuild operation, not of the index itself. Therefore there wouldn't be a lot of point in specifying it in advance.

John

jsqldba SSChampion Points: 11257 More actions · Answer 2

ok that seems right. it looks like a stored property because in SSMS there is the SORT_IN_TEMPDB option in the index properties window. but it doesn't save this property when changed, only issues an immediate rebuild command (which seems rather dangerous, for an unsuspecting DBA who changes it on a very large table).

would it not be useful to have a global default value in SQL so that all index rebuilds are done in TEMPDB? seems so to me but there must be a well-thought-out reason by the MS tech staff that i am missing.

thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Maybe they decided that if they allow users to set such a default, too many people would end up blowing tempdb when they do their index maintenance. I think it's a good thing - if you really want it to be the default, you can always tweak your reindexing scripts to make it so.

John

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

1) Never, EVER use SSMS to do ANY form of DDL activity!!! You can use it to get things set the way you think you want them, but then you MUST generate the Script and review it CLOSELY!! There are all kinds of bugs and flaws in that thing when it comes to DDL.

2) It would be BAD to have an option to set SORT_IN_TEMPDB on, plus there are SOOOOO many other priorities for the SQL Server development teams. Everyone should be creating and managing indexes using scripts, and it is easy to make sure this option is always on in your environment if that is what you want.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service