October 21, 2009 at 11:56 am
Which system table has the info, as to which indexes have the feature to do 'SORT_IN_TEMPDB'.
thanks
Dan.
October 22, 2009 at 5:27 am
I don't think its stored in any table. Is this an interview question?
MJ
October 22, 2009 at 5:40 am
Strange interview question if it is lol
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 22, 2009 at 5:53 am
SORT_IN_TEMPDB, is an index setting/option.
as far as i know, none of the tables/catalogues/metadata show if an index was created using this option. if that is what you are trying to find out.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 22, 2009 at 6:15 am
It is an option in Index. I have always been curious to check how much indexes are that way, because the TempDb is already strained with some queries the developers are fixing, so right night we do not want anyone to create indexes with this option. But how do we search for indexes with this option, if it is not stored in sysindexes or any system tables?
Thanks
Dan
October 22, 2009 at 12:26 pm
I got my answer here..
http://www.sqlservercentral.com/Forums/Topic739604-360-1.aspx
October 23, 2009 at 1:28 am
The same answer that has been given in this topic, it is not stored anywhere. thanks for confirming it.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 23, 2009 at 8:23 am
Silverfox,
You are right, you had already given me the right answer. I guess I was in disbelief. When I saw BOL quoted, I was humbled to accept an answer.
Appreciate your input very much
Dan
October 23, 2009 at 9:28 am
I think SORT_IN_TEMPDB only uses the tempdb when the index is created or when it is being rebuilt. It isn't used when quires are run against the index.
In what sort of ways is your tempDB strained?
Have you got the option of creating extra tempdb files and or moving them on to their own separate drive?
Seth
October 23, 2009 at 12:07 pm
Seth
Some developer do their big queries with sort in tempdb option, and the tempdb bloats up big time. that was the concern.
Dan
October 26, 2009 at 3:07 am
Hi Dan
I appreciate developers using temp but that is not because the index has sortintemp enabled. It is going to be because they are creating and using tempoary tables.
Seth
October 26, 2009 at 5:49 am
Seth
yes, another major use of TempDB; but I guess a developer can go overboard on using too many of TempTables, too, right, and thus bloating the TempDB.
Dan
October 26, 2009 at 5:53 am
Also data imports will use tempdb (espically Bulk inserts) - but the point I want to make was: sortintempdb for an index will only affect tempdb when they are building or rebuilding the index etc - it won't hit tempdb if they are querying the index.
Seth
October 26, 2009 at 6:06 am
Seth,
sometimes, a long running query with heavy order & grouping, bloats out TempDB. So I was thinking TempDB is the temp storage for the processor to order the results, also.
Dan
October 26, 2009 at 6:15 am
Hi Dan
Sort will probably use TempdB - but normally having an up-to-date index and stats will remove the need to do this as they already have a record of the order.
I am happy to be corrected here (I answer partly to help and partly to learn):
I think a large view (or query) with order by on a column that does not have an index or has an index with out of date stats based on it will use tempdb.
Seth
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply