?
Solved

indexing and tempdb getting full

Posted on 2013-01-25
8
Medium Priority
?
379 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 800 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 800 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 400 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 400 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 400 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

Independent Software Vendors: 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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

762 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