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

Non Clustered index on Primary key is not used by the execution plan and showing Table scan Expand / Collapse
Author
Message
Posted Tuesday, March 4, 2014 12:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:07 AM
Points: 970, Visits: 260
Hi ALl,

Hi Have a table with non clustered index on primary key

index_name index_description index_keys
PK_ResearchUserAlias_tbl nonclustered, unique, primary key located on PRIMARY userObjectId, researchContributorId

query :
select ep.*
,rc.brokerageFirmFlag
,rc.independentFirmFlag
,rc.quantitativeFirmFlag
,cast(isnull(my.ExcludeFlag,0) as bit) as ExcludeFlag
,cast(isnull(my.myFlag,0) as bit) as myFlag
,cast(case when ep.researchContentViewTypeId = 1 then 0 else 1 end as bit) as partialAccessFlag
,cast(case when ep.researchProductSalesModelId in (2,3,4) then 1 else 0 end as bit) as afterMarketFlag
,cast(case when rua.researchContributorId is null or ep.researchProductSalesModelId in (2,3,4) then 0 else rua.userIsBound end as bit) as boundFlag
,cast(isnull(rc.supportsLinkbacks,0) as bit) as supportsLinkbackFlag
,cast(isnull(rc.isBindingContributor,0) as bit) as bindingContributorFlag
,@useCapCache as capCacheFlag
from #entitledProducts ep
inner join ResearchContributor_tbl rc (nolock)
on ep.researchContributorId = rc.researchContributorId
left outer join ResearchUserAlias_tbl rua WITH(INDEX(PK_ResearchUserAlias_tbl))
on rua.researchContributorId = ep.researchContributorId
and rua.userObjectId = ep.userObjectId

left outer join @my my
on my.UserObjectId = ep.userObjectId
and my.researchContributorId = ep.researchContributorId

execution plan :



  Post Attachments 
exec plan.png (6 views, 69.90 KB)
Post #1547170
Posted Tuesday, March 4, 2014 12:38 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 701, Visits: 1,260
Please post the actual execution plan of the problematic query as a .sqlplan file attachment.
Post #1547180
Posted Tuesday, March 4, 2014 1:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:07 AM
Points: 970, Visits: 260
pls find the original execution plan


  Post Attachments 
ORIGINAL.sqlplan (13 views, 390.56 KB)
Post #1547198
Posted Tuesday, March 4, 2014 1:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:35 AM
Points: 7,210, Visits: 13,675
Remove the index hint from the query whilst you are still testing and developing. Index hints are used when you've exhausted other, simpler, possibilities.
Add rua.userIsBound to the index as an INCLUDE column.
Then post the actual execution plan as a .sqlplan attachment.


“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 #1547200
Posted Tuesday, March 4, 2014 3:14 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 701, Visits: 1,260
ChrisM@Work (3/4/2014)
Remove the index hint from the query whilst you are still testing and developing. Index hints are used when you've exhausted other, simpler, possibilities.
Add rua.userIsBound to the index as an INCLUDE column.
Then post the actual execution plan as a .sqlplan attachment.

+1,

"Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
Post #1547216
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse