KeyLookup Simulation

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

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?

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

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.