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

Mark Gordon 641 Reputation points
2020-09-11T16:38:24.12+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,547 questions
No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 68,091 Reputation points Microsoft MVP
    2020-09-12T11:49:35.79+00:00

    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
    
    No comments

  2. Mark Gordon 641 Reputation points
    2020-09-12T19:26:20.737+00:00

    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

    No comments

  3. AmeliaGu-MSFT 13,881 Reputation points
    2020-09-14T06:01:39.957+00:00

    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.

    No comments