indexing and tempdb getting full

is there any correlation between the presence and proper implementation of index
to
less space utilized in tempdb?
or vice versa...
LVL 5
25112Asked:
Who is Participating?
 
Anthony PerkinsCommented:
this is the query.. do you have input anything about the query itself that may bloat the tempdb?
I reformatted it to make it readable and it was over 20K lines of code in that query.
It is a total mess and I suspect machine generated.  It is in critical need of refactoring.
0
 
lcohanDatabase AnalystCommented:
Yes, the SORT IN TEMPDB
http://msdn.microsoft.com/en-us/library/ms188281(v=sql.100).aspx

can do that however...you should not limit your tempdb and please see best practice at:


Webcast Video: Sizing Up the TempDB Database – Best Practices
http://www.brentozar.com/archive/2012/08/webcast-video-sizing-up-tempdb-database-best-practices/

More details at:
http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
0
 
25112Author Commented:
how can i check if any index is allowing 'sort in tempdb'.. i could not see it in sys.indexes
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
25112Author Commented:
this is the query.. do you have input anything about the query itself that may bloat the tempdb?
query.txt
0
 
lcohanDatabase AnalystCommented:
"do you have input anything about the query itself that may bloat the tempdb?"

As there are a lots of subqueries in it so implicit lots of record sets that SQL will build in tempdb the answer is yes is those record sets are large. Just look at the query plan (at least estimated one) and hover over it to see how large they are.
0
 
David ToddSenior DBACommented:
Hi,

Fairly large query - likely to be quite a lot of benefit in explicitly using some temp tables to simplify it.

The benefit is likely to be both in your ease of working on it (and colleagues in time to come maintaining it) and in SQL's ability to understand it and get a better plan.

HTH
  David
0
 
Scott PletcherSenior DBACommented:
SQL will use tempdb to hold intermediate table results, to do DISTINCT, GROUP BY and/or ORDER BY processing, and a thousand other things.

That monster query could easily cause a massive use of tempdb.

The query needs rewritten to reduce its overhead.
0
 
25112Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.