How to fix Table Scan…

Guru 2014-03-26 13:36:38

Here is the table definition:

Create table ETMN
EID int not null
FID int not null
TOID int not null


Primary Key

NDX_ETMIN_EID (Non-Clustered)
NDX_ETMIN_FID (Non-Clustered)
NDX_ETMIN_TOID (Non-Clustered)

select * from ETIM and when i tested with "Execution Plan" then I am getting table scan. The table has 178 rows. How do I fix to clustered or index scan?

Edward Norris 2014-03-26 13:46:21
Small tables and especially those that the optimizer believes the majority of rows are valid results, will typically result in a scan to save the overhead of navigating non-clustered trees/lookups (if not covering/includes).

Using a select * will always provide a table scan of some sort, as there is no criteria to require it to use an index.

It should say Clustered table scan, so the first thing is to verify that your PK is clustered.


Aaron Bertrand 2014-03-26 13:51:38
Hi Guru,

This query took 11 milliseconds. The table scan was among the least costly of all of the operations present. It happened because the table EquityTypeMinMax does not have a clustered index.

You could change the table scan to a clustered index scan by making the primary key clustered, or to an index scan by adding an index that only has the columns you need (FactorGroupID and TermOdometerID). I can't tell if you already have one of those, because you told us the index names, but not their definitions.

It seems really weird to me to have a three-column table, with 178 rows, that seems relatively static, and with three different non-clustered indexes – are they all single-column indexes? Why?

Since you only have indexes that either support reading all columns (the non-clustered primary key) or reading only individual columns, SQL Server makes the wise choice and just scans the whole table once, rather than scan two indexes once each (since that will mean 2 page I/Os rather than one). Since this scan reads a grand total of one page, I don't think changing a table scan to a clustered index scan or index scan is going to change anything about the performance of this query at all, since even if you have a two-column index, it is still going to require one full 8kb page to store/read (even if there will be more free space on that page).

I think you should focus on other things – scans are not always bad, and in this case it certainly isn't the cause of any problem (and again, since the query runs in 11 milliseconds, I question whether there is a problem here at all).