Changing the primary key from non-clustered to clustered on tables causing drop in Join performance

Tables in a database were heaps and had non-clustered primary keys. I changed the primary keys to clustered and saw a drop in 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)

avatar image By wanderer 0 asked May 25 at 04:38 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

The same question was reposted a few hours later, apparently because there were issuse with the account creation process. I will answer the more recent copy of the question.

Perhaps an admin that remove this question?

avatar image By Hugo Kornelis 271 answered May 26 at 09:01 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

We are Moving!


Follow this question



asked: May 25 at 04:38 AM

Seen: 20 times

Last Updated: May 26 at 09:01 AM