Issue with Query Store Showing “Restricted Text” in Azure SQL Managed Instance

Question

I am experiencing an issue with Azure SQL Managed Instance where some of the long-running queries in Query Store are displayed as “Restricted Text” instead of the actual query text.

Details:

Environment: Azure SQL Managed Instance

I have admin access, but I am still unable to view the full query text.

This behavior is impacting our ability to troubleshoot and optimize performance.

Can someone please help me understand:

1. Why Query Store is showing queries as “Restricted Text”?

Is there any configuration or permission setting that needs to be adjusted to view the actual query text?

Thank you for your assistance.

Answer 1

The issue of Query Store displaying queries as "Restricted Text" in Azure SQL Managed Instance typically occurs due to permission settings. Even if you have admin access, you may need specific permissions to view the full query text.

In Azure SQL Managed Instance, the following permissions are required to view query text in Query Performance Insight:

1. Owner, Contributor, SQL DB Contributor, or SQL Server Contributor permissions are necessary to view the query text.

If you are not seeing the query text, ensure that you have one of these roles assigned. Additionally, if the Query Store is not properly configured, it may also lead to restricted access to query details. You can check the configuration settings and ensure that Query Store is active on your database.

If you still encounter issues after verifying permissions and configuration, consider reviewing the Query Store settings or consulting Azure documentation for further troubleshooting steps.

Answer 2

Hi Aditya Rangineni,
It looks like you're encountering a common issue with the Query Store in Azure SQL Managed Instance where some of your long-running queries are showing as “Restricted Text.” This can definitely be frustrating, especially when troubleshooting and optimizing performance.

Why "Restricted Text"?

The Query Store typically shows queries as “Restricted Text” for security reasons. Here are a few possible reasons for this:

1. Permissions: Even though you have admin access, it's worth double-checking the specific permissions set for viewing query text in the Azure SQL instance. To see full query text details, ensure you have the required Azure role-based access control (RBAC) permissions, which include:
   • Owner, Contributor, SQL Database Contributor, or SQL Server Contributor permissions are required to view the query text.
2. Query Store Configuration: The Query Store must be correctly configured to capture and store all queries. If it is in a read-only state due to exceeding its maximum storage size or not being optimally configured, it may restrict visibility into the query texts.

Steps to Consider

Here are some steps you can take to possibly resolve the issue:

1. Check Query Store Status: Run the following T-SQL command to check the current status of the Query Store:

   
      SELECT * FROM sys.database_query_store_options;
   
   

   This will give you insights into whether the Query Store is enabled and in what state. If it’s in a read-only state, you might need to increase the maximum storage size or clear some data.
2. Enable Query Store (if not enabled): Although Query Store is enabled by default, if it isn't, enable it with the following command:

   
      ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
   
   

3. Adjust Storage Size: If the Query Store is reaching its max size, you can increase it:

   
      ALTER DATABASE <database_name> SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);
   
   

4. Set Cleanup Policy: To maintain optimal performance, set the cleanup policy and retention correctly. For example:

   
      ALTER DATABASE <database_name> SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));
   
   

5. Capture Policy: If you have a high frequency of ad hoc queries, consider setting a custom capture policy:

   
      ALTER DATABASE <database_name>
   
      SET QUERY_STORE = ON 
   
      (OPERATION_MODE = READ_WRITE, 
   
       QUERY_CAPTURE_MODE = CUSTOM, 
   
       QUERY_CAPTURE_POLICY = (TOTAL_EXECUTION_CPU_TIME_MS = 1000, 
   
                               EXECUTION_COUNT = 30)); 
   
   

Follow-Up Questions

To assist you better, here are a few questions:

• Could you confirm the current permissions associated with your user account in the Azure portal?
• Has there been any recent change in the database configuration or service tier?
• Are there any specific queries that consistently appear as "Restricted Text"?

Hope this helps clear things up! Let me know if you have any more questions!

References

• Resolve Query Store issues in Azure SQL Database
• Permissions for Query Performance Insight
• Optimize the Query Store configuration

Answer 3

I find it difficult to believe that this has anything to do with permissions. Least of RBAC and that jazz.

Rather, I would guess that this is because the SQL text is assumed to include a password or other secrecy. The documentation for the column sys.query_store_query_text.has_restricted_text says Query text contains a password or other unmentionable words.