Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query expands tempdb in SQL Server

Posted on 2013-01-25
2
Medium Priority
?
254 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
[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
2 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 675 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

688 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