Post reply

Keylookup Simulation

  • February 16, 2011 at 1:33 am

    #388867

    Hi

    I am trying to simulate the Keylookup sceario in DimEmployee table in Adventurework db SQLSERVER 2008

    Index Details

    IX_DimEmployee_ParentEmployeeKey nonclustered located on PRIMARY ParentEmployeeKey

    IX_DimEmployee_SalesTerritoryKey nonclustered located on PRIMARY SalesTerritoryKey

    PK_DimEmployee_EmployeeKey clustered, unique, primary key located on PRIMARY EmployeeKey

    My Query is

    select ParentEmployeeKey,SalesTerritoryKey,title from DimEmployee where ParentEmployeeKey = 3 and SalesTerritoryKey = 11

    Here Title is Nonkey column, So i was exepecing The execution plan like Mergejoin,Indexseek(nonclustered) and key lookup.

    But result is diffrent, It executed with ClusteredIndexScan.

    Can anyone please clarify me whats wrong in that my simulation CONFIDENTIALITY NOTICE This e-mail message and any attachments are only for the use of the intended recipient and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, any disclosure, distribution or other use of this e-mail message or attachments is prohibited. If you have received this e-mail message in error, please delete and notify the sender immediately.

    Thank you.

    Jay

  • February 16, 2011 at 1:39 am

    #1287482

    I think you just copied from your email 🙂

    The reason is the index is not fulfill the where condition.

    select ParentEmployeeKey,SalesTerritoryKey,title from DimEmployee where ParentEmployeeKey = 3 and SalesTerritoryKey = 11

    You have to create the index to both the field ParentEmployeeKey,SalesTerritoryKey and cover the title also.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • February 16, 2011 at 1:51 am

    #1287487

    You are correct. My question here is I want to simulate the "Keylookup " Scenario.

  • February 16, 2011 at 3:22 am

    #1287515

    If the optimiser decides there are too many key lookups it will chose rather to use a clustered index scan as it will be faster.

    Use a more restrictive predicate to get fewer rows or force the index (for testing and experimentation only)

    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

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

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