Query Store - SSMS 18.6 not showing Regressed Query Data in Chart Format

Question

Fellow SQLers,

I have turned on query store - sql 2016 - to do some evalution.
On my box, I have ssms 2016, ssms 2017 and ssms 2018.
On ssms 2016 and sssm 2017, when I select View Regressed Queries and select view in Chart Format. It is perfect.
When I do this on SSMS 2018 (18.6), that display is blank for Chart Format. All other display formats are fine.

Anyone know if this is a bug or if something is missing on my end.

Thx
MG

Answer 1

Maybe there are no regressed queries?

I tried this on my end, and indeed the chart came up blank, but I don't have any production database at home, but this was just a copy of AdventureWorks that I have used very little. I used Profiler to see what SSMS was emitting, and I got the query below. Indeed, it came back with an empty result set. But when I commented out the WHERE clause, I got a couple of rows.

Note that the actual query depends on the selections you make. When I selected CPU Time as metric, I actually got one query back - and that was a query that runs against Query Store itself!

CREATE OR ALTER PROCEDURE dbo.Demo
    @StartsWith nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    WAITFOR DELAY '00:00:00.500'

    CREATE TABLE #Temp
    (
        ProductID integer NOT NULL,
        [Name] nvarchar(50) COLLATE DATABASE_DEFAULT NOT NULL
    );

    INSERT INTO #Temp
        (ProductID, [Name])
    SELECT
        P.ProductID,
        P.[Name]
    FROM Production.Product AS P
    WHERE
        P.[Name] LIKE @StartsWith + N'%';

    UPDATE STATISTICS #Temp WITH FULLSCAN

    SELECT
        T.[Name],
        OrderCount = COUNT_BIG(DISTINCT TH.ReferenceOrderID)
    FROM #Temp AS T
    JOIN Production.TransactionHistory AS TH
        ON TH.ProductID = T.ProductID
    GROUP BY
        T.[Name]

    DBCC SHOW_STATISTICS (N'tempdb..#Temp', [Name])
        WITH STAT_HEADER, HISTOGRAM;

    DROP TABLE #Temp;
END;
GO
EXEC sp_getapplock 'Start', 'Shared', 'Session'
EXEC dbo.Demo N'E'
EXEC sp_releaseapplock 'Start', 'Session'

EXEC dbo.Demo N'T'

SELECT * FROM sys.database_query_store_options

exec sp_executesql N'WITH 
hist AS
(
SELECT
    p.query_id query_id,
    ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) total_cpu_time,
    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
WHERE NOT (rs.first_execution_time > @history_end_time OR rs.last_execution_time < @history_start_time)
GROUP BY p.query_id
),
recent AS
(
SELECT
    p.query_id query_id,
    ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) total_cpu_time,
    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
WHERE NOT (rs.first_execution_time > @recent_end_time OR rs.last_execution_time < @recent_start_time)
GROUP BY p.query_id
)
SELECT TOP (@results_row_count)
    results.query_id query_id,
    results.object_id object_id,
    ISNULL(OBJECT_NAME(results.object_id),'''') object_name,
    results.query_sql_text query_sql_text,
    results.additional_cpu_time_workload additional_cpu_time_workload,
    results.total_cpu_time_recent total_cpu_time_recent,
    results.total_cpu_time_hist total_cpu_time_hist,
    ISNULL(results.count_executions_recent, 0) count_executions_recent,
    ISNULL(results.count_executions_hist, 0) count_executions_hist,
    queries.num_plans num_plans
FROM
(
SELECT
    hist.query_id query_id,
    q.object_id object_id,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, recent.total_cpu_time/recent.count_executions-hist.total_cpu_time/hist.count_executions)*(recent.count_executions), 2) additional_cpu_time_workload,
    ROUND(recent.total_cpu_time, 2) total_cpu_time_recent,
    ROUND(hist.total_cpu_time, 2) total_cpu_time_hist,
    recent.count_executions count_executions_recent,
    hist.count_executions count_executions_hist
FROM hist
    JOIN recent ON hist.query_id = recent.query_id
    JOIN sys.query_store_query q ON q.query_id = hist.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE
    recent.count_executions >= @min_exec_count
) AS results
JOIN
(
SELECT
    p.query_id query_id,
    COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_plan p
GROUP BY p.query_id
HAVING COUNT(distinct p.plan_id) >= 1
) AS queries ON queries.query_id = results.query_id
WHERE additional_cpu_time_workload > 0
ORDER BY additional_cpu_time_workload DESC
OPTION (MERGE JOIN)',N'@results_row_count int,@recent_start_time datetimeoffset(7),@recent_end_time datetimeoffset(7),@history_start_time datetimeoffset(7),@history_end_time datetimeoffset(7),@min_exec_count bigint',@results_row_count=25,@recent_start_time='2020-09-12 11:37:47.1269487 +02:00',@recent_end_time='2020-09-12 12:37:47.1269487 +02:00',@history_start_time='2020-09-05 12:37:47.1269487 +02:00',@history_end_time='2020-09-12 12:37:47.1269487 +02:00',@min_exec_count=1

Answer 2

Her Erland,

As always, thanks for the input! In my case, I got a display of the data in any of the views "other than Chart view". Nothing I could alter would cause it to display.

This morning, it is working!!!! I can see lots of queries showing up in Chart view - even the ones I was looking for yesterday. I have no idea why it was not showing "anything" for the past two days. I did check the configuration data range and it was fine. All worked except chartview - now that works.

Thx
MG

Answer 3

Hi @Mark Gordon ,

Glad to know that your issue has been solved.
I am not sure whether this issue is a bug. In my environment, viewing Regressed Queries in chart format works well.
If this issue occurs again, you can submit it to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server.
Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft feedback.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.