SQL Server Performance

KeyLookup Simulation

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by Jayakumar.s, Feb 15, 2011.

  1. Jayakumar.s New Member


    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
  2. FrankKalis Moderator

    What about forcing the use of an index with a hint so that you get your lookup?
  3. mmarovic Active Member

    Nothing is wrong. Query optimizer just calculated execution cost to be lower with clustered index scan.I don't quite get it why you want specific execution plan, in other words: What is the purpose of the simulation?
  4. Jayakumar.s New Member

    Thanks for your reply. Just for analysing purpose only have raised this question. Can you please explain how its calculating execution cost and deciding to go with Clustered index scan for this ?
    B'cuase have seen some examples with similar sceario,There optimizer has chosen the key lookup. Just curious to know about his
  5. mmarovic Active Member

    Well, only microsft dev team could answer the question about how exactly cost is estimated and execution plan built. But AFAIK, they do not want.Long time ago, Joe Chang used Langrange or some other interpolation to reverse engineer sql server cost estimation parameters. If you want to learn more, it is good idea to google Joe's articles. Joe used to be active here, but I guess he is not any more.Anyway, query optimizer algorithms are complex and evolving with each new version and service pack, so you will never know exactly how it works.
  6. satya Moderator

Share This Page