SQL Server Performance

Index Usage

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by dataczar, Apr 8, 2009.

  1. dataczar New Member

    I have the following table:
    CREATE TABLE [dbo].[POLICIES](
    [policy_id] [int] NOT NULL,
    [row_begin_date] [datetime] NOT NULL,
    [row_end_date] [datetime] NOT NULL CONSTRAINT [DF_POLICIES_row_end_date] DEFAULT ('9999-12-31'),
    [policy_number] [varchar](25) NOT NULL,
    [effective_date] [datetime] NULL,
    [termination_date] [datetime] NULL,
    [customer_id] [int] NULL,
    [company_id] [int] NOT NULL,
    [product_id] [int] NOT NULL,
    [policy_form_id] [int] NULL,
    [renewability_code] [int] NULL,
    [base_policy_type_code] [varchar](4) NOT NULL,
    [audit_id] [int] NOT NULL,
    CONSTRAINT [PK_POLICIES] PRIMARY KEY CLUSTERED
    (
    [policy_id] ASC,
    [row_begin_date] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    When I run the following query on the table it is doing a Clustered Index Scan and I am wondering if there is anything I can do about it.
    select *
    from policies
    where getdate() between row_begin_date and row_end_date
    Any help on this would be appreciated. This query comes back quickly, but when this table is joined to other tables that are using row_begin and row_end dates then they are all performing a Clustered Index Scan, which is effecting performance.
  2. FrankKalis Moderator

    Do you really need all columns from that table or can you probably create a nonclustered index for the columns you need? Also, have a look a the INCLUDE() clause for indices.
  3. satya Moderator

    The SQL Server optimizer is built on the assumption that there is no data correlation between indexes and hence assumes that the cost in physical IO of using a non-clustered index to retrieve the data row to be 1 physical IO operation for every qualifying result row.
    To optimize further the queires you can do either or all below:
    • Reorder index key columns so that the key column with the highest cardinality is the first column of the index.
      • Create indexes to cover the queries. As Frank referred about multiple single or multi-column indexes can be combined to make a covering index.
        • Add index hints to the queries.
  4. dataczar New Member

    I do not need all the columns when I am doing the select.
    I would get the Clustered Index Scan even if I had this as the query:
    select policy_id from policies where '2009-04-09' between row_begin_date and row_end_date
  5. rohit2900 Member

    Create composite index on row_begin_date and row_end_date and include policy_id... this should work....
  6. dataczar New Member

    That does work and causes a seek on the nonclustered index. However, as soon as I add another field to the select then I am back to a scan.
    Would I need to include all the columns?
  7. satya Moderator

Share This Page