Return to Question

I'm beginning with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It just keeps saying "Waiting" for hours and hours. Query on the background (see below for an example) is not able to finish and is consuming lot of CPU. No matter how am I changing configuration options, it keeps waiting and waiting (even last hour report). Is this the reality of Query Store all over? Sounds being such a great tool but is totally unusable in real?

My Query Store size is about 1,7 GB. I'm collecting 7 day behind, in 1 hour interval and Auto Capture mode - so it seems to be reasonable settings, to me.

This is an example of a query on the background which is never finished:

SELECT TOP (@results_row_count) p.query_id query_id, q.object_id object_id, ISNULL(OBJECT_NAME(q.object_id),'') object_name, qt.query_sql_text query_sql_text, ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration, SUM(rs.count_executions) count_executions, COUNT(distinct p.plan_id) num_plans FROM sys.query_store_runtime_stats rs JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id JOIN sys.query_store_query q ON q.query_id = p.query_id JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time) GROUP BY p.query_id, qt.query_sql_text, q.object_id HAVING COUNT(distinct p.plan_id) >= 1 ORDER BY total_duration DESC 

Any ideas how to make it operational?

EDITORIAL: I have opened a case with Microsoft about this. We didn't solve it but came to a workaround. If I change Query Store Capture Mode to All (not using Auto mode) - then things go fine and the reports are generated in a timely fashion.

EDITORIAL #2 This has come to a completely different issue. The problem is not the Capture Mode but Legacy Cardinality Estimator which the database is running under. Please vote for this Azure Feedback: https://feedback.azure.com/forums/908035-sql-server/suggestions/37971781-slow-or-non-operational-query-store-under-legacy-c

I'm beginning with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It just keeps saying "Waiting" for hours and hours. Query on the background (see below for an example) is not able to finish and is consuming lot of CPU. No matter how am I changing configuration options, it keeps waiting and waiting (even last hour report). Is this the reality of Query Store all over? Sounds being such a great tool but is totally unusable in real?

My Query Store size is about 1,7 GB. I'm collecting 7 day behind, in 1 hour interval and Auto Capture mode - so it seems to be reasonable settings, to me.

This is an example of a query on the background which is never finished:

SELECT TOP (@results_row_count) p.query_id query_id, q.object_id object_id, ISNULL(OBJECT_NAME(q.object_id),'') object_name, qt.query_sql_text query_sql_text, ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration, SUM(rs.count_executions) count_executions, COUNT(distinct p.plan_id) num_plans FROM sys.query_store_runtime_stats rs JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id JOIN sys.query_store_query q ON q.query_id = p.query_id JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time) GROUP BY p.query_id, qt.query_sql_text, q.object_id HAVING COUNT(distinct p.plan_id) >= 1 ORDER BY total_duration DESC 

Any ideas how to make it operational?

EDITORIAL: I have opened a case with Microsoft about this. We didn't solve it but came to a workaround. If I change Query Store Capture Mode to All (not using Auto mode) - then things go fine and the reports are generated in a timely fashion.

EDITORIAL #2 This has come to a completely different issue. The problem is not the Capture Mode but Legacy Cardinality Estimator which the database is running under. Please vote for this Azure Feedback: https://feedback.azure.com/forums/908035-sql-server/suggestions/37971781-slow-or-non-operational-query-store-under-legacy-c

Editorial #3: Azure Feedback URL seems not working anymore (MS keeps changing it so nobody can track it, probably), please vote here: https://feedback.azure.com/d365community/idea/2569b74f-6825-ec11-b6e6-000d3a4f0da0

I'm begining with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It's just keep saying "Waiting" for hours and hours. Query on the background (see below an example) is not able to finish and is consuming lot of CPU. No matter how am I changing configuration options, it's keep waiting and waiting (even last hour report. It this the reality of Query Store al over? Sounds being such a great tool but is totally unusable in real?

My Query Store size is about 1,7 GB. I'm collecting 7 day behind, in 1 hour interval and Auto Capture mode - so it's seems reasonable settings, to me.

This is an example of a query on the background which is never finished:

SELECT TOP (@results_row_count) p.query_id query_id, q.object_id object_id, ISNULL(OBJECT_NAME(q.object_id),'') object_name, qt.query_sql_text query_sql_text, ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration, SUM(rs.count_executions) count_executions, COUNT(distinct p.plan_id) num_plans FROM sys.query_store_runtime_stats rs JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id JOIN sys.query_store_query q ON q.query_id = p.query_id JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time) GROUP BY p.query_id, qt.query_sql_text, q.object_id HAVING COUNT(distinct p.plan_id) >= 1 ORDER BY total_duration DESC 

Any ideas how to make it operational?

EDITORIAL: I have opened an case with Microsoft about this. We didn't solve it but came to workaround. If I change Query Store Capture Mode to All (not using Auto mode) - then things go fine and the reports are generated in timely fashion.

EDITORIAL #2 This has come to a completely different issue. The problem is not the Capture Mode but Legacy Cardinality Estimator which the database is running under. Please vote for this Azure Feedback: https://feedback.azure.com/forums/908035-sql-server/suggestions/37971781-slow-or-non-operational-query-store-under-legacy-c

I'm beginning with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It just keeps saying "Waiting" for hours and hours. Query on the background (see below for an example) is not able to finish and is consuming lot of CPU. No matter how am I changing configuration options, it keeps waiting and waiting (even last hour report). Is this the reality of Query Store all over? Sounds being such a great tool but is totally unusable in real?

My Query Store size is about 1,7 GB. I'm collecting 7 day behind, in 1 hour interval and Auto Capture mode - so it seems to be reasonable settings, to me.

This is an example of a query on the background which is never finished:

SELECT TOP (@results_row_count) p.query_id query_id, q.object_id object_id, ISNULL(OBJECT_NAME(q.object_id),'') object_name, qt.query_sql_text query_sql_text, ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration, SUM(rs.count_executions) count_executions, COUNT(distinct p.plan_id) num_plans FROM sys.query_store_runtime_stats rs JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id JOIN sys.query_store_query q ON q.query_id = p.query_id JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time) GROUP BY p.query_id, qt.query_sql_text, q.object_id HAVING COUNT(distinct p.plan_id) >= 1 ORDER BY total_duration DESC 

Any ideas how to make it operational?

EDITORIAL: I have opened a case with Microsoft about this. We didn't solve it but came to a workaround. If I change Query Store Capture Mode to All (not using Auto mode) - then things go fine and the reports are generated in a timely fashion.

EDITORIAL #2 This has come to a completely different issue. The problem is not the Capture Mode but Legacy Cardinality Estimator which the database is running under. Please vote for this Azure Feedback: https://feedback.azure.com/forums/908035-sql-server/suggestions/37971781-slow-or-non-operational-query-store-under-legacy-c

I'm begining with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It's just keep saying "Waiting" for hours and hours. Query on the background (see below an example) is not able to finish and is consuming lot of CPU. No matter how am I changing configuration options, it's keep waiting and waiting (even last hour report. It this the reality of Query Store al over? Sounds being such a great tool but is totally unusable in real?

My Query Store size is about 1,7 GB. I'm collecting 7 day behind, in 1 hour interval and Auto Capture mode - so it's seems reasonable settings, to me.

This is an example of a query on the background which is never finished:

SELECT TOP (@results_row_count) p.query_id query_id, q.object_id object_id, ISNULL(OBJECT_NAME(q.object_id),'') object_name, qt.query_sql_text query_sql_text, ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration, SUM(rs.count_executions) count_executions, COUNT(distinct p.plan_id) num_plans FROM sys.query_store_runtime_stats rs JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id JOIN sys.query_store_query q ON q.query_id = p.query_id JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time) GROUP BY p.query_id, qt.query_sql_text, q.object_id HAVING COUNT(distinct p.plan_id) >= 1 ORDER BY total_duration DESC 

Any ideas how to make it operational?

EDITORIAL: I have opened an case with Microsoft about this. We didn't solve it but came to workaround. If I change Query Store Capture Mode to All (not using Auto mode) - then things go fine and the reports are generated in timely fashion.

I'm begining with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It's just keep saying "Waiting" for hours and hours. Query on the background (see below an example) is not able to finish and is consuming lot of CPU. No matter how am I changing configuration options, it's keep waiting and waiting (even last hour report. It this the reality of Query Store al over? Sounds being such a great tool but is totally unusable in real?

My Query Store size is about 1,7 GB. I'm collecting 7 day behind, in 1 hour interval and Auto Capture mode - so it's seems reasonable settings, to me.

This is an example of a query on the background which is never finished:

SELECT TOP (@results_row_count) p.query_id query_id, q.object_id object_id, ISNULL(OBJECT_NAME(q.object_id),'') object_name, qt.query_sql_text query_sql_text, ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration, SUM(rs.count_executions) count_executions, COUNT(distinct p.plan_id) num_plans FROM sys.query_store_runtime_stats rs JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id JOIN sys.query_store_query q ON q.query_id = p.query_id JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time) GROUP BY p.query_id, qt.query_sql_text, q.object_id HAVING COUNT(distinct p.plan_id) >= 1 ORDER BY total_duration DESC 

Any ideas how to make it operational?

EDITORIAL: I have opened an case with Microsoft about this. We didn't solve it but came to workaround. If I change Query Store Capture Mode to All (not using Auto mode) - then things go fine and the reports are generated in timely fashion.

EDITORIAL #2 This has come to a completely different issue. The problem is not the Capture Mode but Legacy Cardinality Estimator which the database is running under. Please vote for this Azure Feedback: https://feedback.azure.com/forums/908035-sql-server/suggestions/37971781-slow-or-non-operational-query-store-under-legacy-c