Skip to content

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

Closed
@BrentOzar

Description

@BrentOzar

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.

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Relationships

None yet

Development

No branches or pull requests

Issue actions