More testing was showing simple parameterization alone would yield significant performance improvements, without using hints like option(recompile) that would require code changes.
When I switched to sql statements as captured in profiler from production, ( below )
like: exec sp_executesql N'SELECT DISTINCT TOP 2000
I wasn't getting that performance boost.
If I add the option(recompile), either query form uses the new index, runs multi-threaded, uses the two new filtered statistics and doesn't timeout during compilation. Running the straight select without sp_executesql I got all of that just with simple parameterization as the database setting ( no option recompile )
exec sp_executesql N'SELECT DISTINCT TOP 2000 CollateralGroupRequest02.SERVICE_REQUEST_ID AS PrimaryKey,Client18.SHORT_NAME AS ClientShortName,EeeReason19.DESCRIPTION AS EeeReason,AccountProperty110.MODIFIED_MANUFACTURER_ID AS VIN,Account15.CATEGORY AS AccountType,FollowupEntity111.ENTITY_CODE AS DealerID,FollowupEntity111.NORMALIZED_ENTITY_CODE AS FollowupEntity111_NORMALIZED_ENTITY_CODE60,Account15.FINANCED_DATE AS FinancedDate,Account15.BOOKED_DATE AS BookedDate,AccountProperty110.EXPECTED_TITLING_STATE_ABBR AS ExpectedTitlingState,BusinessUnit112.LONG_NAME AS BusinessUnit,BusinessUnit112.SHORT_NAME AS BusinessUnitId,CollateralGroupRequest02.SERVICE_REQUEST_STATUS AS Status,AccountOwnershipDocSummary16.STATUS AS AccountStatus,AccountOwnershipDocSummary16.BORROWER_FULL_NAMES AS BorrowerFullNames,Account15.CUSTOM_ATTRIBUTE_1 AS AccountNumber,Account15.CUSTOM_ATTRIBUTE_2 AS LoanNumber,Account15.CUSTOM_ATTRIBUTE_3 AS LoanSuffix,Account15.CUSTOM_ATTRIBUTE_4 AS Branch,CollateralGroupRequest02.EEE_DATE AS EEEDate,CollateralGroupRequest02.EEE_CAUSE AS CauseOfEEE,CollateralGroupRequest02.REQUEST_TRANSACTION_TYPE AS RequestType,Jurisdiction113.SHORT_NAME AS TMState,Account15.RECOVERY_STATUS AS RecoveryCode,Account15.USER_DEFINED_1 AS UserDef1,Account15.USER_DEFINED_2 AS UserDef2,Account15.USER_DEFINED_3 AS UserDef3,LienholderStatusCode114.STATUS_CODE AS LienholderStatus,AccountProperty110.VEHICLE_TYPE AS CollateralType,Account15.SUB_CATEGORY AS AccountSubType,(SELECT DerivedTable01_11 FROM (SELECT MIN(ReminderWorkItem02.REMIND_DATE) AS DerivedTable01_11 FROM WORK_QUEUE_ITEM AS ReminderWorkItem02 WHERE ((ReminderWorkItem02.ACCOUNT_ID = Account15.ACCOUNT_ID AND ReminderWorkItem02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS421 AND ReminderWorkItem02.NAME = @DerivedTable01_NAME622 AND ReminderWorkItem02.SECURED_ORGANIZATIONORGANIZATION_ID = Account15.CLIENT_ID) AND ((ReminderWorkItem02.CONCRETE_TYPE IN ( @DerivedTable01_923))))) AS ScalarQueryTable) AS ReminderDate,(SELECT ExternalUser13_USERNAME13 FROM (SELECT TOP 1 ExternalUser13.USERNAME AS ExternalUser13_USERNAME13,ReminderWorkItem02.REMIND_DATE AS ReminderWorkItem02_REMIND_DATE1 FROM WORK_QUEUE_ITEM AS ReminderWorkItem02 INNER JOIN USR AS ExternalUser13 ON ReminderWorkItem02.ASSIGNED_USER_ID=ExternalUser13.USR_ID WHERE ((ReminderWorkItem02.ACCOUNT_ID = Account15.ACCOUNT_ID AND ReminderWorkItem02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS524 AND ReminderWorkItem02.NAME = @DerivedTable01_NAME725 AND ReminderWorkItem02.SECURED_ORGANIZATIONORGANIZATION_ID = Account15.CLIENT_ID) AND ((ReminderWorkItem02.CONCRETE_TYPE IN ( @DerivedTable01_1126)))) ORDER BY 2) AS ScalarQueryTable) AS ReminderUser FROM SERVICE_REQUEST AS CollateralGroupRequest02 INNER JOIN (SERVICED_COLLATERAL_GROUP_ITEM AS ServicedCollateralGroupItem13 INNER JOIN (SERVICED_COLLATERAL_GROUP_ITEM AS ServicedAccount14 INNER JOIN (ACCOUNT AS Account15 INNER JOIN ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary16 ON Account15.ACCOUNT_ID=AccountOwnershipDocSummary16.ACCOUNT_ID INNER JOIN PROPERTY AS AccountProperty110 ON Account15.ACCOUNT_ID=AccountProperty110.ACCOUNT_ID LEFT OUTER JOIN LEGAL_ENTITY AS FollowupEntity111 ON Account15.FOLLOWUP_ENTITYLEGAL_ENTITY_ID=FollowupEntity111.LEGAL_ENTITY_ID LEFT OUTER JOIN BUSINESS_UNIT AS BusinessUnit112 ON Account15.BUSINESS_UNIT_ID=BusinessUnit112.BUSINESS_UNIT_ID LEFT OUTER JOIN LIENHOLDER_STATUS_CODE AS LienholderStatusCode114 ON Account15.LIENHOLDER_STATUS_CODE_ID=LienholderStatusCode114.LIENHOLDER_STATUS_CODE_ID) ON ServicedAccount14.ACCOUNT_ID=Account15.ACCOUNT_ID) ON ServicedCollateralGroupItem13.SERVICED_COLLATERAL_GROUP_ITEM_ID=ServicedAccount14.SERVICED_COLLATERAL_GROUP_ITEM_ID) ON CollateralGroupRequest02.SERVICE_REQUEST_ID=ServicedCollateralGroupItem13.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID INNER JOIN ORGANIZATION AS Client18 ON CollateralGroupRequest02.CLIENT_ID=Client18.ORGANIZATION_ID LEFT OUTER JOIN EEE_REASON AS EeeReason19 ON CollateralGroupRequest02.EEE_REASON_ID=EeeReason19.EEE_REASON_ID INNER JOIN ORGANIZATION AS Jurisdiction113 ON CollateralGroupRequest02.JURISDICTION_ID=Jurisdiction113.ORGANIZATION_ID WHERE ((CollateralGroupRequest02.CLIENT_ID = @DerivedTable01_CLIENT_ID30 AND Account15.CLIENT_ID = @DerivedTable01_CLIENT_ID61 AND CollateralGroupRequest02.EEE_DATE IS NOT NULL AND ((CollateralGroupRequest02.CONCRETE_TYPE = @DerivedTable01_CONCRETE_TYPE113 AND CollateralGroupRequest02.BUSINESS_PROCESS_STATUS IN ( @DerivedTable01_BUSINESS_PROCESS_STATUS134, @DerivedTable01_BUSINESS_PROCESS_STATUS145) AND AccountOwnershipDocSummary16.STATUS = @DerivedTable01_STATUS166) OR (CollateralGroupRequest02.CONCRETE_TYPE = @DerivedTable01_CONCRETE_TYPE187 AND CollateralGroupRequest02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS208 AND AccountOwnershipDocSummary16.STATUS = @DerivedTable01_STATUS229) OR (CollateralGroupRequest02.CONCRETE_TYPE = @DerivedTable01_CONCRETE_TYPE2410 AND CollateralGroupRequest02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS2611) OR (CollateralGroupRequest02.CONCRETE_TYPE = @DerivedTable01_CONCRETE_TYPE2812 AND CollateralGroupRequest02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS3013 AND AccountOwnershipDocSummary16.STATUS = @DerivedTable01_STATUS3214 OR (CollateralGroupRequest02.SERVICE_REQUEST_STATUS = @DerivedTable01_SERVICE_REQUEST_STATUS3415 AND CollateralGroupRequest02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS3616 AND AccountOwnershipDocSummary16.STATUS = @DerivedTable01_STATUS3817)) OR (CollateralGroupRequest02.CONCRETE_TYPE = @DerivedTable01_CONCRETE_TYPE4018 AND CollateralGroupRequest02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS4219)) AND (Account15.CUSTOM_ATTRIBUTE_4 = @DerivedTable01_CUSTOM_ATTRIBUTE_44420))) ORDER BY 20
OPTION
(
--RECOMPILE,-- Used to see the Statistics Output
QUERYTRACEON 3604,-- Redirects the output to SSMS
QUERYTRACEON 9204 -- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
)',N'@DerivedTable01_CLIENT_ID30 int,@DerivedTable01_CLIENT_ID61 int,@DerivedTable01_CONCRETE_TYPE113 varchar(8000),@DerivedTable01_BUSINESS_PROCESS_STATUS134 varchar(8000),@DerivedTable01_BUSINESS_PROCESS_STATUS145 varchar(8000),@DerivedTable01_STATUS166 varchar(8000),@DerivedTable01_CONCRETE_TYPE187 varchar(8000),@DerivedTable01_BUSINESS_PROCESS_STATUS208 varchar(8000),@DerivedTable01_STATUS229 varchar(8000),@DerivedTable01_CONCRETE_TYPE2410 varchar(8000),@DerivedTable01_BUSINESS_PROCESS_STATUS2611 varchar(8000),@DerivedTable01_CONCRETE_TYPE2812 varchar(8000),@DerivedTable01_BUSINESS_PROCESS_STATUS3013 varchar(8000),@DerivedTable01_STATUS3214 varchar(8000),@DerivedTable01_SERVICE_REQUEST_STATUS3415 varchar(8000),@DerivedTable01_BUSINESS_PROCESS_STATUS3616 varchar(8000),@DerivedTable01_STATUS3817 varchar(8000),@DerivedTable01_CONCRETE_TYPE4018 varchar(8000),@DerivedTable01_BUSINESS_PROCESS_STATUS4219 varchar(8000),@DerivedTable01_CUSTOM_ATTRIBUTE_44420 varchar(8000),@DerivedTable01_BUSINESS_PROCESS_STATUS421 varchar(8000),@DerivedTable01_NAME622 varchar(8),@DerivedTable01_923 varchar(32),@DerivedTable01_BUSINESS_PROCESS_STATUS524 varchar(8000),@DerivedTable01_NAME725 varchar(8),@DerivedTable01_1126 varchar(32)',@DerivedTable01_CLIENT_ID30=11330,@DerivedTable01_CLIENT_ID61=11330,@DerivedTable01_CONCRETE_TYPE113='Fdi.Po.FollowUpRequest',@DerivedTable01_BUSINESS_PROCESS_STATUS134='Open',@DerivedTable01_BUSINESS_PROCESS_STATUS145='Closed',@DerivedTable01_STATUS166='NO_TITLE_PM',@DerivedTable01_CONCRETE_TYPE187='Fdi.Po.TitleMaintenanceRequest',@DerivedTable01_BUSINESS_PROCESS_STATUS208='Open',@DerivedTable01_STATUS229='TITLE_MAINTENANCE_REQUEST_SENT',@DerivedTable01_CONCRETE_TYPE2410='Fdi.Po.DuplicateTitleRequest',@DerivedTable01_BUSINESS_PROCESS_STATUS2611='Closed',@DerivedTable01_CONCRETE_TYPE2812='Fdi.Po.DirectLendingServiceRequest',@DerivedTable01_BUSINESS_PROCESS_STATUS3013='Open',@DerivedTable01_STATUS3214='NO_TITLE_PM',@DerivedTable01_SERVICE_REQUEST_STATUS3415='DocumentsReturned',@DerivedTable01_BUSINESS_PROCESS_STATUS3616='Closed',@DerivedTable01_STATUS3817='NO_TITLE_PM',@DerivedTable01_CONCRETE_TYPE4018='Fdi.Po.AdHocRequest',@DerivedTable01_BUSINESS_PROCESS_STATUS4219='Open',@DerivedTable01_CUSTOM_ATTRIBUTE_44420='01102',@DerivedTable01_BUSINESS_PROCESS_STATUS421='Open',@DerivedTable01_NAME622='REMINDER',@DerivedTable01_923='Fdi.Workflow.Po.ReminderWorkItem',@DerivedTable01_BUSINESS_PROCESS_STATUS524='Open',@DerivedTable01_NAME725='REMINDER',@DerivedTable01_1126='Fdi.Workflow.Po.ReminderWorkItem'