When Query Store Alterations Are Blocked

Erin Stellato gives us some helpful tips on Query Store:

If you are trying to execute an ALTER DATABASE command to change a Query Store option (e.g. turn it off, change a setting) and it is blocked, take note of the blocking session_id and what that session_id is executing.  If you are trying to execute this ALTER command right after a failover or restart, you are probably blocked by the Query Store data loading.

As a reminder, when a database with Query Store enabled starts up, it loads data from the Query Store internal tables into memory (this is an optimization to make specific capabilities of Query Store complete quickly).  In some cases this is a small amount of data, in other cases, it’s larger (potentially a few GB), and as such, it can take seconds or minutes to load.  I have seen this take over 30 minutes to load for a very large Query Store (over 50GB in size).

Erin has a story which ties this together, so check that out.

Related Posts

Finding The Slow Query In A Procedure

Erin Stellato shows us how we can find the slowest query within a stored procedure: Figuring out exactly what causes slow performance for a stored procedure can sometimes feel like trying to unravel a ball of Clark Griswold’s Christmas lights.  It’s not uncommon to see procedures with hundreds, even thousands of lines of code.  You […]

Read More

Using Query Store To Force Plans With Plan Guides On Them Already

Grant Fritchey creates a plan guide and then forces the plan in Query Store: If I look at the plan that is stored in Query Store, I’ll see the identical plan up above, including the PlanGuideDB and PlanGuideName properties. So, let’s force the plan using the values returned from the query above: 1 EXEC sys.sp_query_store_force_plan […]

Read More

Categories

August 2018
MTWTFSS
« Jul Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031