actual vs estimated rows -- statistic just updated with fullscan
-
I was asked to optimize this fairly complex query but my main focus at the moment is why the operation with the 2nd highest estimated cost ( 14% for an index seek at the far right of the attached plan ) has a large variance between estimated and actual rows when I just updated the statistic on the index with fullscan
Operation Object Est Cost Est Rows Actual Rows Index Seek [COLLATERALMANAGER].[dbo].[SERVICE_REQUEST].[index_ServiceRequest_ServiceRequestStatus].(NonClustered) 14.20% 1,993,490 14,167 
SELECT TOP 2000
--DuplicateTitleRequest02.service_request_id AS
--DuplicateTitleRequest02_SERVICE_REQUEST_ID22,
DuplicateTitleRequest02.action_date
AS DuplicateTitleRequest02_ACTION_DATE1,
AccountOwnershipDocSummary18.ownership_doc_modified_manufacturer_id AS
AccountOwnershipDocSummary18_OWNERSHIP_DOC_MODIFIED_MANUFACTURER_ID27,
AccountOwnershipDocSummary18.original_account_manufacturer_id AS
AccountOwnershipDocSummary18_ORIGINAL_ACCOUNT_MANUFACTURER_ID29,
AccountOwnershipDocSummary18.account_modified_manufacturer_id AS
AccountOwnershipDocSummary18_ACCOUNT_MODIFIED_MANUFACTURER_ID31,
AccountOwnershipDocSummary18.borrower_full_names AS
AccountOwnershipDocSummary18_BORROWER_FULL_NAMES33,
AccountOwnershipDocSummary18.owner_full_names AS
AccountOwnershipDocSummary18_OWNER_FULL_NAMES35,
Jurisdiction19.short_name AS
Jurisdiction19_SHORT_NAME37,
AccountOwnershipDocSummary18.status AS
AccountOwnershipDocSummary18_STATUS39,
Account17.financed_date AS
Account17_FINANCED_DATE41,
Account17.perfected_date_time AS
Account17_PERFECTED_DATE_TIME43,
DuplicateTitleRequest02.service_request_status AS
DuplicateTitleRequest02_SERVICE_REQUEST_STATUS5,
DuplicateTitleRequest02.fdi_department_id AS
DuplicateTitleRequest02_FDI_DEPARTMENT_ID47,
DuplicateTitleRequest02.usr_id AS
DuplicateTitleRequest02_USR_ID49,
DuplicateTitleRequest02.duplicate_title_request_reason AS
DuplicateTitleRequest02_DUPLICATE_TITLE_REQUEST_REASON51,
Client13.short_name AS
Client13_SHORT_NAME53,
DuplicateTitleRequest02.created_date_time AS
DuplicateTitleRequest02_CREATED_DATE_TIME55,
Account17.category AS
Account17_CATEGORY57,
OwnershipDoc110.created_date_time AS
OwnershipDoc110_CREATED_DATE_TIME59,
FdiDepartment111.department_name AS
FdiDepartment111_DEPARTMENT_NAME61,
AccountOwnershipDocSummary18.ownership_doc_issuing_state_abbr AS
AccountOwnershipDocSummary18_OWNERSHIP_DOC_ISSUING_STATE_ABBR63,
OperationCode112.code AS
OperationCode112_CODE65,
OperationCode112.code_description AS
OperationCode112_CODE_DESCRIPTION67,
Facility113.long_name AS
Facility113_Long_Name69
FROM service_request AS DuplicateTitleRequest02
INNER JOIN (organization AS Client13
LEFT OUTER JOIN facility AS Facility113
ON Client13.client_facility_id =
Facility113.facility_id)
ON DuplicateTitleRequest02.client_id = Client13.organization_id
INNER JOIN (serviced_collateral_group_item AS
ServicedCollateralGroupItem15
INNER JOIN (serviced_collateral_group_item AS
ServicedAccount16
INNER JOIN (account AS Account17
INNER JOIN (
account_ownership_doc_summary AS
AccountOwnershipDocSummary18
LEFT OUTER JOIN ownership_doc AS
OwnershipDoc110
ON
AccountOwnershipDocSummary18.ownership_doc_id =
OwnershipDoc110.ownership_doc_id)
ON Account17.account_id =
AccountOwnershipDocSummary18.account_id)
ON ServicedAccount16.account_id =
Account17.account_id)
ON
ServicedCollateralGroupItem15.serviced_collateral_group_item_id = ServicedAccount16.serviced_collateral_group_item_id)
ON DuplicateTitleRequest02.service_request_id =
ServicedCollateralGroupItem15.collateral_group_requestservice_request_id
LEFT OUTER JOIN organization AS Jurisdiction19
ON DuplicateTitleRequest02.jurisdiction_id =
Jurisdiction19.organization_id
LEFT OUTER JOIN fdi_department AS FdiDepartment111
ON DuplicateTitleRequest02.fdi_department_id =
FdiDepartment111.fdi_department_id
LEFT OUTER JOIN operation_code AS OperationCode112
ON DuplicateTitleRequest02.operation_code_id =
OperationCode112.operation_code_id
WHERE ( (
--Client13.organization_id = 11330 AND
DuplicateTitleRequest02.service_request_status IN (
'WaitingForTitle' )
--AND DuplicateTitleRequest02.service_request_status NOT IN
-- (
-- 'CompletedViaScannedTitle', 'CancelledWithCredit',
-- 'CancelledWithoutCredit'
-- )
AND Client13.client_facility_id = 1
AND ( ( DuplicateTitleRequest02.business_process_status = 'Open' )
OR ( DuplicateTitleRequest02.business_process_status =
'Closed'
AND DuplicateTitleRequest02.service_request_status =
'EEE' ) ) )
AND ( ( DuplicateTitleRequest02.concrete_type IN (
'Fdi.Po.DuplicateTitleRequest' )
)
AND ( Client13.concrete_type IN ( 'Fdi.Po.Client' ) ) ) )
ORDER BY 1 option(recompile) --- 2/*
update statistics [service_request] [index_ServiceRequest_ServiceRequestStatus] with fullscan
*/ -
That is a big difference. I'm not sure why you're seeing that without checking the statistics to understand what the values that the plan is compiling for represent there.
I'd be more focused on the key lookup operation though. That's a heck of a lot of work being done on top of the rest of the plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Looking at it again, an interesting thing is that the output from the Hash join is estimated at 2,000 rows and is actually 10k. That's the opposite direction of the other estimate. Is it possible that you don't have an enforced constraint between these two tables? Is there a foreign key? Is it using WITH CHECK? That could affect the choices the optimizer is making here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I"ll have to look at the hash you mentioned. Since I've implemented filtered statistics on our largest clients and busiest tables, when I run this with a client specified it uses the filtered statistic and the cardinality estimates are much better. Not sure if the "business side" is going to agree to always specifying a client.
Using this the filtered stat on that client shows as loaded six times. option(QUERYTRACEON 3604,QUERYTRACEON 9204)
The original query had a non-sargeable, totally un-needed piece I'm suggesting development take out. It basically says status must be equal to "X" and NOT IN other status' ____ ORMs !! So, "give me only white paint, but also make sure you don't give me blue or green paint" 🙂
DuplicateTitleRequest02.service_request_status IN (
'WaitingForTitle' )
--AND DuplicateTitleRequest02.service_request_status NOT IN
-- (
-- 'CompletedViaScannedTitle', 'CancelledWithCredit',
-- 'CancelledWithoutCredit'
-- )
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply