Solved

Query expands tempdb in SQL Server

Posted on 2013-01-25
2
247 Views
Last Modified: 2013-01-28
I have a view that I am running through Management Studio in the query widow. I am selecting 1000 records. When I run it, the tempdb table grows very quickly and the query takes several minutes to run.  Tempdb can go from 1 mb to 1 gb in a few minutes.  The query uses COUNT(*) ,  GROUP BY and HAVING. All tables (6) are indexed and I reoganized all of the indexes while testing.  A web page that calls the view runs with no affect on tempdb.  No other views in this database have the problem.  Any idea why this would happen?

Thanks,

Steve
0
Comment
Question by:McLeanIS
2 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 225 total points
ID: 38820774
First you need to understand what tempdb is used for - among other things, sorts.

See: http://msdn.microsoft.com/en-us/library/ms190768.aspx 

If you're doing a group by, there's some implicit sorting in there in order to group the same items.

A 1GB tempdb doesn't seem terribly out of the ordinary to me (but then that's based upon my particular servers - I have no idea about how large your data set is).

However, a 1 MB tempfile seems terribly small, there's no room for it to do anything.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38823388
Why is your Tempdb so small?  It should be at least 1 GB.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

25 Experts available now in Live!

Get 1:1 Help Now