Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know that table scans are bad, and that every table should have a primary key and clustered index, but I'm wondering what might cause the spectacularly bad performance that I'm seeing below in SQL Server 2008 R2.

For testing's sake, I am doing a SELECT * FROM myTable on a table/heap with 1960 rows. The table has the following columns: (int, datetime, int, int, float, smallint, uniqueidentifier). There are no indexes. This table has had a long history of inserts and deletes.

I get the following stats from the table scan in the query plan:

<RelOp 
  AvgRowSize="57" 
  EstimateCPU="0.002313" 
  EstimateIO="74.4157" 
  EstimateRebinds="0" 
  EstimateRewinds="0" 
  EstimateRows="1960" 
  LogicalOp="Table Scan" 
  NodeId="0" 
  Parallel="false" 
  PhysicalOp="Table Scan" 
  EstimatedTotalSubtreeCost="74.418"
  TableCardinality="1960">

And from SET STATISTICS TIME ON:

Table 'myTable'. Scan count 1, logical reads 100458, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

What factors could lead to such a small number of small rows requiring such a massive number of reads (and have the query plan predict the same)?

share|improve this question
9  
    
^^^ Should be answer –  Eric Higgins Apr 17 '12 at 23:36
1  
@Eric: I notices that now an answer like that gets automatically flagged as 'trivial answer' and is moved to comments. –  Remus Rusanu Apr 18 '12 at 1:32
    
@Remus Hmm... "trivial" or not, that definitely looks like the answer. It appears I have 25 million ghost records in that table. Assuming I am too impatient for the ghost cleanup, would the best way to go be to copy what little is left to a new table, and drop the existing one? –  Joshdan Apr 18 '12 at 4:24
1  
You can do an ALTER TABLE ... REBUILD –  Remus Rusanu Apr 18 '12 at 5:26

1 Answer 1

up vote 2 down vote accepted

As per the comments:

See SQL Server “empty table” is slow after deleting all (12 million) records – Remus Rusanu

The answer to this question is: Ghosted Records, subsequently confirmed by the original poster. Posting here for completeness.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.