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 the
data_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.