3

I am following this guide: Monitoring Performance By Using the Query Store

  • I am using SSMS 2016 CTP3.2 and Sql Azure V12
  • I have enabled the query store on the database properties.
  • I can use the query performance insight on the azure portal.

But when I open the azure database on SMSS 2016 CTP3.2 and expand the database tree, the Query Store / Regressed Queries or any other options are available.

I want to be able to take the most IO consuming queries and analyze the execution plan.

Anything else I have to enable?

The relevant is_query_store_on column in sys.databases shows 0, but the database properties says it is enabled. I executed ALTER DATABASE <DB> SET QUERY_STORE = ON; but is_query_store_on still stays at 0.

The following query:

SELECT 
    actual_state, actual_state_desc, readonly_reason, 
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Shows actual_state = 2;

  • Ok, so you thought you enabled it, but haven't. If the portal is showing it's enabled but sys.databases doesn't agree, contact support. – Aaron Bertrand Jan 28 '16 at 21:41
4

This fixed my problem:

  • Disabling the query store by command:

    ALTER DATABASE <DBNAME> SET QUERY_STORE = OFF;
    
  • Then enabling it again fixed the problem.

    ALTER DATABASE <DBNAME> SET QUERY_STORE = ON;
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.