3

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)?

Improve this question
5
  • 9
    Ghosted Records. See SQL Server “empty table” is slow after deleting all (12 million) records
    – Remus Rusanu
    Commented Apr 17, 2012 at 22:58
  • ^^^ Should be answer
    – Eric Higgins
    Commented Apr 17, 2012 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
    Commented Apr 18, 2012 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
    Commented Apr 18, 2012 at 4:24
  • 1
    You can do an ALTER TABLE ... REBUILD
    – Remus Rusanu
    Commented Apr 18, 2012 at 5:26

1 Answer 1

Reset to default
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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