Solved

indexing and tempdb getting full

Posted on 2013-01-25
8
363 Views
Last Modified: 2013-01-30
is there any correlation between the presence and proper implementation of index
to
less space utilized in tempdb?
or vice versa...
0
Comment
Question by:25112
8 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 200 total points
ID: 38819212
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
 
LVL 5

Author Comment

by:25112
ID: 38819978
how can i check if any index is allowing 'sort in tempdb'.. i could not see it in sys.indexes
0
 
LVL 5

Author Comment

by:25112
ID: 38820082
this is the query.. do you have input anything about the query itself that may bloat the tempdb?
query.txt
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 200 total points
ID: 38820248
"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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 100 total points
ID: 38820566
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 38831583
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 38832252
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
 
LVL 5

Author Comment

by:25112
ID: 38835797
thanks
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question