SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Non Clustered index on Primary key is not used by the execution plan and showing Table scan


Non Clustered index on Primary key is not used by the execution plan and showing Table scan

Author
Message
kbhanu15
kbhanu15
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1833 Visits: 344
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 :
Attachments
exec plan.png (9 views, 69.00 KB)
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5348 Visits: 2692
Please post the actual execution plan of the problematic query as a .sqlplan file attachment.
kbhanu15
kbhanu15
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1833 Visits: 344
pls find the original execution plan
Attachments
ORIGINAL.sqlplan (21 views, 390.00 KB)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41597 Visits: 20005
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
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5348 Visits: 2692
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 :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search