Solved

indexing and tempdb getting full

Posted on 2013-01-25
8
360 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
Comment Utility
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
Comment Utility
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
Comment Utility
this is the query.. do you have input anything about the query itself that may bloat the tempdb?
query.txt
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 200 total points
Comment Utility
"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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 100 total points
Comment Utility
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:ScottPletcher
ScottPletcher earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now