NC Index Seek 51% and select cost 0%?

Question

Post reply

NC Index Seek 51% and select cost 0%?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
July 13, 2013 at 6:07 am

#296381

Hi,
what could be issues, select command taking 50 sec for result..
as per actual Exec.plan dispaly NC Index Seek 51% and select cost 0%..
Pl. suggestion me..how to improve performance?
thanks
ananda

Viewing 15 posts - 1 through 15 (of 17 total)

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

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

Core of your problem

Estimated rows 1

Actual rows 2887406

That's on the index seek for the REsult table. Probably stale or missing stats, could be parameter sniffing, without more details pretty hard to say.

Fix that and there's a far better chance of a suitable exec plan.

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

thanks for reply... I got the result 00:00:00 seconds after update stats in NC Clustred Index in RESULT Table..

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

ananda.murugesan (7/14/2013)
thanks for reply... I got the result 00:00:00 seconds after update stats in NC Clustred Index in RESULT Table..

Can you post the actual plan?

Joins in the WHERE clause are not recommended, use ANSI joins instead. Your query can be rewritten like so:

SELECT

s.SAMPLED_DATE ,

s.SAMPLE_NUMBER ,

sp.[DESCRIPTION] as SAMPLING_POINT ,

r.NAME as COMPONENT ,

t.TAG_NAME as IP21_TAG ,

r.NUMERIC_ENTRY as APPROVED_RESULT ,

u.DISPLAY_STRING as UOM

FROM [SAMPLE] s

INNER JOIN RESULT r

ON r.SAMPLE_NUMBER = s.SAMPLE_NUMBER

INNER JOIN OPC_TAG t

ON t.ANALYSIS = r.ANALYSIS

AND t.SAMPLING_POINT = s.SAMPLING_POINT

INNER JOIN SAMPLING_POINT sp

ON sp.NAME = t.SAMPLING_POINT

INNER JOIN UNITS u

ON u.UNIT_CODE = r.UNITS

WHERE t.OPC_SERVER = @0

AND s.SAMPLED_DATE > = DATEADD ( MM , @1 , GETDATE ( ) )

AND s.STATUS = @2

AND r.STATUS = @3

AND s.ALIQUOT_GROUP IS NULL

^{“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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

I modified Query as per your suggestion and fetching result 00 seconds..But NC clustred index seek 50% and .. what could be issues?, Need to change index column? and all Inner Join seeks 0%

attched here actual plan

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

ananda.murugesan (7/15/2013)
But NC clustred index seek 50% and .. what could be issues?

And that's a problem why?

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

Problem is need to check other table stats and will update it. not in RESULT table stats.

as per query that well parameterized and again index seek 50 %.. please explain me?

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

ananda.murugesan (7/15/2013)
I modified Query as per your suggestion and fetching result 00 seconds..But NC clustred index seek 50% and .. what could be issues?, Need to change index column? and all Inner Join seeks 0%
attched here actual plan

As Gail says, why should that be a problem? The costs are relative - you won't get them all down to 0%!!

The route taken by the optimiser to fetch rows from the RESULT table could be improved by using a covering index and by including a couple more keys. Try this:

create a new index on the RESULT table with keys SAMPLE_NUMBER, STATUS and ANALYSIS,

and included columns UNITS, NAME, NUMERIC_ENTRY. Then check the plan - the order of the keys is significant and is tricky to get right on the first attempt.

Edit - removed RESULTNUMBER, it's the cluster key.

^{“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

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

ananda.murugesan (7/15/2013)
as per query that well parameterized and again index seek 50 %.. please explain me?

Well, the cost has to be somewhere. Percentages have to add to 100% or they're meaningless, so something has to have a non-zero cost percentage.

Is the query still a problem? If no, go find a query that is a problem and worry about that query.

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

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

ChrisM@Work (7/15/2013)
Edit - removed RESULTNUMBER, it's the cluster key.

Why? What if someone changes the clustered index?

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 10

thanks Gail & Chris

Query still there is no problem and working fine....

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

If you find the time to create the index I suggested, I'd be interested in seeing the execution plan 😉

^{“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

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

GilaMonster (7/15/2013)
ChrisM@Work (7/15/2013)
Edit - removed RESULTNUMBER, it's the cluster key.
Why? What if someone changes the clustered index?

It won't matter if OP creates the index I suggested. Since it's covering, no key lookup is required - hence no need for the cluster key to be output.

^{“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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 13

Hi ChrisM

RESULT Table having 3 NC index and 1 clustred index available already..

if created another one more NC index as you suggested, it will be overlapping on existing one.. pl suggest.

/****** Object: Index [IX_RESULT_SAMPLE_NUMBER] Script Date: 07/15/2013 16:35:47 ******/

CREATE NONCLUSTERED INDEX [IX_RESULT_SAMPLE_NUMBER] ON [dbo].[RESULT]

(

[SAMPLE_NUMBER] ASC

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

GO

/****** Object: Index [IX_RESULT_STATUS] Script Date: 07/15/2013 16:35:55 ******/

CREATE NONCLUSTERED INDEX [IX_RESULT_STATUS] ON [dbo].[RESULT]

(

[STATUS] ASC

)

INCLUDE ( [SAMPLE_NUMBER],

[ANALYSIS],

[NUMERIC_ENTRY],

[UNITS]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

/****** Object: Index [IX_RESULT_TEST_NUMBER] Script Date: 07/15/2013 16:36:13 ******/

CREATE NONCLUSTERED INDEX [IX_RESULT_TEST_NUMBER] ON [dbo].[RESULT]

(

[TEST_NUMBER] ASC

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

GO

/****** Object: Index [PK__RESULT__9AEED70F6CA3E9F7] Script Date: 07/15/2013 16:36:27 ******/

ALTER TABLE [dbo].[RESULT] ADD PRIMARY KEY CLUSTERED

(

[RESULT_NUMBER] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

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

None of the existing NC indexes are covering.

Create the new index. Change the order of the key columns if necessary.

Then monitor index usage, the cost of maintaining the indexes against the benefit of usage. Ask if you are unsure how to do this.

You may find that queries currently using IX_RESULT_SAMPLE_NUMBER or IX_RESULT_STATUS use the new index instead.

^{“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