You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
The text was updated successfully, but these errors were encountered:
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:
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.
The text was updated successfully, but these errors were encountered: