Solved

indexing and tempdb getting full

Posted on 2013-01-25
8
368 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 40

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 40

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

738 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