SQL Server Performance

How to get rid of the Clustered Index Scan

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Narine, May 22, 2008.

  1. Narine New Member

    Hi all,
    I have a table with 32 million rows.
    It has a clustered index on a non-unique column, ListingID.
    WHen I select from the table searching on a specific ListingID, execution plan shows a Clustered Index Scan.
    Any way to optimize it? The select statement is very slow.
    Thanks to all in advance!
    Narine
  2. alent1234 New Member

    how how many rows are returned? we had similar problems last year where the where column had a non-clustered index on it. around the 1.5 million row mark it would turn into an index scan instead of a seek. Few months ago we changed the column to a clustered index, but i can't remember the new threshold.
    with smaller tables i noticed this. if you return too many rows it scans instead of seeks.
    can you add any more columns to the where clause?
  3. Narine New Member

    Thanks Alen for the reply.
    I just found a problem in my code. THe ListingId is a varchar field and I wasn't using quotes around my parameter in the search criteria because it contains numeric values. Adding quotes made a huge difference, it became ten times faster and the execution plan shows Clustered Index Seek now.
    It must be the implicit conversion from INT to Varchar that was slowing down. Great lesson learned today, always use quotes around Varchar filed!
    Thanks
    Narine
  4. FrankKalis Moderator

    Can you please post your table structure and your query?

Share This Page