Still skewed parallelism after update stats with fullscan
In the plan, the seek is on this index:
[dbo].[ACCOUNT][index_Account_ClientId_FollowUpEntityLegalEntityId]
http://sqlperformance.com/2015/08/sql-performance/more-on-cxpacket-waits-skewed-parallelism
link text
SELECT DISTINCT FollowupEntity14.FULL_NAME AS FollowupEntity14_FULL_NAME128,FollowupEntity14.ENTITY_CODE AS FollowupEntity14_ENTITY_CODE130,FollowupEntity14.BUSINESS_TELEPHONE AS FollowupEntity14_BUSINESS_TELEPHONE132,Address15.ADDRESS_LINE_1 AS Address15_ADDRESS_LINE_1134,Address15.ADDRESS_LINE_2 AS Address15_ADDRESS_LINE_2136,Address15.ADDRESS_LINE_3 AS Address15_ADDRESS_LINE_3138,Address15.ADDRESS_LINE_4 AS Address15_ADDRESS_LINE_4140,Address15.CITY AS Address15_CITY142,Address15.COUNTRY AS Address15_COUNTRY144,Address15.POSTAL_CODE AS Address15_POSTAL_CODE146,FollowupEntity14.CONTACT_NAME AS FollowupEntity14_CONTACT_NAME148,FollowupEntity14.CONTACT_TELEPHONE AS FollowupEntity14_CONTACT_TELEPHONE150,DealerCallStatus110.LAST_SUCCESSFUL_CALL_DATE AS DealerCallStatus110_LAST_SUCCESSFUL_CALL_DATE153,DealerCallStatus110.LAST_UNSUCCESSFUL_CALL_DATE AS DealerCallStatus110_LAST_UNSUCCESSFUL_CALL_DATE155,DealerCallStatus110.LAST_INBOUND_CALL_DATE AS DealerCallStatus110_LAST_INBOUND_CALL_DATE157,FollowupEntity14.LEGAL_ENTITY_ID AS FollowupEntity14_LEGAL_ENTITY_ID159 FROM WORK_QUEUE_ITEM AS FollowUpConstrainedDealerPhoneCallWorkItem02 INNER JOIN (ACCOUNT AS Account13 INNER JOIN (LEGAL_ENTITY AS FollowupEntity14 INNER JOIN ADDRESS AS Address15 ON FollowupEntity14.ADDRESS_ID=Address15.ADDRESS_ID LEFT OUTER JOIN CALL_STATUS AS DealerCallStatus18 ON FollowupEntity14.LEGAL_ENTITY_ID=DealerCallStatus18.FOLLOWUP_ENTITYLEGAL_ENTITY_ID INNER JOIN CALL_STATUS AS DealerCallStatus110 ON FollowupEntity14.LEGAL_ENTITY_ID=DealerCallStatus110.FOLLOWUP_ENTITYLEGAL_ENTITY_ID) ON Account13.FOLLOWUP_ENTITYLEGAL_ENTITY_ID=FollowupEntity14.LEGAL_ENTITY_ID INNER JOIN ORGANIZATION AS Client17 ON Account13.CLIENT_ID=Client17.ORGANIZATION_ID) ON FollowUpConstrainedDealerPhoneCallWorkItem02.ACCOUNT_ID=Account13.ACCOUNT_ID INNER JOIN SERVICE_REQUEST AS CollateralGroupRequest16 ON FollowUpConstrainedDealerPhoneCallWorkItem02.SERVICE_REQUEST_ID=CollateralGroupRequest16.SERVICE_REQUEST_ID WHERE ((FollowUpConstrainedDealerPhoneCallWorkItem02.SECURED_ORGANIZATIONORGANIZATION_ID = @DerivedTable01_SECURED_ORGANIZATIONORGANIZATION_ID20 AND Account13.CLIENT_ID = @DerivedTable01_CLIENT_ID41 AND Address15.STATE = @DerivedTable01_STATE72 AND CollateralGroupRequest16.SERVICE_REQUEST_STATUS <> @DerivedTable01_SERVICE_REQUEST_STATUS103 AND Client17.CLIENT_FACILITY_ID = @DerivedTable01_CLIENT_FACILITY_ID134 AND FollowUpConstrainedDealerPhoneCallWorkItem02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS155 AND FollowUpConstrainedDealerPhoneCallWorkItem02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS176 AND FollowUpConstrainedDealerPhoneCallWorkItem02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS197 AND FollowUpConstrainedDealerPhoneCallWorkItem02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS218 AND (FollowUpConstrainedDealerPhoneCallWorkItem02.SUPPRESSION_DATE <= @DerivedTable01_SUPPRESSION_DATE239 OR (FollowUpConstrainedDealerPhoneCallWorkItem02.SUPPRESSION_DATE IS NULL )) AND (((((DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS = @DerivedTable01_LAST_OUTBOUND_CALL_STATUS2610) AND (DateDiff(Day, DealerCallStatus18.LAST_SUCCESSFUL_CALL_DATE, GETDATE()) > @DerivedTable01_3011)) OR ((DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS = @DerivedTable01_LAST_OUTBOUND_CALL_STATUS3212) AND (DateDiff(Day, DealerCallStatus18.LAST_UNSUCCESSFUL_CALL_DATE, GETDATE()) > @DerivedTable01_3613))) OR (DealerCallStatus18.CALL_STATUS_ID IS NULL )) OR (DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS IS NULL )) AND (((((DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS = @DerivedTable01_LAST_OUTBOUND_CALL_STATUS4014) AND (DateDiff(Day, DealerCallStatus18.LAST_SUCCESSFUL_CALL_DATE, GETDATE()) > @DerivedTable01_4415)) OR ((DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS = @DerivedTable01_LAST_OUTBOUND_CALL_STATUS4616) AND (DateDiff(Day, DealerCallStatus18.LAST_UNSUCCESSFUL_CALL_DATE, GETDATE()) > @DerivedTable01_5017))) OR (DealerCallStatus18.CALL_STATUS_ID IS NULL )) OR (DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS IS NULL )) AND (((((DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS = @DerivedTable01_LAST_OUTBOUND_CALL_STATUS5418) AND (DateDiff(Day, DealerCallStatus18.LAST_SUCCESSFUL_CALL_DATE, GETDATE()) > @DerivedTable01_5819)) OR ((DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS = @DerivedTable01_LAST_OUTBOUND_CALL_STATUS6020) AND (DateDiff(Day, DealerCallStatus18.LAST_UNSUCCESSFUL_CALL_DATE, GETDATE()) > @DerivedTable01_6421))) OR (DealerCallStatus18.CALL_STATUS_ID IS NULL )) OR (DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS IS NULL )) AND (((((DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS = @DerivedTable01_LAST_OUTBOUND_CALL_STATUS6822) AND (DateDiff(Day, DealerCallStatus18.LAST_SUCCESSFUL_CALL_DATE, GETDATE()) > @DerivedTable01_7223)) OR ((DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS = @DerivedTable01_LAST_OUTBOUND_CALL_STATUS7424) AND (DateDiff(Day, DealerCallStatus18.LAST_UNSUCCESSFUL_CALL_DATE, GETDATE()) > @DerivedTable01_7825))) OR (DealerCallStatus18.CALL_STATUS_ID IS NULL )) OR (DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS IS NULL ))) AND ((FollowUpConstrainedDealerPhoneCallWorkItem02.CONCRETE_TYPE IN ( @DerivedTable01_8126)) AND (FollowupEntity14.CONCRETE_TYPE IN ( @DerivedTable01_8227)) AND (CollateralGroupRequest16.CONCRETE_TYPE IN ( @DerivedTable01_8328, @DerivedTable01_8429, @DerivedTable01_8530, @DerivedTable01_8631, @DerivedTable01_8732, @DerivedTable01_8833, @DerivedTable01_8934, @DerivedTable01_9035, @DerivedTable01_9136, @DerivedTable01_9237, @DerivedTable01_9338, @DerivedTable01_9439, @DerivedTable01_9540, @DerivedTable01_9641, @DerivedTable01_9742, @DerivedTable01_9843, @DerivedTable01_9944, @DerivedTable01_10045, @DerivedTable01_10146, @DerivedTable01_10247, @DerivedTable01_10348, @DerivedTable01_10449, @DerivedTable01_10550, @DerivedTable01_10651, @DerivedTable01_10752, @DerivedTable01_10853, @DerivedTable01_10954, @DerivedTable01_11055, @DerivedTable01_11156, @DerivedTable01_11257, @DerivedTable01_11358, @DerivedTable01_11459, @DerivedTable01_11560, @DerivedTable01_11661, @DerivedTable01_11762, @DerivedTable01_11863, @DerivedTable01_11964, @DerivedTable01_12065, @DerivedTable01_12166, @DerivedTable01_12267, @DerivedTable01_12368, @DerivedTable01_12469, @DerivedTable01_12570, @DerivedTable01_12671)) AND (Client17.CONCRETE_TYPE IN ( @DerivedTable01_12772))))
/* Create a table type. */
–CREATE TYPE ConcreteTypeTableType AS TABLE
–(concrete_type VARCHAR(150));
–GO
/* Declare a variable that references the type. */
DECLARE @ConcreteTypeTVP AS ConcreteTypeTableType;
/* Add data to the table variable. */
INSERT INTO @ConcreteTypeTVP (concrete_type)
VALUES('Fdi.Po.Client'), ('Fdi.Po.CollateralGroupRequest'), ('Fdi.Po.PortfolioItemTransferRequest'), ('Fdi.Po.AdHocRequest'), ('Fdi.Po.ManualDeleteConfirmationRequest'), ('Fdi.Po.CheckRequest'), ('Fdi.Po.CaliforniaCheckRequest'), ('Fdi.Po.LienFilingRequest'), ('Fdi.Po.OhioLienFiling'), ('Fdi.Po.NewYorkLienFilingRequest'), ('Fdi.Po.NewYorkCollateralLienFilingRequest'), ('Fdi.Po.PaperTitleRequest'), ('Fdi.Po.FloridaPaperTitleRequest'), ('Fdi.Po.VirginiaEmergencyPaperTitleRequest'), ('Fdi.Po.NevadaPaperTitleRequest'), ('Fdi.Po.DispatchSubsequentRequest'), ('Fdi.Po.EndOfLeaseRequest'), ('Fdi.Po.ReleaseInterestRequest'), ('Fdi.Po.FloridaReleaseInterestRequest'), ('Fdi.Po.NevadaReleaseInterestRequest'), ('Fdi.Po.SouthCarolinaReleaseInterestRequest'), ('Fdi.Po.IdahoReleaseInterestRequest'), ('Fdi.Po.OhioReleaseInterestRequest'), ('Fdi.Po.OhioPaperTitleReleaseInterestRequest'), ('Fdi.Po.ArizonaEmergencyReleaseOfInterestRequest'), ('Fdi.Po.VirginiaEmergencyReleaseOfInterestRequest'), ('Fdi.Po.NorthCarolinaEmergencyReleaseOfInterestRequest'), ('Fdi.Po.NevadaTDNRequest'), ('Fdi.Po.NevadaTDERRequest'), ('Fdi.Po.NevadaTNORRequest'), ('Fdi.Po.DuplicateTitleRequest'), ('Fdi.Po.TitleMaintenanceRequest'), ('Fdi.Po.ChangeOwnerAddressRequest'), ('Fdi.Po.ConvertPaperTitleToElectronicRequest'), ('Fdi.Po.ConvertToElectronicRequest'), ('Fdi.Po.TitleMaintenanceViaAilotRequest'), ('Fdi.Po.ResearchRequest'), ('Fdi.Po.ErrorCorrectionRequest'), ('Fdi.Po.ContractRequest'), ('Fdi.Po.InquiryServiceRequest'), ('Fdi.Po.VerificationInquiryServiceRequest'), ('Fdi.Po.InformationInquiryServiceRequest'), ('Fdi.Po.FollowUpRequest'), ('Fdi.Po.DirectLendingServiceRequest'), ('Fdi.Po.ReturnToClientRequest'), ('Fdi.Po.FollowupEntity'), ('Fdi.Workflow.Po.FollowUpConstrainedDealerPhoneCallWorkItem');
————-select * from @ConcreteTypeTVP
DECLARE @DerivedTable01_SECURED_ORGANIZATIONORGANIZATION_ID20 INT, @DerivedTable01_CLIENT_ID41 INT, @DerivedTable01_STATE72 VARCHAR(8000), @DerivedTable01_SERVICE_REQUEST_STATUS103 VARCHAR(8000), @DerivedTable01_CLIENT_FACILITY_ID134 SMALLINT, @DerivedTable01_BUSINESS_PROCESS_STATUS155 VARCHAR(8000), @DerivedTable01_SUPPRESSION_DATE176 DATETIME, @DerivedTable01_LAST_OUTBOUND_CALL_STATUS207 VARCHAR(12), @DerivedTable01_248 INT, @DerivedTable01_LAST_OUTBOUND_CALL_STATUS269 VARCHAR(12), @DerivedTable01_3010 INT, @DerivedTable01_3311 VARCHAR(58), @DerivedTable01_7957 VARCHAR(13);
SET @DerivedTable01_3311 = 'Fdi.Workflow.Po.FollowUpConstrainedDealerPhoneCallWorkItem';
SET @DerivedTable01_7957 = 'Fdi.Po.Client';
SET @DerivedTable01_3010 = (3);
SET @DerivedTable01_LAST_OUTBOUND_CALL_STATUS269 = 'Unsuccessful';
SET @DerivedTable01_248 = (3);
SET @DerivedTable01_LAST_OUTBOUND_CALL_STATUS207 = 'Successful';
SET @DerivedTable01_SUPPRESSION_DATE176 = '2016-08-31 00:00:00.000';
SET @DerivedTable01_BUSINESS_PROCESS_STATUS155 = 'Open';
SET @DerivedTable01_CLIENT_FACILITY_ID134 = (1);
SET @DerivedTable01_SERVICE_REQUEST_STATUS103 = 'EEE';
SET @DerivedTable01_STATE72 = 'VA';
SET @DerivedTable01_CLIENT_ID41 = (11303);
SET @DerivedTable01_SECURED_ORGANIZATIONORGANIZATION_ID20 = (11303);
SELECT DISTINCT
FollowupEntity14.FULL_NAME AS FollowupEntity14_FULL_NAME80,
FollowupEntity14.ENTITY_CODE AS FollowupEntity14_ENTITY_CODE82,
FollowupEntity14.BUSINESS_TELEPHONE AS FollowupEntity14_BUSINESS_TELEPHONE84,
Address15.ADDRESS_LINE_1 AS Address15_ADDRESS_LINE_186,
Address15.ADDRESS_LINE_2 AS Address15_ADDRESS_LINE_288,
Address15.ADDRESS_LINE_3 AS Address15_ADDRESS_LINE_390,
Address15.ADDRESS_LINE_4 AS Address15_ADDRESS_LINE_492,
Address15.CITY AS Address15_CITY94,
Address15.COUNTRY AS Address15_COUNTRY96,
Address15.POSTAL_CODE AS Address15_POSTAL_CODE98,
FollowupEntity14.CONTACT_NAME AS FollowupEntity14_CONTACT_NAME100,
FollowupEntity14.CONTACT_TELEPHONE AS FollowupEntity14_CONTACT_TELEPHONE102,
DealerCallStatus110.LAST_SUCCESSFUL_CALL_DATE AS DealerCallStatus110_LAST_SUCCESSFUL_CALL_DATE105,
DealerCallStatus110.LAST_UNSUCCESSFUL_CALL_DATE AS DealerCallStatus110_LAST_UNSUCCESSFUL_CALL_DATE107,
DealerCallStatus110.LAST_INBOUND_CALL_DATE AS DealerCallStatus110_LAST_INBOUND_CALL_DATE109,
FollowupEntity14.LEGAL_ENTITY_ID AS FollowupEntity14_LEGAL_ENTITY_ID111
FROM WORK_QUEUE_ITEM AS FollowUpConstrainedDealerPhoneCallWorkItem02
INNER JOIN(ACCOUNT AS Account13
INNER JOIN(LEGAL_ENTITY AS FollowupEntity14
INNER JOIN ADDRESS AS Address15 ON FollowupEntity14.ADDRESS_ID = Address15.ADDRESS_ID
LEFT OUTER JOIN CALL_STATUS AS DealerCallStatus18 ON FollowupEntity14.LEGAL_ENTITY_ID = DealerCallStatus18.FOLLOWUP_ENTITYLEGAL_ENTITY_ID
INNER JOIN CALL_STATUS AS DealerCallStatus110 ON FollowupEntity14.LEGAL_ENTITY_ID = DealerCallStatus110.FOLLOWUP_ENTITYLEGAL_ENTITY_ID) ON Account13.FOLLOWUP_ENTITYLEGAL_ENTITY_ID = FollowupEntity14.LEGAL_ENTITY_ID
INNER JOIN ORGANIZATION AS Client17 ON Account13.CLIENT_ID = Client17.ORGANIZATION_ID) ON FollowUpConstrainedDealerPhoneCallWorkItem02.ACCOUNT_ID = Account13.ACCOUNT_ID
INNER JOIN SERVICE_REQUEST AS CollateralGroupRequest16 ON FollowUpConstrainedDealerPhoneCallWorkItem02.SERVICE_REQUEST_ID = CollateralGroupRequest16.SERVICE_REQUEST_ID
WHERE((FollowUpConstrainedDealerPhoneCallWorkItem02.SECURED_ORGANIZATIONORGANIZATION_ID = @DerivedTable01_SECURED_ORGANIZATIONORGANIZATION_ID20
AND Account13.CLIENT_ID = @DerivedTable01_CLIENT_ID41
AND Address15.STATE = @DerivedTable01_STATE72
AND CollateralGroupRequest16.SERVICE_REQUEST_STATUS <> @DerivedTable01_SERVICE_REQUEST_STATUS103
AND Client17.CLIENT_FACILITY_ID = @DerivedTable01_CLIENT_FACILITY_ID134
AND FollowUpConstrainedDealerPhoneCallWorkItem02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS155
AND (FollowUpConstrainedDealerPhoneCallWorkItem02.SUPPRESSION_DATE <= @DerivedTable01_SUPPRESSION_DATE176
OR (FollowUpConstrainedDealerPhoneCallWorkItem02.SUPPRESSION_DATE IS NULL))
AND (((((DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS = @DerivedTable01_LAST_OUTBOUND_CALL_STATUS207)
AND (DATEDIFF(Day, DealerCallStatus18.LAST_SUCCESSFUL_CALL_DATE, GETDATE()) > @DerivedTable01_248))
OR ((DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS = @DerivedTable01_LAST_OUTBOUND_CALL_STATUS269)
AND (DATEDIFF(Day, DealerCallStatus18.LAST_UNSUCCESSFUL_CALL_DATE, GETDATE()) > @DerivedTable01_3010)))
OR (DealerCallStatus18.CALL_STATUS_ID IS NULL))
OR (DealerCallStatus18.LAST_OUTBOUND_CALL_STATUS IS NULL)))
AND ((FollowUpConstrainedDealerPhoneCallWorkItem02.CONCRETE_TYPE IN(@DerivedTable01_3311))
AND (FollowupEntity14.CONCRETE_TYPE IN
(
SELECT CONCRETE_TYPE
FROM @ConcreteTypeTVP
))
AND (Client17.CONCRETE_TYPE IN(@DerivedTable01_7957))));
–IF EXISTS
–(
— SELECT *
— FROM tempdb..sysobjects
— WHERE id = OBJECT_ID('tempdb..#tempvalues')
–)
— DROP TABLE #tempvalues;
–GO