Additional memory counters, SQL versioning, and memory XML output

[mfuller333]

The additional memory counters appear when the parameter @get_memory_grant_info=1 is enabled. Once enabled the sp_whoisactive version code checks if you are on a supported version, if you are it dynamically adds the views, and shows you 4 additional top-level columns containing memory grant info (requested_memory, granted_memory, and max_used_memory. It also changes what is stored in used_memory column, instead of containing the query granted memory plus memory used by the system process itself; it changes to the actual [used_memory] in kb by the query. This allows you at a quick glance to see if you have memory bloating occurring at the query level.

If one of the records in the results sparks your interest the other additional top-level column [memory_grant_info] contains some key memory counters from the following DMV’s in an XML format.

• sys.dm_exec_query_stats • sys.dm_exec_query_memory_grants • sys.dm_exec_query_resource_semaphores • sys.resource_governor_workload_groups • sys.resource_governor_resource_pools

The column memory_grant_info contains:

<memory_counters>
  <query_memory_grants>
    <request_time>2021-08-05T10:06:01.617</request_time>
    <grant_time>2021-08-05T10:06:01.617</grant_time>
    <wait_time_ms>0</wait_time_ms>
    <requested_memory_kb>605192.00</requested_memory_kb>
    <granted_memory_kb>605192.00</granted_memory_kb>
    <used_memory_kb>94104.00</used_memory_kb>
    <max_used_memory_kb>94104.00</max_used_memory_kb>
    <ideal_memory_kb>605192.00</ideal_memory_kb>
    <required_memory_kb>6792.00</required_memory_kb>
    <queue_id>0</queue_id>
    <wait_order>0</wait_order>
    <is_next_candidate>0</is_next_candidate>
    <dop>4.00</dop>
    <query_subtree_cost>1805.6810</query_subtree_cost>
  </query_memory_grants>
  <query_resource_semaphores>
    <timeout_error_count>0</timeout_error_count>
    <target_memory_mb>259046400</target_memory_mb>
    <max_target_memory_kb>259046400</max_target_memory_kb>
    <total_memory_kb>259046400</total_memory_kb>
    <available_memory_kb>254852448</available_memory_kb>
    <granted_memory_kb>4193952</granted_memory_kb>
    <used_memory_kb>833880</used_memory_kb>
    <grantee_count>7</grantee_count>
    <waiter_count>0</waiter_count>
  </query_resource_semaphores>
  <resource_governor_workload_groups>
    <name>default</name >
    <request_max_memory_grant_percent>25</request_max_memory_grant_percent>
    <request_max_cpu_time_sec>0</request_max_cpu_time_sec>
    <request_memory_grant_timeout_sec>0</request_memory_grant_timeout_sec>
    <max_dop>0</max_dop>
  </resource_governor_workload_groups>
  <resource_governor_resource_pools>
    <name>default</name>
    <min_memory_percent>0</min_memory_percent>
    <max_memory_percent>100</max_memory_percent>
    <min_cpu_percent>0</min_cpu_percent>
    <max_cpu_percent>100</max_cpu_percent>
  </resource_governor_resource_pools>
</memory_counters>

I included the 4 top level memory grant counters in the XML output intentionally, because once you click into the XML, it’s nice not to have to toggle back to look at those values on another tab.

The other change you will see is when you enable the @Delta_interval parameter the top level column [used_memory_delta] which contained the difference of used memory between the start and end of the query execution. Now when the @get_memory_grant_info=1 is combined with the @Delta_interval another top level column appears [max_used_memory_delta], along with the [used_memory_delta], and it changes the measurement of the Delta from ( memory grant + sys processes in pages) to the actual query’s used_memory, and max_used_memory deltas in kb so you can see the actual consumption of the memory over the duration.

Finally, the versioning code added allows us to easily code for new features that are version specific. For example the code below would check if the version is (2012 SP3 + or 2014 SP2 + or SQL Server 2016 +CU9 )

--Corrected to handle 2005 correctly
DECLARE @sql_version INT  
SET @sql_version = CONVERT(INT,LEFT(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS CHAR(15)),'.',''),7))

SELECT 
CASE
     WHEN
                            (@sql_version BETWEEN 1106020 AND 1200000) OR
                            (@sql_version BETWEEN 1205000 AND 1300000) OR
                            (@sql_version BETWEEN 1301601 AND 8999999)
     THEN
                            1
     ELSE
                            0
     END as is_feature_avail,

     CASE WHEN @sql_version > 8999999
     THEN 
        1
     ELSE
        0
     END AS is_sql_2005

All the help is updated to reflect the changes, and SQL output is aligned. As well as I added an error trap in the case the user tried to engage the @get_memory_info=1 on SQL Server 2005.

**Note none of the DMV’s are enabled nor is the functionality of the procedure changed unless the @get_memory_grant_info=1 is enabled.

[mfuller333]

The normal output of sp_whoisactive will tell you that a job is running, but it will hide the name of the job behind a hexadecimal representation of the [job_id]. This CASE statement will match the job name and job step with the process, so you can easily identify the problematic job by name.

                CASE LEFT(spy.program_name,15)
                    WHEN 'SQLAgent - TSQL' THEN 
                        (SELECT TOP 1 'SQL Job = '+j.name 
                        FROM msdb.dbo.sysjobs WITH (NOLOCK) j
                        INNER JOIN msdb.dbo.sysjobsteps WITH (NOLOCK) s ON
                            j.job_id=s.job_id
                        WHERE RIGHT(CAST(s.job_id AS NVARCHAR(50)),10) = RIGHT(SUBSTRING(spy.program_name,30,34),10) )
                    WHEN 'SQL Server Prof' THEN 'SQL Server Profiler'
                ELSE 
                    spy.program_name

[erikdarlingdata]

@mfuller333 I was getting ready to review this, but I wanted to clarify if the latest addition to the code is related to the memory grant counters, or a different issue?

[mfuller333]

@erikdarlingdata I totally meant to add the converting the job name from hex as a separate pull. It just converts the job_id's hex representation to the actual job name. Is it cool to leave in as part of this release? If not I can create a separate PR.

[erikdarlingdata]

@mfuller333 I'd pull it out and open an issue for it, for visibility. It's generally frowned upon to PR stack, especially without a corresponding issue.

I'd also be interested in @amachanic's feedback, since decoding the job step name is typically hidden behind @get_additional_info = 1.

As a final remark, cross-database queries are not allowed in Azure SQL DB, so I think your code would be a breaking change without a check, there.

[erikdarlingdata]

@mfuller333 does this execute successfully for you? When I install this version and execute it without any parameters, I get some errors:

Warning: The join order has been enforced because a local join hint is used. Msg 102, Level 15, State 1, Line 77 Incorrect syntax near 'j'. Msg 319, Level 15, State 1, Line 78 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Msg 156, Level 15, State 1, Line 319 Incorrect syntax near the keyword 'AS'.

[mfuller333]

@erikdarlingdata OK, I just committed the latest version minus the job name code. Also what version of SQL are you testing on?

[erikdarlingdata]

I'm on Microsoft SQL Server 2019 (RTM-CU13) (KB5005679) - 15.0.4178.1 for initial testing. I'll go back to SQL Server 2008 for final testing.

Since removing the Agent Job code, it now compiles and runs successfully. Thanks for that.

[erikdarlingdata]

I've tested this code against SQL Server 2008 - 2019, and it all works fine.

I think my only critique is that the column is at the very end of the results, instead of in a perhaps more intuitive place (between max_used_memory and status), but that's not worth holding up a merge in my eyes. If you'd like to move it, go ahead.

@amachanic I think this is good to go, but I'll wait for your thumbs up or down.

[mfuller333]

@erikdarlingdata Thanks for the input, I agree, I moved the column memory_grant_info between max_used_memory and status on the last upload.

[mfuller333]

@amachanic I submitted the changes requested minus the hints and the null fields to remove from the XML output. Help with the hints would be great, as well as guidance defining what the final memory XML output should include.

[amachanic]

@mfuller333 I just resolved a merge conflict and the diff went a bit sideways. Have you seen that before?

[mfuller333]

@mfuller333 I just resolved a merge conflict and the diff went a bit sideways. Have you seen that before?

@amachanic No, I haven't, but I did do a little research about git revert command https://www.atlassian.com/git/tutorials/undoing-changes/git-revert

[mfuller333]

@amachanic After your last Merge branch 'master' into master GPG key ID: 4AEE18F83AFDEB23, All my changes appear merged in when I review them , but I cannot see the delta's like before. Should I resubmit them?

[mfuller333]

@erikdarlingdata If you want to give it a spin, now returning Nulls so you will see NULL grants as they are in the DMV, and this filters out of the XML results.

[amachanic]

@mfuller333 I think the version I've just committed should be the end. Give it a shot and let's see if there are any oddities. (But it's looking good on this end.)

[mfuller333]

Cool, I will review when I get back to land, out on the boat for my twin daughter's birthday party.

[mfuller333]

That's funny, I was thinking the Bizzaro World opposite, as I indicated in earlier conversations; instead, make all the counters KB. Keep in mind I am trying to think in human terms...we purchase RAM in a form of bytes, not pages. Though, pages makes sense considering everything. But my question is, at first glance, what does someone think it means?

[mfuller333]

I had a chance to review the code enhancements and give it a spin, excellent work! Besides thinking differently on the memory output measurement, looks good to me. 👍

[amachanic]

That's funny, I was thinking the Bizzaro World opposite, as I indicated in earlier conversations; instead, make all the counters KB. Keep in mind I am trying to think in human terms...we purchase RAM in a form of bytes, not pages. Though, pages makes sense considering everything. But my question is, at first glance, what does someone think it means?

I fully agree with you in principle. But sp_whoisactive has been around for over a decade and has numerous columns quoted in page-space, including reads, writes, tempdb, etc. From a product management perspective, consistency must win -- both internally and with regard to what's already been done before.

What we'll do is turn around and create a new feature that will allow users to request an internal conversion of page-space metrics to kb/mb/gb. So we'll deal with this; just not here.

[mfuller333]

Agreed, on a future PR, give the user the flexibility to choose what suits them best. I like that idea 👍