How to reduce the Clustered index seek cost

  • HI All,

    can you please provide the possible ways to reduce the clustered index seek cost in execution plan.

  • Firstly, why?

    Is it a problem? Costs in a plan have to add to 100%, so a single operator with a 'high' cost is not necessarily a problem. Is the query itself a problem?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sure, but suggestions will depend upon the execution plan for the query exhibiting the problem. Can you post it please? - actual not estimated.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • applying this you can reduce cluster performance.

    GO

    /****** Object: Index [IX_Stu] Script Date: 12/28/2009 11:11:43 ******/

    CREATE CLUSTERED INDEX [IX_Stu] ON [dbo].[stu]

    (

    [StuKey] ASC

    )WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    table (some columns omitted for brevity):

    CREATE TABLE [dbo].[stu](

    [StuCertKey] [int] IDENTITY(1,1) NOT NULL,

    [StuKey] [int] NULL

    CONSTRAINT [PK_Stu] PRIMARY KEY NONCLUSTERED

    (

    [StuCertKey] ASC

    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80) ON [PRIMARY]

    ) ON [PRIMARY]

    http://www.tulipvioletgurgaon.co.in/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply