SQL Server Performance

"clustered index seek" vs "index seek" - DTA tells me to create an new nonclustered index but I don't know why...

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by gregbalajewicz, Sep 23, 2008.

  1. gregbalajewicz New Member

    Hello,
    I am optimizing a querry, for this simple table:
    • ID int
    • NAME nvarchar(25)
    • (a few other columns not participating in the query)
    Clustered Index on ID column exists.
    In the querry, I am joining this table with another table on the ID column and returning ID and NAME. Returning about 30 rows out of 3000 rows in the table
    I would think that the Clustered Index would do well but DTA suggest i create a new index:
    CREATE
    NONCLUSTERED INDEX [somename]
    (
    [ID]
    ASC
    )INCLUDE
    ( [Name])
    and sure enough, the querry plan does use this index rather than the clustered one.
    I am very interested why would this be.... Could not find anything on this topic anywhere.
  2. satya Moderator

    How does that query look like, can you post that sample code?
  3. sotn New Member

    A covering non clustered seekable index is (almost) always the fastest way to get to the data as it is very narrow and you are only returning 1% of data.
    Another performance point, does your [name] field need to be nvarchar and not varchar ?
    I've seen lots of implementations where varchar is the right choice but nvarchar has been used thereby doubling the space.
  4. rohit2900 Member

    If you are using clustered index for querying the result set then I don't think that you need to create a extra non clustered covering index on the same column. As covering index is created to get rid of the book mark look up physical operator and as we all know that clustered index have data pages at the leaf level so you don't need to create that index.
    And one more thing as you said you are joining it with some other table on ID column. Can you cross check that ID col in other table is also indexed.
    Can you post the tables structure and the select query?
  5. rohit2900 Member

    HI...
    I'm having a small doubt as what all coulmns my index should cover and in what order. I'll explain my ques with below ex.
    I'm having two tables t1, t2 and I'm using below query to fetch the data.
    select t1.col1, t1.col2, t2.col1, t2.col2
    from t1
    inner join t2 on t1.col3 = t2.col3
    where t1.col4 = ... and t2.col4 = ...
    Now if I'm creating a covering index on these tables then what columns should I create index and which column I keep in include part.
  6. satya Moderator

Share This Page