SQL Server Performance

Query Analyser Index Usage

Discussion in 'Performance Tuning for DBAs' started by NickC, Nov 30, 2002.

  1. NickC New Member

    Hi All,
    New to the site, so go easy on me!!
    DB: SQL 2000 Enterprise
    OS: Windows 2000 Advanced server.
    Server, IBM, 8*Pentium 900 xeons, 8GB RAM, 1 terabyte HDD.

    I've got a query that should use an index on a table, but isn't.
    Basic table structure is:

    Col1, Col2, Val1.

    Query Selects all three columns and sums column Val1, criteria on Col2

    Non clustered composite index on Col1 and Col2.

    Stats have been updated with fullscan, on both columns.

    The query analyser always selects a full table scan, (Currently 70 millin rows), when an index seek would seem to be the best course of action. However, if I remove the Val1 column from the query, and just return the columns Col1 and Col2, it does an index seek and returns the query almost instantaneously. Forcing an index seek multiplys the cost up expoentially. I'd appreciate any help anyone can give.

    Many Thanks

    Nick C
  2. bradmcgehee New Member

    Can you send a copy of the query and its text execution plan? When you say that you are "forcing an index seek", do you mean you are using a hint? And what do you mean by "multiplys the cost up exponentially"? I know these are lots of questions, but they will help me help you.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page