Post reply

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

  • March 4, 2014 at 12:14 am

    #290544

    Hi ALl,

    Hi Have a table with non clustered index on primary key

    index_name index_description index_keys

    PK_ResearchUserAlias_tblnonclustered, unique, primary key located on PRIMARYuserObjectId, 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 :

  • March 4, 2014 at 12:38 am

    #1693766

    Please post the actual execution plan of the problematic query as a .sqlplan file attachment.

  • March 4, 2014 at 1:52 am

    #1693779

    pls find the original execution plan

  • March 4, 2014 at 1:54 am

    #1693781

    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

  • March 4, 2014 at 3:14 am

    #1693795

    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 🙂

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

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