SQL Server Performance

When does a table become "big"?

Discussion in 'SQL Server 2005 General Developer Questions' started by matt3.5, Sep 10, 2008.

  1. matt3.5 New Member

    I have been reading some articles on the site about index selection etc and keep reading the phrase "an index scan may be better if many rows are returned or if the table is small".
    However, how small is small? This may seem like a stupid question, but if you have a 200gb database then a table with 5000 rows and 10 columns could be called "small". So I was wondering what the general rule of thumb would be for when an index scan is not acceptable - a table with 100 rows? 1000? 10,000? x 10, 20 columns etc?
    How about a a clustered index scan on a junction table? This would be very narrow - 2 int columns, but could have many rows.
    Just curious
  2. martins New Member

    I think that the answer to your question will be closely related to the environment. If you have plenty of memory and fast disks, then scanning a 100k table would be nothing...but try and do the same on a small box or server and the outcome changes significantly.
    In my opinion anything with less than 10k rows are small, and shouldn't really need non-clustered indexes unless it is used to join with large tables. You should still have a clustered index though.
    I wouldn't relate the size of a table in comparison with the database size, but rather look at usage and the rest of the environment.
  3. matt3.5 New Member

    Thanks Martins, that's interesting. You see a database I am working on at the moment has a table that is only ever going to have 2-300 rows x 18 columns, it will not grow beyond this due to the particular business case. I have clustered index scans going on with this table, the optimizer seems to refuse to pick up an index to 'seek' wherever I place it, I'm looking at selectivity and estimated page sizes, but my efforts so far are proving a bit fruitless and I am starting to think it's just because the table is small.
    On the other hand I have a monolithic website users table but have got pure clustered index seeks on that, so I guess that's the big fish dealt with as far as disk i/o etc is concerned.
  4. martins New Member

    I think unless that table will be used to join a very large table on one of the 18 columns only, and that column is not already in the clustered index, the optimizer might never choose to use a non-clustered index.
    You will probably also find that the whole table can be loaded into memory at one time, so it is faster for the query engine to scan the clustered index than it would be to do a non-clustered seek and a bookmark lookup.
    I wouldn't be concerned with table or clustered index scans in small tables, unless performance is suffering and all the other tables involved have the appropriate indexes.

Share This Page