Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

NC Index Seek 51% and select cost 0%? Expand / Collapse
Author
Message
Posted Saturday, July 13, 2013 6:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:17 PM
Points: 1,049, Visits: 2,957
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


  Post Attachments 
I21_VMD.sqlplan (17 views, 81.86 KB)
Post #1473290
Posted Saturday, July 13, 2013 12:54 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 42,493, Visits: 35,562
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 2008, MVP
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

Post #1473308
Posted Sunday, July 14, 2013 10:59 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:17 PM
Points: 1,049, Visits: 2,957
thanks for reply... I got the result 00:00:00 seconds after update stats in NC Clustred Index in RESULT Table..

Post #1473445
Posted Monday, July 15, 2013 2:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 7,137, Visits: 13,527
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1473523
Posted Monday, July 15, 2013 3:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:17 PM
Points: 1,049, Visits: 2,957
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


  Post Attachments 
New_Query.sqlplan (5 views, 74.30 KB)
Post #1473548
Posted Monday, July 15, 2013 3:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 42,493, Visits: 35,562
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 2008, MVP
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

Post #1473551
Posted Monday, July 15, 2013 3:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:17 PM
Points: 1,049, Visits: 2,957
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?
Post #1473553
Posted Monday, July 15, 2013 3:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 7,137, Visits: 13,527
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1473554
Posted Monday, July 15, 2013 4:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 42,493, Visits: 35,562
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 2008, MVP
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

Post #1473560
Posted Monday, July 15, 2013 4:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 42,493, Visits: 35,562
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 2008, MVP
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

Post #1473561
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse