sp_BlitzCache - Incorrect Percent Memory Grant Used Calculations

[mdpenguin]

Version of the script

• 8.15 - 20230613

What is the current behavior?

• PercentMemoryGrantUsed is calculated as:

CAST(ISNULL(NULLIF(( max_used_grant_kb * 1.00 ), 0) / NULLIF(min_grant_kb, 0), 0) * 100. AS MONEY) AS PercentMemoryGrantUsed

• This doesn't calculate the percent of memory grant that was used by the query because the two numbers aren't related. There's also no reason to believe that the maximum/minimum used grant reflects the amount of memory used when the maximum/minimum grant was granted.

If the current behavior is a bug, please provide the steps to reproduce.

• Run SP

What is the expected behavior?

• A better calculation would use total_used_grant_kb / total_grant_kb as this will cover all memory usage since SP was compiled

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

• All versions of SQL Server and All OS's are affected. This appears to have been how the metric has always been calculated.

[mdpenguin]

@erikdarlingdata, why have you assigned this to me? Are you asking me to make a PR?

[erikdarlingdata]

@mdpenguin yes, I assigned the issue you to you because you opened it and seemed to have a clear idea of what the code change should be. If you don't want it assigned to you I can remove it.

[BrentOzar]

Thanks for the pull request. Makes sense. Merging into the dev branch, will be in the next release with credit to you in the release notes.