Description
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.