select top 2000 from table_name cause tempdb full

Question

select top 2000 from table_name cause tempdb full

Louis Lam-391748

Ten Centuries

Points: 1083
More actions
May 19, 2010 at 10:50 am

#221267

Hi,
We have an application that queries all tables in a given database in MSSQL 2005 SP3. The goal is to find how data are pattern and report certain pattern to administrator. He way we are doing this is by a select statement per table like this.
select top 2000 col1, col2, .. coln
from table_name
Our application would take this data and analyze it. We come across some client database where this process cause their tempdb to be FULL. Their tempdb is already size like 30Gig. The database where this scan happen have tables with hundred of millions of rows.
Does a select top #### from table_name cause a full table scan? Could this be a cause of tempdb being full which customer claim is the case? Is there some efficient ways of getting records from MSSQL2005 without a tablescan if I don't know anything about the table and I just want to take some sample data from it? Like just give some random sample data for me to look at without causing any scan? Hopefully there is some solution.
Thank you very much,
Louis Lam

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you've got to look at the execution plan to be sure...

a simple SELECT TOP 2000 FROM SOMETABLE would probably not bloat temp db, but throw in an ORDER BY, and yea, it's got to get everything int he table, sort it, then take the top 2000...i'd expect THAT might bloat your tempDB.

to be sure , you have to get the actual execution plan of the actual statement being run to determine what the issue is...just by adding an index on the same columnas is being used in hte ORDER BY might relieve some of the bloat, i think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Stefan_G SSCertifiable Points: 6609 More actions · Answer 2

Maybe table_name is a view that contains an expensive join or order by.

If table_name is really a table and there is no order by involved there should be no problems

Louis Lam-391748 Ten Centuries Points: 1083 More actions · Answer 3

Thanks guy, it turn out that there was an expensive order by in the query. It was doing something like this

row_number() over (

order by "column_name")

We found a solution around it.

Thanks.