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.

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

sp_BlitzFirst: add 5-second duration filter on cardinality estimation errors check #3253

Closed
BrentOzar opened this issue Apr 18, 2023 · 0 comments · Fixed by #3254
Closed
Assignees

Comments

@BrentOzar
Copy link
Member

BrentOzar commented Apr 18, 2023

Version of the script
SELECT @Version = '8.13', @VersionDate = '20230215';

What is the current behavior?
CheckID 42 looks for cardinality estimation errors. It does not have a filter for how long the query has run, so on a busy server (tens of thousands of batch requests per second), it can show many queries with estimation errors that are actually finishing quickly.

What is the expected behavior?
Add a 5-second filter on duration - only queries running longer than 5 seconds should show up.

This isn't a matter of just adding a filter in the WHERE clause. The pre-flight check does filter for queries that take at least 5 seconds:

        IF EXISTS( SELECT 1/0
                   FROM sys.dm_exec_requests AS r
                   JOIN sys.dm_exec_sessions AS s
                       ON r.session_id = s.session_id
                   WHERE s.host_name IS NOT NULL
                   AND r.total_elapsed_time > 5000 )

But then after that check, the one that actually checks for bad estimates is only querying sys.dm_exec_query_profiles, which doesn't have a start date or duration on it. We'll need to add a join out to sys.dm_exec_requests on request_id, and get the elapsed time from there.

@BrentOzar BrentOzar added this to the 2023-05 Release milestone Apr 18, 2023
@BrentOzar BrentOzar self-assigned this Apr 18, 2023
@BrentOzar BrentOzar changed the title sp_BlitzFirst: add 1-second duration filter on cardinality estimation errors check sp_BlitzFirst: add 5-second duration filter on cardinality estimation errors check Apr 18, 2023
BrentOzar added a commit that referenced this issue Apr 18, 2023
Only alert on queries running longer than 5 seconds. Closes #3253.
BrentOzar added a commit that referenced this issue Apr 18, 2023
…-on-estimations

#3253 sp_BlitzFirst estimation filter
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant