SQL Server Performance

SQL Query Analyzer don't pick correct Index if i use Variable

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by waqar, Apr 29, 2008.

  1. waqar Member

    Hi,
    Today i came across a strange issue (although i have solved it but that is not the answer of my question).
    I have a table with following structure (following structure is only for question purpose, real table have about 15 columns)

    CREATE TABLE TBL1
    (
    ID INT IDENTITY(1,1),
    CODE VARCHAR(13),
    CODEValue VARCHAR(50),
    NROW INT)
    I have 2 Indexes
    1- ID is primary Key
    2- NRow has a NON-Clustered Index named as IDX.

    I have about 10 million records in TBL.
    Now if i perform a simple search

    SELECT
    TOP 100
    ID,CODE,CODEValue,NROW
    FROM
    TBL1
    WHERE
    NROW>567000

    Result is instant and Query analyzer show it is using index IDX with Index Seek.

    But if i perform following query with following code

    DECLARE @NROW INT
    SET @NROW=567000
    SELECT
    TOP 100
    ID,CODE,CODEValue,NROW
    FROM
    TBL1
    WHERE
    NROW>@NROW
    System takes 6~7 sec and use Primary Key as Index and perform TABLE scan.
    But if i change my query and use INDEX HINT as follows
    DECLARE @NROW INT
    SET @NROW=567000
    SELECT
    TOP 100
    ID,CODE,CODEValue,NROW
    FROM
    TBL1 WITH (INDEX(IDX))
    WHERE
    NROW>@NROW
    Result is instant and Query Analyzer showing Index Seek using IDX index.

    I am not sure why SQL is not picking correct Index, any input?

  2. MohammedU New Member

  3. satya Moderator

Share This Page