Aaron Bertrand tries to disentangle a query:
This question was posted to #sqlhelp by Jake Manske, and it was brought to my attention by Erik Darling.
I don’t recall ever having a performance issue with
sys.partitions. My initial thought (echoed by Joey D’Antoni) was that a filter on thedata_compressioncolumn should avoid the redundant scan, and reduce query runtime by about half. However, this predicate doesn’t get pushed down, and the reason why takes a bit of unpacking.
Click through for the results of Aaron’s research.