Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sp_BlitzFirst - Warn when there are running queries with a memory grant exceeding x% of the workspace #2505

Closed
Adedba opened this issue Aug 4, 2020 · 2 comments · Fixed by #2571
Closed

sp_BlitzFirst - Warn when there are running queries with a memory grant exceeding x% of the workspace #2505

Adedba opened this issue Aug 4, 2020 · 2 comments · Fixed by #2571
Assignees
@Adedba
Labels
enhancement sp_BlitzFirst
Milestone
2020-09 Release

Comments

@Adedba
Copy link
Contributor

Adedba commented Aug 4, 2020

Is your feature request related to a problem? Please describe.
Currently sp_BlitzFirst collects memory grant information and you can visibly see the % or workspace currently in use however I think it would be great to see if there are single queries running with an individual grant of x% , so 15% as a default but have this as a parameter so that it can be changed

Describe the solution you'd like
Add a new parameter for controlling the memory grant %, lets say @MemoryGrantThresholdPct

When you run sp_Blitzfirst go and get the Max workspace variable value (@MaxWorkspace) from CheckId 40 and use this to compare against the currently running queries with outstanding memory grants, if one or more of these single queries have a grant exceeding your Threshold then pop a warning in the output at level 100? Include the query plan in there too to help investigation and for added benefit if logging sp_BlitzFirst to a table

Are you ready to build the code for the feature?
Yep ready to give this a go!
@BrentOzar
Copy link
Member

Great idea, love it!

Adedba added a commit to Adedba/SQL-Server-First-Responder-Kit that referenced this issue Sep 8, 2020
@Adedba
New check for running query with high memory grant
40b11e8 
BrentOzarULTD#2505 - Added a new check (CheckID 46) to check for running queries with a memory grant greater than x% with x being a new parameter @MemoryGrantThresholdPct which defaults to 15.00.
If a query is identified whilst running sp_BlitzFirst you will get a row per query with priority 100 which includes the Grant size, Percent , sql handle, sql text and xml query plan.
@Adedba Adedba mentioned this issue Sep 8, 2020
Merged
Adedba added a commit to Adedba/SQL-Server-First-Responder-Kit that referenced this issue Sep 8, 2020
@Adedba
Fixed CheckID typo
efe2074 
BrentOzarULTD#2505 - fixed CheckID typo
BrentOzar added a commit that referenced this issue Sep 12, 2020
@BrentOzar
Merge pull request #2571 from Adedba/2505_memory-grant-exceeding-x%
f6e60ff 
sp_BlitzFirst - #2505 Warn when there are running queries with a memory grant exceeding x% of the workspace
@BrentOzar BrentOzar added this to the 2020-09 Release milestone Sep 12, 2020
@BrentOzar BrentOzar linked a pull request Sep 12, 2020 that will close this issue
sp_BlitzFirst - #2505 Warn when there are running queries with a memory grant exceeding x% of the workspace #2571
Merged
@BrentOzar
Copy link
Member

Nice work! Works great. Merged into the dev branch, will be in the September release with credit to you in the release notes.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@Adedba Adedba

Labels
enhancement sp_BlitzFirst
Projects
None yet
Milestone
2020-09 Release
2 participants
@BrentOzar @Adedba