0

I have a query that behaves differently depending on the client executing it. I got instantaneous result (163 rows) with Azure Data Studio. More than 7 minutes for SQL Management Studio, Report Builder, SSRS Web Portal.

What is even better is that I achieve the same performance in SSMS if I remove two columns from the select OR if I add 2 null columns.

SELECT
  mycols,
  (...),
  null,
  null
FROM
(...)

The explain plan is the same, I can't understand a thing. Tuning advisor has no advice.

I thought it has something to do why the client options and the only one that was different was the ARITHMETIC_ABORT_ENABLED that was FALSE. After setting it to true in SSMS still no effect.

I use SQL Server 2019.

Here is the query:

DECLARE @UserID as VARCHAR(30) = CURRENT_USER 
DECLARE @ProductionCenterSwipCode as VARCHAR(30) = 'AIX'
DECLARE @ResourceName as VARCHAR(30) = NULL
DECLARE @ActivityCode as VARCHAR(30) = '{ALL}'
DECLARE @SubactivityCode as VARCHAR(30) = '{ALL}'
DECLARE @WorkNatureCode as VARCHAR(30) = '{ALL}'
DECLARE @ClientId as INT = -9999
DECLARE @ContractId as INT = -9999
DECLARE @StartDate as DATE= CAST( '2021-04-01' AS DATE)
DECLARE @EndDate as DATE = CAST( '2021-05-30' AS DATE)


SELECT concat(cct.cct_activity_domain_code, ' - ', cct.cct_activity_domain_label) domain_of_activity,
       concat(hrh.hrh_production_center_swip_code, ' - ', hrh.hrh_production_center_name) resource_production_center,
       hrh.hrh_full_name resource,
       dat.dat_date Date_logged,
       tim.tim_number_hours_logged time_logged,
       concat(rng.rng_type_of_work_code, ' - ', rng.rng_type_of_work_label) type_of_work,
       tsk.tsk_code task,
       concat(sta.wat_activity_code , ' - ', sta.wat_activity_label) activity,
       cct.cct_client_reference client_contract,
       cli.cli_name client_name,
       wko.wko_swip_id wo_id,
       wko.wko_client_reference wo_client_reference,
       concat(sta.wat_workorder_type_code, ' - ', sta.wat_workorder_type_label) wo_type,
       wko.wko_current_status_label wo_status,
       rng.rng_label range_element,
       rnh.rnh_current_status_label range_element_status,
       concat(sta.wat_subactivity_code, ' - ', sta.wat_subactivity_label) sub_activity,
       concat(sta.wat_nature_of_work_code, ' - ', sta.wat_nature_of_work_label) work_nature,
       concat(sta.wat_priority_code, ' - ', sta.wat_priority_label) priority,
       concat(sta.wat_complexity_code, ' - ', sta.wat_complexity_label) complexity,
       concat(sta.wat_skill_level_code, ' - ', sta.wat_skill_level_label) skill_level,
       concat(sta.wat_program_code, ' - ', sta.wat_program_label) program,
       concat(sta.wat_perimeter_code, ' - ', sta.wat_perimeter_label) technical_scope,
       concat(sta.wat_customer_production_center_code, ' - ', sta.wat_customer_production_center_label) client_production_center,
       concat(sta.wat_scenario_code, ' - ', sta.wat_scenario_label) production_scenario,
       concat(sta.wat_application_classification_code, ' - ', sta.wat_application_classification_label) application_ranking,
       concat(sta.wat_customer_technical_leader_code, ' - ', sta.wat_customer_technical_leader_label) client_technical_leader,
       wko.wko_current_start_date wo_start_date,
       wko.wko_current_commit_end_date wo_end_date_commitment,
       wko.wko_last_delivery_date wo_last_delivery_date,
       wko.wko_highest_id highest_level_wo_id,
       wko.wko_highest_client_reference highest_level_wo_client_refence,
       wko.wko_batch_reference wo_batch_reference,
       wko.wko_label wo_descriprion
FROM dwh_swip.swip.f_logged_time tim
INNER JOIN dwh_swip.swip.d_human_resource_hstr hrh ON hrh.hrh_id = tim.tim_hrh_id
INNER JOIN dwh_swip.swip.d_range_element rng ON rng.rng_id = tim.tim_rng_id
INNER JOIN dwh_swip.swip.d_date dat ON dat.dat_id = tim.tim_dat_id
INNER JOIN dwh_swip.swip.d_range_element_hstr rnh ON rnh.rnh_id = tim.tim_rnh_id
INNER JOIN dwh_swip.swip.d_task tsk ON tsk.tsk_id = tim.tim_tsk_id
INNER JOIN dwh_swip.swip.d_workorder wko ON wko.wko_id = tim.tim_wko_id
INNER JOIN dwh_swip.swip.d_client_contract cct ON cct.cct_id = tim.tim_cct_id
INNER JOIN dwh_swip.swip.d_client cli ON cli.cli_id = tim.tim_cli_id
INNER JOIN dwh_swip.swip.d_wo_structuring_attributes sta ON sta.wat_id = tim.tim_wat_id
WHERE exists ( SELECT 1
    FROM swip.b_user_perimeter
    WHERE upr_domain_id = (@UserID) AND upr_active_domain_code = cct.cct_activity_domain_code )
AND hrh.hrh_production_center_swip_code IN (@ProductionCenterSwipCode)
AND cct.cct_activity_domain_code IN (N'ADS_TD',N'ADS_VV_ILL',N'AC_TD',N'BI',N'DEF_TD',N'DOM_MG',N'HE_TD',N'INDUSTRY',N'LLA DOM',N'NAV_CU_SUP',N'NAV_ENG',N'NAV_TD',N'non applicable',N'llaxx',N'unknown')
AND (@ResourceName IS NULL or @ResourceName IS NOT NULL AND hrh.hrh_full_name like N'%'+ @ResourceName + N'%')
AND ('{ALL}' in (@ActivityCode) or '{ALL}' NOT IN (@ActivityCode) AND sta.wat_activity_code IN (@ActivityCode))
AND ('{ALL}' in (@SubactivityCode) or '{ALL}' NOT IN (@SubactivityCode) AND sta.wat_subactivity_code IN (@SubactivityCode))
AND ('{ALL}' in (@WorkNatureCode) or '{ALL}' NOT IN (@WorkNatureCode) AND sta.wat_nature_of_work_code IN (@WorkNatureCode))
AND (-9999 in (@ClientId) or -9999 NOT IN (@ClientId) AND tim.tim_cli_id IN (@ClientId))
AND (-9999 in (@ContractId) or -9999 NOT IN (@ContractId) AND tim.tim_cct_id IN (@ContractId))
AND  tim.tim_dat_id >= (YEAR(@StartDate)*10000 + MONTH(@StartDate)*100 + DAY(@StartDate))
AND  tim.tim_dat_id <= (YEAR(@EndDate)*10000 + MONTH(@EndDate)*100 + DAY(@EndDate) )
nicolasL
  • 136
  • 1
  • 7
  • For better SQL performance help, you need to include your table and index definitions, and share the actual query plan via https://brentozar.com/pastetheplan – Charlieface Jun 24 '21 at 22:31
  • The actual explain plan from SSMS is here. https://www.brentozar.com/pastetheplan/?id=B1xAF-Q2u – nicolasL Jun 25 '21 at 07:47
  • Looking at the plan, I suggest you focus on the implicit conversions: for example `cct_activity_domain_code` is `varchar` so the values should not have `N` in front of them, also some of the variables/parameters are the wrong types. But there are only a few rows here so it won't make that much difference. I can't believe that the query plan for the slow version is the same, please add that also. – Charlieface Jun 25 '21 at 09:01
  • SSRS is automatically generating SQL to handle IN parameters. Maybe I can do something about it. The real problem is, I copy paste the whole query and parameters : 0ms in azuredata studio 7 minutes in ssms. The previous explain plan is the slow one. I'll add the fast one, but for some reason brentozar says the plan is not valid. – nicolasL Jun 25 '21 at 10:15
  • Are you using scalar UDF functions anywhere, for example in a view or computed column? It's weird because you have `ActualElapsedms="27"` and `CompileTime="43"` (these are in milliseconds), the only things I can see of note are `` and `` – Charlieface Jun 25 '21 at 11:41
  • No, I don't. Still: it doesn't explain the different behavior thing. And why if I add 2 nulls columns in the select, it takes 0 seconds in SSMS. – nicolasL Jun 25 '21 at 12:43
  • 1
    That's because you are getting a different plan, because as you change anything (even formatting) then the query is recompiled. Please add the fast plan. – Charlieface Jun 25 '21 at 12:45
  • Here it is https://www.brentozar.com/pastetheplan/?id=BkSHA-whu – nicolasL Jun 28 '21 at 08:54
  • Looks really strange, I suspect that it just a fluke as to which query is getting this problem. It seems that the `MEMORY_ALLOCATION_EXT` wait is causing the problem, but Paul Randal claims [it does not come up generally as an issue](https://www.sqlskills.com/help/waits/memory_allocation_ext/). Are you under memory pressure perhaps? I think we need to get this migrated to [dba.se] for better help, you may want to flag a mod – Charlieface Jun 28 '21 at 11:37

1 Answers1

0

It was an SQL Server bug. Answer from Microsoft support:

ISSUE: You were having performance issues with a specific query. On SSMS and SSRS, it was taking longer to finish. On Azure Data Studio and SQL CMD, it was running faster

Fixes a performance issue when a query outputs NULL values following a semi join in SQL Server 2019 for a database with UTF8.

To solve the issue, you have to apply the latest CU on SQL, which is the CU11

At first Microsoft had trouble figuring it out because they didn't know at that time that the behaviour would change depending on the SQL client. Now they know ^^

nicolasL
  • 136
  • 1
  • 7