Possible Candidate for Worst Execution Plan of the year

atroyan 2015-03-31 17:09:48

Hi All,

Having a major headache trying to tune this query and looking for help.

Problem view —-

SELECT TOP (100) PERCENT dbo.vw_avoice_candidate.ever_contacted, dbo.vw_avoice_candidate.contact_dte, dbo.vw_avoice_candidate.signed_card,
dbo.vw_avoice_candidate.sign_card_dte, dbo.vw_avoice_candidate.candidate_id, dbo.vw_avoice_candidate.campaign_id, dbo.vw_avoice_candidate.location_id,
dbo.vw_avoice_candidate.ssn, dbo.vw_avoice_candidate.lst_nme, dbo.vw_avoice_candidate.frst_nme, dbo.vw_avoice_candidate.avc_init,
dbo.vw_avoice_candidate.name_suffix, dbo.vw_avoice_candidate.Full_Nme, dbo.vw_avoice_candidate.addr1, dbo.vw_avoice_candidate.addr2,
dbo.vw_avoice_candidate.disp_addr2, dbo.vw_avoice_candidate.disp_csz, dbo.vw_avoice_candidate.city, dbo.vw_avoice_candidate.state,
dbo.vw_avoice_candidate.zip_cde, dbo.vw_avoice_candidate.home_addr1, dbo.vw_avoice_candidate.home_disp_addr2, dbo.vw_avoice_candidate.home_disp_csz,
dbo.vw_avoice_candidate.home_city, dbo.vw_avoice_candidate.home_state, dbo.vw_avoice_candidate.home_zip_cde, dbo.vw_avoice_candidate.misc_flag1,
dbo.vw_avoice_candidate.misc_flag2, dbo.vw_avoice_candidate.misc_number1, dbo.vw_avoice_candidate.misc_number2, dbo.vw_avoice_candidate.misc_date1,
dbo.vw_avoice_candidate.misc_date2, dbo.vw_avoice_candidate.note, dbo.vw_avoice_candidate.txt_msg_flg, dbo.vw_avoice_candidate.latitude,
dbo.vw_avoice_candidate.longitude, dbo.vw_avoice_candidate.zoom, dbo.vw_avoice_candidate.iconfile, dbo.vw_avoice_candidate.county,
dbo.vw_avoice_candidate.ctry_cde, dbo.vw_avoice_candidate.home_phone, dbo.vw_avoice_candidate.phone, dbo.vw_avoice_candidate.cellphone,
dbo.vw_avoice_candidate.workphone, dbo.vw_avoice_candidate.cell_phone, dbo.vw_avoice_candidate.work_phone, dbo.vw_avoice_candidate.email_addr,
dbo.vw_avoice_candidate.email, dbo.vw_avoice_candidate.fax, dbo.vw_avoice_candidate.birth_dte, dbo.vw_avoice_candidate.sex,
dbo.vw_avoice_candidate.marital_sts, dbo.vw_avoice_candidate.chalenge_vote, dbo.vw_avoice_candidate.chal_vte_reas, dbo.vw_avoice_candidate.bad_addr_flg,
dbo.vw_avoice_candidate.misc_data1, dbo.vw_avoice_candidate.misc_data2, dbo.vw_avoice_candidate.lastupdateduser,
dbo.vw_avoice_candidate.lastupdateddate, dbo.vw_avoice_candidate.moduleid, dbo.vw_avoice_candidate.roleid, dbo.vw_avoice_candidate.campaign_name,
dbo.vw_avoice_candidate.work_ext, dbo.vw_avoice_candidate.full_time, dbo.vw_avoice_candidate.hours_worked, dbo.vw_avoice_candidate.supervisor,
dbo.vw_avoice_candidate.pay_rate, dbo.vw_avoice_candidate.hire_dte, dbo.vw_avoice_candidate.term_dte, dbo.vw_avoice_candidate.term_reason,
dbo.vw_avoice_candidate.seasonal, dbo.vw_avoice_candidate.work_email, dbo.vw_avoice_candidate.location_name, dbo.vw_avoice_candidate.DescDept,
dbo.vw_avoice_candidate.DescStatus, dbo.vw_avoice_candidate.DescShift, dbo.vw_avoice_candidate.DescJob, dbo.vw_avoice_candidate.DescLanguage,
dbo.vw_avoice_candidate.native_lang, dbo.vw_avoice_candidate.english_speak, dbo.vw_avoice_candidate.DescRace,
CASE WHEN fnAvoicePolygonNames_1.DescZone IS NULL THEN 'None' ELSE fnAvoicePolygonNames_1.DescZone END AS DescZone,
dbo.vw_avoice_candidate.Expr2, dbo.vw_avoice_candidate.Expr3, dbo.vw_avoice_candidate.Expr4, dbo.vw_avoice_candidate.DescContact,
dbo.vw_avoice_candidate.DescEval, dbo.vw_avoice_candidate.user_added, dbo.vw_avoice_candidate.date_added, dbo.vw_avoice_candidate.last_contact_date,
dbo.vw_avoice_candidate.home_addr2, dbo.vw_avoice_candidate.misc_flag3, dbo.vw_avoice_candidate.misc_flag4
FROM dbo.avoice_candidate INNER JOIN
dbo.fnAvoicePolygonNames() AS fnAvoicePolygonNames_1 ON dbo.avoice_candidate.candidate_id = fnAvoicePolygonNames_1.candidate_id RIGHT OUTER JOIN
dbo.vw_avoice_candidate ON fnAvoicePolygonNames_1.campaign_id = dbo.vw_avoice_candidate.campaign_id AND
dbo.avoice_candidate.candidate_id = dbo.vw_avoice_candidate.candidate_id

View used in the join —

SELECT TOP (100) PERCENT CASE WHEN dbo.vw_avoice_candidate_NO_contacts.candidate_id IS NULL THEN 'Yes' ELSE 'No' END AS ever_contacted,
dbo.vw_avoice_last_contacted.contact_dte, dbo.vw_avoice_last_contacted.lastupdateddate AS last_contact_date,
CASE WHEN dbo.vw_avoice_last_signed_card.signed_card IS NULL THEN 'No' ELSE dbo.vw_avoice_last_signed_card.signed_card END AS signed_card,
CASE WHEN dbo.vw_avoice_last_signed_card.sign_card_dte = CONVERT(datetime, '01/01/1900', 101) THEN NULL ELSE CONVERT(VARCHAR(10),
dbo.vw_avoice_last_signed_card.sign_card_dte, 101) END AS sign_card_dte, dbo.avoice_candidate.candidate_id, dbo.avoice_candidate.campaign_id,
dbo.avoice_candidate.location_id, dbo.avoice_candidate.ssn, dbo.avoice_candidate.lst_nme, dbo.avoice_candidate.frst_nme, dbo.avoice_candidate.avc_init,
dbo.avoice_candidate.name_suffix, CASE WHEN len(avc_init) > 0 AND NOT avc_init IS NULL THEN LTRIM(isnull(name_suffix, ")
+ ' ' + frst_nme + ' ' + avc_init + ' ' + lst_nme) ELSE LTRIM(isnull(name_suffix, ") + ' ' + frst_nme + ' ' + isnull(lst_nme, ")) END AS Full_Nme,
dbo.avoice_candidate.addr1, dbo.avoice_candidate.addr2, CASE WHEN avoice_candidate.addr2 IS NULL OR
avoice_candidate.addr2 = ' ' OR
avoice_candidate.addr2 = " THEN avoice_candidate.city + ', ' + avoice_candidate.state + ' ' + CASE WHEN avoice_candidate.zip_cde IS NULL
THEN ' ' ELSE avoice_candidate.zip_cde END ELSE avoice_candidate.addr2 END AS disp_addr2, CASE WHEN avoice_candidate.addr2 IS NULL OR
avoice_candidate.addr2 = ' ' OR
avoice_candidate.addr2 = " THEN ' ' ELSE avoice_candidate.city + ', ' + avoice_candidate.state + ' ' + CASE WHEN avoice_candidate.zip_cde IS NULL
THEN ' ' ELSE avoice_candidate.zip_cde END END AS disp_csz, CASE WHEN dbo.avoice_candidate.city IS NULL
THEN 'Unknown' WHEN LEN(RTRIM(dbo.avoice_candidate.city)) < 1 THEN 'Unknown' ELSE dbo.avoice_candidate.city END AS city, dbo.avoice_candidate.state, CASE WHEN avoice_candidate.zip_cde IS NULL THEN ' ' ELSE avoice_candidate.zip_cde END AS zip_cde, dbo.avoice_candidate.home_addr1, CASE WHEN home_addr2 IS NULL OR home_addr2 = ' ' OR home_addr2 = '' THEN avoice_candidate.city + ', ' + avoice_candidate.state + ' ' + CASE WHEN avoice_candidate.zip_cde IS NULL THEN ' ' ELSE avoice_candidate.zip_cde END ELSE home_addr2 END AS home_disp_addr2, CASE WHEN home_addr2 IS NULL OR home_addr2 = ' ' OR home_addr2 = '' THEN ' ' ELSE avoice_candidate.city + ', ' + avoice_candidate.state + ' ' + CASE WHEN avoice_candidate.zip_cde IS NULL THEN ' ' ELSE avoice_candidate.zip_cde END END AS home_disp_csz, dbo.avoice_candidate.home_city, dbo.avoice_candidate.home_state, dbo.avoice_candidate.home_zip_cde, dbo.avoice_candidate.misc_flag1, dbo.avoice_candidate.misc_flag2, dbo.avoice_candidate.misc_number1, dbo.avoice_candidate.misc_number2, CASE WHEN dbo.avoice_candidate.misc_date1 = CONVERT(datetime, '01/01/1900', 101) THEN NULL ELSE CONVERT(VARCHAR(10), dbo.avoice_candidate.misc_date1, 101) END AS misc_date1, CASE WHEN dbo.avoice_candidate.misc_date2 = CONVERT(datetime, '01/01/1900', 101) THEN NULL ELSE CONVERT(VARCHAR(10), dbo.avoice_candidate.misc_date2, 101) END AS misc_date2, dbo.avoice_candidate.note, dbo.avoice_candidate.txt_msg_flg, dbo.avoice_candidate.latitude, dbo.avoice_candidate.longitude, dbo.avoice_candidate.zoom, dbo.avoice_candidate.iconfile, dbo.avoice_candidate.county, dbo.avoice_candidate.ctry_cde, dbo.avoice_candidate.home_phone, CASE WHEN dbo.avoice_candidate.home_phone = '(_) _' THEN 'No' WHEN dbo.avoice_candidate.home_phone = " THEN 'No' WHEN dbo.avoice_candidate.home_phone
IS NULL THEN 'No' ELSE 'Yes' END AS phone,
CASE WHEN dbo.avoice_candidate.cell_phone = '(
) -__' THEN 'No' WHEN dbo.avoice_candidate.cell_phone = " THEN 'No' WHEN dbo.avoice_candidate.cell_phone
IS NULL THEN 'No' ELSE 'Yes' END AS cellphone, CASE WHEN dbo.avoice_candidate.work_phone > ' ' THEN 'Yes' ELSE 'No' END AS workphone,
dbo.avoice_candidate.cell_phone, dbo.avoice_candidate.work_phone, dbo.avoice_candidate.email_addr,
CASE WHEN dbo.avoice_candidate.email_addr > ' ' THEN 'Yes' ELSE 'No' END AS email, dbo.avoice_candidate.fax,
CASE WHEN dbo.avoice_candidate.birth_dte = CONVERT(datetime, '01/01/1900', 101) THEN NULL ELSE CONVERT(VARCHAR(10), dbo.avoice_candidate.birth_dte,
101) END AS birth_dte, CASE WHEN dbo.avoice_candidate.sex = 'M' THEN 'Male' WHEN dbo.avoice_candidate.sex = 'F' THEN 'Female' ELSE 'Unknown' END AS sex,
CASE WHEN dbo.avoice_candidate.marital_sts = 'M' THEN 'Married' WHEN dbo.avoice_candidate.marital_sts = 'S' THEN 'Single' WHEN dbo.avoice_candidate.marital_sts
= 'D' THEN 'Divorced' WHEN dbo.avoice_candidate.marital_sts = 'W' THEN 'Widowed' ELSE 'Unknown' END AS marital_sts, dbo.avoice_candidate.chalenge_vote,
dbo.avoice_candidate.chal_vte_reas,
CASE WHEN dbo.avoice_candidate.bad_addr_flg = 0 THEN 'No' WHEN dbo.avoice_candidate.bad_addr_flg = 1 THEN 'Yes' ELSE 'No' END AS bad_addr_flg,
CASE WHEN dbo.avoice_candidate.misc_data1 > ' ' THEN dbo.avoice_candidate.misc_data1 ELSE 'None' END AS misc_data1,
CASE WHEN dbo.avoice_candidate.misc_data2 > ' ' THEN dbo.avoice_candidate.misc_data2 ELSE 'None' END AS misc_data2,
dbo.avoice_candidate.lastupdateduser, dbo.avoice_candidate.lastupdateddate, dbo.avoice_candidate.moduleid, dbo.avoice_candidate.roleid,
dbo.avoice_campaign.campaign_name, dbo.avoice_candidate.work_ext,
CASE WHEN dbo.avoice_candidate.full_time = 'Y' THEN 'Yes' WHEN dbo.avoice_candidate.full_time = 'N' THEN 'No' ELSE 'Unknown' END AS full_time,
dbo.avoice_candidate.hours_worked, dbo.avoice_candidate.supervisor, dbo.avoice_candidate.pay_rate,
CASE WHEN dbo.avoice_candidate.hire_dte = CONVERT(datetime, '01/01/1900', 101) THEN NULL ELSE CONVERT(VARCHAR(10), dbo.avoice_candidate.hire_dte, 101)
END AS hire_dte, dbo.avoice_candidate.term_reason,
CASE WHEN dbo.avoice_candidate.seasonal = 'N' THEN 'No' WHEN dbo.avoice_candidate.seasonal = 'Y' THEN 'Yes' ELSE " END AS seasonal,
dbo.avoice_candidate.work_email, dbo.avoice_location.location_name, CASE WHEN avoice_vw_TableDetail_1.Description IS NULL
THEN 'Unknown' ELSE avoice_vw_TableDetail_1.Description END AS DescDept, CASE WHEN avoice_vw_TableDetail_2.Description IS NULL
THEN 'Unknown' ELSE avoice_vw_TableDetail_2.Description END AS DescStatus, CASE WHEN avoice_vw_TableDetail_3.Description IS NULL
THEN 'Unknown' ELSE avoice_vw_TableDetail_3.Description END AS DescShift, CASE WHEN avoice_vw_TableDetail_4.Description IS NULL
THEN 'Unknown' ELSE avoice_vw_TableDetail_4.Description END AS DescJob, CASE WHEN avoice_vw_TableDetail_5.Description IS NULL
THEN 'Unknown' ELSE avoice_vw_TableDetail_5.Description END AS DescRace, CASE WHEN dbo.avoice_vw_TableDetail.Description IS NULL
THEN 'Unknown' ELSE dbo.avoice_vw_TableDetail.Description END AS DescLanguage, CASE WHEN avoice_vw_TableDetail_6.Description IS NULL
THEN 'Not Contacted' ELSE avoice_vw_TableDetail_6.Description END AS DescContact, CASE WHEN avoice_vw_TableDetail_7.Description IS NULL
THEN 'None' ELSE avoice_vw_TableDetail_7.Description END AS DescEval, dbo.avoice_candidate.native_lang,
CASE WHEN dbo.avoice_candidate.english_speak = 'N' THEN 'No' WHEN dbo.avoice_candidate.english_speak = 'Y' THEN 'Yes' ELSE " END AS english_speak,
dbo.avoice_candidate.sex AS Expr2, dbo.avoice_candidate.marital_sts AS Expr3, dbo.avoice_candidate.seasonal AS Expr4,
CASE WHEN dbo.avoice_candidate.term_dte = CONVERT(datetime, '01/01/1900', 101) THEN NULL ELSE CONVERT(VARCHAR(10), dbo.avoice_candidate.term_dte,
101) END AS term_dte, dbo.avoice_candidate.user_added, dbo.avoice_candidate.date_added, dbo.avoice_candidate.home_addr2, dbo.avoice_candidate.misc_flag3,
dbo.avoice_candidate.misc_flag4
FROM dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_7 RIGHT OUTER JOIN
dbo.vw_avoice_last_contacted ON avoice_vw_TableDetail_7.Id = dbo.vw_avoice_last_contacted.evaluation RIGHT OUTER JOIN
dbo.vw_avoice_last_signed_card RIGHT OUTER JOIN
dbo.avoice_candidate INNER JOIN
dbo.avoice_location ON dbo.avoice_candidate.location_id = dbo.avoice_location.location_id INNER JOIN
dbo.avoice_campaign ON dbo.avoice_candidate.campaign_id = dbo.avoice_campaign.campaign_id ON
dbo.vw_avoice_last_signed_card.candidate_id = dbo.avoice_candidate.candidate_id LEFT OUTER JOIN
dbo.vw_avoice_last_contact_type LEFT OUTER JOIN
dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_6 ON dbo.vw_avoice_last_contact_type.contact_type = avoice_vw_TableDetail_6.Id ON
dbo.avoice_candidate.candidate_id = dbo.vw_avoice_last_contact_type.candidate_id ON
dbo.vw_avoice_last_contacted.candidate_id = dbo.avoice_candidate.candidate_id LEFT OUTER JOIN
dbo.vw_avoice_candidate_NO_contacts ON dbo.avoice_candidate.candidate_id = dbo.vw_avoice_candidate_NO_contacts.candidate_id LEFT OUTER JOIN
dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_5 ON dbo.avoice_candidate.race = avoice_vw_TableDetail_5.Id LEFT OUTER JOIN
dbo.avoice_vw_TableDetail ON dbo.avoice_candidate.native_lang = dbo.avoice_vw_TableDetail.Id LEFT OUTER JOIN
dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_4 ON dbo.avoice_candidate.tbl_dtl_job_id = avoice_vw_TableDetail_4.Id LEFT OUTER JOIN
dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_2 ON dbo.avoice_candidate.status = avoice_vw_TableDetail_2.Id LEFT OUTER JOIN
dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_3 ON dbo.avoice_candidate.work_shift = avoice_vw_TableDetail_3.Id LEFT OUTER JOIN
dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_1 ON dbo.avoice_candidate.tbl_dtl_dept_id = avoice_vw_TableDetail_1.Id
WHERE (CASE WHEN dbo.avoice_candidate.term_dte = CONVERT(datetime, '01/01/1900', 101) THEN NULL ELSE CONVERT(VARCHAR(10), dbo.avoice_candidate.term_dte,
101) END IS NULL)
link text

SQLkiwi 2015-04-01 13:20:52
This looks like a sort of "flexible schema" design, given the presence of the UserDefinedxxx tables. As a design pattern, it almost inevitably leads to complex views and functions, in an attempt to provide useful access to the data.

As the views and functions become more and more nested (and not all referenced objects were provided in the question) the logic quickly becomes unfathomable, both for humans and for the query optimizer. It is little wonder that the execution plan is so large, and contains so much repeated effort. If the original query text was expanded to recursively include the text of all the referenced views and functions, it would be very large as well.

The query plan provided was not captured with Plan Explorer, so it is lacking potentially interesting information about e.g. CPU, I/O and duration. The question does not mention how slow the query is or how fast it needs to go. That said, I'm not sure that information would help much. No one is going to be able to suggest a "magic bullet" to fix the underlying design problems, aside from saying that the database design and view implementations are deeply flawed in multiple ways.

Leaving aside the many expansions of dbo.avoice_vw_TableDetail, one thing you could look to improve is the indexing on dbo.avoice_contact and dbo.avoice_candidate. There are many instances of Eager Index Spools and Top N Sorts above scans of these tables. SQL Server is building temporary nonclustered indexes in each of these cases, then discarding them after each execution. With a full analysis, you may be able to avoid these by providing better indexes.

With optimal indexing, hints, and other improvements, you might be able to get this query working well enough. The usual problem though, is that there are many such problem queries, and all have fragile performance. At some point, a redesign of the underlying database and access strategy is going to become the only sensible option. That time might already be passed.