SQL Azure Database size - internal table plan_persist_plan

Question

We are having an issue with our SQL Azure databases, as they increase size, mainly because of one internal system table - plan_persist_plan.

In one of the databases, with a total size of 400Mb, the plan_persist_plan table represents by itself 310Mb and the rest are mainly user tables and indexes.

We calculate the database size with the following query:

SELECT sum(reserved_page_count)*8.0/1024 FROM sys.dm_db_partition_stats

To know each object size, we use:

SELECT OBJECT_NAME(object_id), reserved_page_count*8.0/1024, * FROM sys.dm_db_partition_stats order by reserved_page_count desc

Can anyone please explain:

The purpose of this table - plan_persist_plan?
How can we control it's size and possibly shrink it?

I don't have a good answer for this, and searches don't turn up anything. I've done a bunch of experimentation and I suspect it's something to do with Plan Store internals. Every database I've tested this on has had the plan_persist_plan as 0 pages. You might wait a bit and try again. If not, contacting Microsoft directly might be the way to go. — Grant Fritchey

Grant Fritchey Grant Fritchey · Accepted Answer · 2017-09-07T14:03:27

With experimentation, what I'm seeing is that plan_persist_plan is part of the Query Store internals. With Query Store disabled and cleared, there is no data in this table. Enabling Query Store and I immediately see data added to the table and it grows as the queries I run grow. I then disabled Query Store and the information in plan_persist_plan was still there. I then cleared Query Store and that information went away.

I think you're calculating the size a little wrong though. By default out of the box the max for query store is 100mb. Unless you've changed that I don't see how it's storing 300mb.

If you want to try this, run:

ALTER DATABASE MyDB SET QUERY_STORE CLEAR;

Substituting your database name.

SQL Azure Database size - internal table plan_persist_plan

1 Answers