Still skewed parallelism after update stats with fullscan

Burnhaven 2016-08-09 23:02:24

I used Paul Randal's query to find multi-threading queries with cxpacket waits. After finding one, the execution plan showed an index seek with wide variance between estimated and actual rows. 55k estimated versus 2.3 million actual. So I updated that index's statistic with fullscan. removed that plan from cache and re-ran the query. Still shows the same wide variance
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

/storage/attachments/1677-tmp2182.pesession

Aaron Bertrand 2016-08-24 03:51:24

How many @DerivedTable variables are there in that IN() predicate? (The query text is truncated.) SQL Server doesn't do well estimating a large number of IN() values, regardless of how fresh your stats are (it's not the stats' fault). Have you considered using a TVP of n values instead of passing discrete values into n distinct parameters?

Burnhaven 2016-08-24 18:09:38

There are probably 30-40 of those parameters in the "IN" clause. I see Brent has a post here on TVP's:
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))))

Aaron Bertrand 2016-08-25 01:10:48

Oh my. Yeah, I'm not sure what is generating that spaghetti for you, but I don't think there are any easy fixes. 🙁

Burnhaven 2016-08-31 15:25:02

I tried changing the large number of values behind the "IN" operator to a temp table subquery. Then, as shown below, to a TVP used in the select statement. Didn't make any difference in the particular skew between actual/estimated rows in that particular index seek.

/* 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

August 9, 2016

No Comments