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_compression column 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.
Why is sys.partitions slow?
If you look at the definition for
sys.partitions, it is basically what Jake described – a
UNION ALL of all the columnstore and rowstore partitions, with THREE explicit references to
sys.sysrowsets (abbreviated source here):
CREATE VIEW sys.partitions AS WITH partitions_columnstore(...cols...) AS ( SELECT ...cols..., cmprlevel AS data_compression ... FROM sys.sysrowsets rs OUTER APPLY OpenRowset(TABLE ALUCOUNT, rs.rowsetid, 0, 0, 0) ct -------- *** ^^^^^^^^^^^^^^ *** LEFT JOIN sys.syspalvalues cl ... WHERE ... sysconv(bit, rs.status & 0x00010000) = 1 -- Consider only columnstore base indexes ), partitions_rowstore(...cols...) AS ( SELECT ...cols..., cmprlevel AS data_compression ... FROM sys.sysrowsets rs -------- *** ^^^^^^^^^^^^^^ *** LEFT JOIN sys.syspalvalues cl ... WHERE ... sysconv(bit, rs.status & 0x00010000) = 0 -- Ignore columnstore base indexes and orphaned rows. ) SELECT ...cols... from partitions_rowstore p OUTER APPLY OpenRowset(TABLE ALUCOUNT, p.partition_id, 0, 0, p.object_id) ct union all SELECT ...cols... FROM partitions_columnstore as P1 LEFT JOIN (SELECT ...cols... FROM sys.sysrowsets rs OUTER APPLY OpenRowset(TABLE ALUCOUNT, rs.rowsetid, 0, 0, 0) ct ------- *** ^^^^^^^^^^^^^^ *** ) ...
This view seems cobbled together, probably due to backward compatibility concerns. It could surely be rewritten to be more efficient, particularly to only reference the
TABLE ALUCOUNT objects once. But there's not much you or I can do about that right now.
cmprlevel comes from
sys.sysrowsets (an alias prefix on the column reference would have been helpful). You would hope that a predicate against a column there would logically happen before any
OUTER APPLY and could prevent one of the scans, but that's not what happens. Running the following simple query:
SELECT * FROM sys.partitions AS p INNER JOIN sys.objects AS o ON p.object_id = o.object_id WHERE o.is_ms_shipped = 0;
Yields the following plan when there are columnstore indexes in the databases (click to enlarge):
And the following plan when there are not (click to enlarge):
These are the same estimated plan, but SentryOne Plan Explorer is able to highlight when an operation is skipped at runtime. This happens for the third scan in the latter case, but I don't know that there's any way to reduce that runtime scan count further; the second scan happens even when the query returns zero rows.
In Jake's case, he has a lot of objects, so performing this scan even twice is noticeable, painful, and one time too many. And quite honestly I don't know if
TABLE ALUCOUNT, an internal and undocumented loopback call, has to also scan some of these bigger objects multiple times.
Looking back at the source, I wondered if there was any other predicate that could be passed to the view that could coerce the plan shape, but I really don't think there's anything that could have an impact.
Will another view work?
We could, however, try a different view altogether. I looked for other views that contained references to both
ALUCOUNT, and there are multiple that show up in the list, but only two are promising:
SELECT * FROM sys.internal_partitions AS p INNER JOIN sys.objects AS o ON p.object_id = o.object_id WHERE o.is_ms_shipped = 0;
But the plan was not much better (click to enlarge):
There are only two scans against
sys.sysrowsets this time, but the scans are irrelevant anyway because the query doesn't come close to producing the rows we're interested in. We only see rows for columnstore-related objects (as the documentation states).
sys.system_internals_partitions. I'm little wary about this, because it's unsupported (see the warning here), but bear with me a moment:
SELECT * FROM sys.system_internals_partitions AS p INNER JOIN sys.objects AS o ON p.object_id = o.object_id WHERE o.is_ms_shipped = 0;
In the database with columnstore indexes, there's a scan against
sys.sysschobjs, but now only one scan against
sys.sysrowsets (click to enlarge):
If we run the same query in the database with no columnstore indexes, the plan is even simpler, with a seek against
sys.sysschobjs (click to enlarge):
However, this isn't quite what we're after, or at least not quite what Jake was after, because it includes artifacts from columnstore indexes, as well. If we add these filters, the actual output now matches our earlier, much more expensive query:
SELECT * FROM sys.system_internals_partitions AS p INNER JOIN sys.objects AS o ON p.object_id = o.object_id WHERE o.is_ms_shipped = 0 AND p.is_columnstore = 0 AND p.is_orphaned = 0;
As a bonus, the scan against
sys.sysschobjs has become a seek even in the database with columnstore objects. Most of us won't notice that difference, but if you're in a scenario like Jake's, you just might (click to enlarge):
sys.system_internals_partitions exposes a different set of columns than
sys.partitions (some are completely different, others have new names) so, if you are consuming the output downstream, you'll have to adjust for those. You'll also want to validate that it returns all of the information you want across rowstore, memory-optimized, and columnstore indexes, and don't forget about those pesky heaps. And, finally, be ready for leaving out the
internals many, many times.
As I mentioned above, this system view is not officially supported, so its functionality may change at any time; it could also be moved under the Dedicated Administrator Connection (DAC), or removed from the product altogether. Feel free to use this approach if
sys.partitions is not working well for you but, please, make sure you have a backup plan. And make sure it is documented as something you regression test when you start testing future versions of SQL Server, or after you upgrade, just in case.