Changing the primary key from non-clustered to clustered on tables causing drop in Join performance
This query is joining tables on clustered index keys and taking 10 secs and doing a lot more 'worktable' logical reads than a exactly same query joining the tables with nonclustered primary keys on the same columns which took 5 seconds and a lot less 'worktable' logical reads. Joins are using primary-key/foreign-key columns. Please let me know what is going wrong here that is causing performance to drop.
I will attach the Non-clustered primary keys query in a second post(NonClusteredPrimarykeys.pesession)
Perhaps an admin that remove this question?