4

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.

  • what is your flush interval to disk? – Stijn Wynants May 31 '18 at 18:23
  • It is 15 minutes. Why? Should I change it? Do you think it's too frequent and delaying other work with the tool? – jerik1 May 31 '18 at 18:25
  • The query is not running at the moment of the flush? – Stijn Wynants May 31 '18 at 18:27
  • How should I know it's a moment of flush? Query (report) is not runing anytime I try. Don't think that I would be so "lucky" to hit flush moment all the time. – jerik1 May 31 '18 at 18:33
  • Ok, does the server which you are running this on have a high load & a lot of adhoc plans? – Stijn Wynants May 31 '18 at 18:39
1

I have the same issue, and upon investigating, discovered the root issue in slow Query Store queries is repeated access of the in-memory TVFs. It doesn't matter how many rows are returned from these TVFs, only the number of times they are accessed. The fix (until MS fixes underlying stuff) will be whatever you can do to nudge your plans away from reading them multiple times. Some more information in this related question and in my blog post.

Ultimately, I was able to fix this with a plan guide in production, using hints that I found moved the optimizer away from repeated TVF executions. Since the exact query text is required, and the query rarely finishes, I built a script that would create a plan guide while the report is running by pulling the text from exec_requests. Follow the below steps to create a plan guide.

  1. Open up Top Resource Consuming Queries
  2. Run the below script while the report is still spinning
  3. Cancel the report and run again.
--USE YourDB
DECLARE @badqry NVARCHAR(MAX)

SELECT TOP (1) @badqry = SUBSTRING(st.text, (r.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(st.text)  
        ELSE r.statement_end_offset 
    END - r.statement_start_offset)/2) + 1)
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE st.text LIKE '(@results_row_count int,@interval_start_time%'

IF @badqry IS NULL
RAISERROR('Missed the plan',16,1)

EXEC sp_create_plan_guide
@name = N'Fixing QueryStore Top Duration',
@stmt = @badqry,
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@results_row_count int,@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',
@hints = N'OPTION (HASH JOIN, LOOP JOIN)'

You should be able to run the above script for most of the canned Top Resource queries, but make sure to change the plan guide name if you do. Also, there's certainly a better hint out there than HASH JOIN, LOOP JOIN, but it's worked well enough for me so far.

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.