Unfortunately, this is not an apples-to-apples comparison because there are different clustered indexes on both versions, and that constitutes a significant difference. The *same* clustered index that was added to the new, partitioned version needs to be on the original before a comparison takes place. Having the same clustered index on the original will remove the key lookup on 40M records, the nested loop join, and the index scan, and the disparity will not be so exaggerated.
thanks! why the script generation creates cluster index and in the next line creates drop line for the same index?
This is very helpful
Unfortunately, this is not an apples-to-apples comparison because there are different clustered indexes on both versions, and that constitutes a significant difference. The *same* clustered index that was added to the new, partitioned version needs to be on the original before a comparison takes place. Having the same clustered index on the original will remove the key lookup on 40M records, the nested loop join, and the index scan, and the disparity will not be so exaggerated.
Does partitioning cause deadlock?
I have used system tables like you did, but not used NOLOCK
How to partition it by 3?
Superb