My Experience with Query Store

I am writing this blog to share my experience with query store. I have used Query store couple of years ago. However, I didn’t explore much in that. But recently I completed Migration of SQL Server 2008 to SQL Server 2019 and there I dig more in query store.

So basically, Query store is the SQL server feature which has been introduced in SQL Server 2016 and use for capturing and storing Query plans for further analysis.

Working with Query store is an easy task. To enable query store, go to database property-->click on Query Store-->Change Operation Mode to Read Write.

On the same window, we can configure other settings as per our requirement.

Once query store enabled, we can monitor below queries.

Please check below Microsoft doc for best practices of query store.  

Best practices with Query Store - SQL Server | Microsoft Docs

I found query store useful for Query plan monitoring and comparing plans.

However, I faced interesting issues as well. That I would like to share.

After configuration of query store, for couple of days we didn’t face any issue to fetch reports. However, after a week, query store reports started loading slow. Extremely slow !.

At that time, we thought this feature is useless. On the other community sites, we found that same issue faced by many people. However, no solution and workaround provided.

After digging into the issue, we understand that, we have used legacy compatibility level (i.e., 100) for database where query store is enabled and as we know in each database compatibility level algorithm has changed. Here, why we used legacy compatibility level is the different story. We will see how we could overcome the query store issue. 

Generally, query store captures and store query plan and its metadata in below DMVs. And same being used while fetching the reports.

sys.query_store_plan

sys.query_store_query

sys.query_store_query_text

sys.query_store_runtime_stats

sys.query_store_wait_stats

sys.query_store_runtime_stats_interval

When we query above DMVs on the database which has compatibility level set to 100, there we found very slow response. However, when we changed database compatibility level to 150 then we received quick response.

However, in production we can’t change compatibility level without plan. Hence, we executed the query on database which has latest compatibly level.

Ex:

USE [tempdb]

SELECT q.query_id, qt.query_text_id, qt.query_sql_text,

    SUM(rs.count_executions) AS total_execution_count

FROM Fireworks.sys.query_store_query_text AS qt

JOIN Fireworks.sys.query_store_query AS q

    ON qt.query_text_id = q.query_text_id

JOIN Fireworks.sys.query_store_plan AS p

    ON q.query_id = p.query_id

JOIN Fireworks.sys.query_store_runtime_stats AS rs

    ON p.plan_id = rs.plan_id

GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text

ORDER BY total_execution_count DESC;

Here, Fireworks database is having compatibility level 100.

In this way we could able to fetch reports on time without any issue,

Hope you have enjoyed this.