Incorrect GrantedQueryMemory(KB) Value in SSMS Dashboard report

Question

Just checking by to see if someone else has witnessed the same and tried a fix or has been fixed:

Issue:

I ran below query on stackoverflow2013 database

select
   ParentId as [Post Link],
   count(id)
from posts
where posttypeid = 2 and len(body) <= 200
  and (body like '%hank%')
group by parentid
having count(id) > 1
order by count(id) desc;

and when checking in SSMS dashboard report for GrantedQueryMemory the value shows around 62 GB which does not seem correct when collecting same from dmv

From dmv it shows 62 MB

It looked to me like a bug so reported here

Answer 1

Using XE to capture the query being executed by the dashboard, it just pulls the raw value from sys.dm_exec_requests. Here's the query:

exec sp_executesql @stmt=N'
          SELECT master.dbo.fn_varbintohexstr(sql_handle) AS sql_handle,
          master.dbo.fn_varbintohexstr(plan_handle) AS plan_handle,
          case when LEN(qt.text) < 2048 then qt.text else LEFT(qt.text, 2048) + N''...'' end as query_text,
        r.session_id,
        r.request_id,
        r.start_time,
        r.status,
        r.statement_start_offset,
        r.statement_end_offset,
        r.database_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        r.wait_resource,
        r.last_wait_type,
        r.open_transaction_count,
        r.open_resultset_count,
        r.transaction_id,
        r.cpu_time,
        r.total_elapsed_time,
        r.scheduler_id,
        r.reads,
        r.writes,
        r.logical_reads,
        r.transaction_isolation_level,
        r.granted_query_memory,
        r.executing_managed_code
    FROM sys.dm_exec_requests AS r
        JOIN sys.dm_exec_sessions s on r.session_id = s.session_id
        outer APPLY msdb.sys.dm_exec_sql_text(sql_handle) as qt
    WHERE s.is_user_process = CASE when @include_system_processes > 0 THEN s.is_user_process ELSE 1 END',@params=N'@include_system_processes Bit',@include_system_processes=0

Notice the r.granted_query_memory in the select, with no multiplication or division being performed on it. Note that this number is actually in pages. When I run your query I get these different numbers:

• sys.dm_exec_requests.granted_query_memory = 1,464 pages (times 8KB per page = 11,712 KB)
• sys.dm_exec_query_stats.last_grant_kb = 11,712 kilobytes
• Performance Dashboard = 11,993,088 "kilobytes"

This is exactly the number of bytes granted (11,712 * 1,024 = 11,993,088).

So it appears the bug is in the SSMS user interface code here, rather than in the query, as it's getting 1,464 back from the query, and must then be multiplying by 8 (conversion from pages to KB) and then 1,024 (accidental conversion from KB to bytes). Good call reporting it to Microsoft.

I recently reported the exact same type of calculation problem to Microsoft in a Query Store report, which is documented in this Q&A: What is 'Log Memory' in Query Store 2017. Seems like a common problem =)