SQL Server Performance

newbie question related indexes

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Mar 4, 2010.

  1. shankbond New Member

    Hi,
    I have read a number of articles regarding nonclustered, clustered indexes, about included columns
    But now I have become confused Like I did a little test on a table with 5 columns and 1000 rows; I made nonclustered indexes on the columns, and I see that due to those columns on which there is no index there is a RID lookup( which I guess is expensive operation). but when I INCLUDED those columns in the index, it was an index seek operation.
    Now the point is that do I have to cover up all the columns in the index? or which columns should I index on ?
    I don't think there is a good point to cover up all the columns or indexes.
    Please help
  2. shankbond New Member

    Hi,
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[DummyTable2](
    [EmpId] [int] NULL,
    [EmpName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [c1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [c2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    --Script to load values in the table
    DECLARE @a INT
    SET @a=0;
    WHILE(@a<>10000)--loading 10000 records in the table
    BEGIN
    INSERT INTO dbo.DummyTable2 (EmpId, EmpName, c1, c2) VALUES (@a,'awkward'+CONVERT(VARCHAR(8), @a), 'c1'+CONVERT(VARCHAR(8), @a), 'c2'+CONVERT(VARCHAR(8), @a))
    SET @a=@a+1
    END
    Please confirm whether I am correct or not in getting at the Conclusion?
    After Running the select query when there was no index then query performed worst with subtree cost: .0535413
    When there was no index at all either clustered or nonclustered on the where clause included column then the cost is: .0535413
    When the where clause column had a clustered index on it then: .0032831
    but when there was nonclustered index on it then: .0065704 but the table still does not have a clustered index at all here
    finally when I had included the columns in the column specified in the select query then: .0032831
    Moral of the story:
    You should always have an index on the columns in where clause, if the index is clustered in the clause then the performance is best or if the index is nonclustered then there is possibility to improve the performance if it includes the columns queried in the index of the column in where clause.
    [8-|]( almost drained by doing that calculations)
    please help by telling whether My conclusions are correct or please correct me
  3. preethi Member

    Hi,
    1. You have seen RID lookup because, the table does not have a clustered index and you have some columns in select/where clause which is not covered by the index query used.
    2. When data of all the columns could be retrieved from an index (using index keys and include keys) just a seek will help you to find the indexes.
    3. Indexes come with the cost. Your insert, update & delete operations need to do the amendments to the indexes as well, so those operations will be slower.
    4. Indexes are faster because they are smaller. When you include all columns into an index, they just make "another table"
    5. You need to select the columns used very frequently in queries. They are good candidates for indexes.
    6. Keys used in where/group by clauses are better choices for first keys in the indexes.
    These are few points I can think of.

    Hope this helps.
  4. preethi Member

    Just to add, having a clustered index on table is better because, it has a double linked list of data pages which allows the data to be fetched faster.

Share This Page