How to reduce the Clustered index seek cost

Question

How to reduce the Clustered index seek cost

kbhanu15

SSCarpal Tunnel

Points: 4498
More actions
September 2, 2014 at 5:33 am

#311167

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

tulipvioletproject Valued Member Points: 52 More actions · Answer 3

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/