May 3, 2024 at 9:32 pm
Experts, I am learning some skills so I can troubleshoot some performance-related issues. I have gotten good with some basics but when it comes to query which gets the data from tens of tables, I get lost, especially when I look at the execution plan. I am hoping if someone would give me some tips on what to look for on the execution plan I am sharing.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 4, 2024 at 10:44 am
Experts, I am learning some skills so I can troubleshoot some performance-related issues. I have gotten good with some basics but when it comes to query which gets the data from tens of tables, I get lost, especially when I look at the execution plan. I am hoping if someone would give me some tips on what to look for on the execution plan I am sharing.
Where did you share it?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 4, 2024 at 11:09 am
if you tried to upload a sqlplan here it won't work - you need to use https://www.brentozar.com/pastetheplan/ and share the link to it here
May 4, 2024 at 1:04 pm
Thanks frederico_fonseca
This is the url
https://www.brentozar.com/pastetheplan/?id=S1e6Wn7MR
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 4, 2024 at 1:44 pm
not much you can do here other than rewrite that view (and likely the views contained within it).
likely best thing is to see what really needs to be retrieved and how to get it and write the SQL directly instead of using the view - and if required using a few temp tables to (potentially) speed up things by requiring less overall memory to be allocated.
from the plan itself there are a few things you can do - lots of key lookup to get column LIST_NAME - you should consider adding these as a INCLUDE column to the index that is being used to retrieve remaining data - but this won't really change performance much on this case.
May 6, 2024 at 2:42 pm
Woof!
You have a bunch of plan affecting conversion warnings. I'd look to those as a first step. Nesting views, having a view that calls other views which joins to views, etc., is a very, VERY, bad coding choice when it comes to SQL Server. You're demanding the optimizer perform miracles, teasing out all the various tables to put together an execution plan. In this case, the optimizer has given up. You can tell this by looking at the properties of the first operator, the SELECT operator. Inside there is "Reason for early termination" of the execution plan. In this case, it's Timeout. That means the optimizer wasn't able to finish optimizing and has simply provided you with a functional, not an optimal, execution plan.
In the plan a few things pop out. For example, you have this many estimated rows: 9955460000000. But the actual value is: 1278024. That's a huge disparity. Suggestion here, might be that you need to get your statistics up to date. However, again, this could simply be an artifact of the unoptimized plan. I see a bunch of key lookup operations. It's possible there are indexes you could add or modify to help eliminate those.
Best bet, rewrite the code so that it's not a gigantic, everything covering everything query. Get the data you need from the tables you need. Then, when you need other data, get that. Each in distinct queries. Get away from this idea of trying to hide the complexity of the system behind a view. It's just going to keep giving you problems. Also, the WHERE clause is our friend. Moving all the data is always going to be painful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2024 at 2:47 pm
And, it looks like the code is hitting this table, [CoreV2].[HCM].[Employee_Info_Master].[EmployeeInfoMaster_List_Name_Top] [emp_CA], over and over again. You shouldn't have to reference the same table repeatedly unless you're getting different data out, but this looks like it's all the same data. Person_number, eff_start_date and effseq are referenced over and over. Eliminate this duplication. And, maybe add list_name as an include column on that index so you get rid of the key lookup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2024 at 11:08 pm
So, the next step is... post the code for the view.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2024 at 4:00 pm
WITH STATUS AS
(
SELECT BUSINESS_UNIT, PROJECT_ID, effdt, Project_Status, ROW_NUMBER() OVER (Partition BY Project_ID Order by effdt desc, effseq desc) as RN from PS.Project_Status
)
Select Distinct
prj.Business_Unitas ProjectBusinessUnit
,B.descras ProjectBusinessUnitName
,CAST (NULL as BIGINT)as Project_ID
,prj.Project_IDas ProjectNumber
,prj.descras ProjectDescription
,CAST(NULL as nvarchar(max))as ProjectDescription
,CAST(NULL as nvarchar(240))as ProjectName
,CAST(NULL as BIGINT)as ProjectCustomerID
,prj.Project_User2as ProjectCustomerNumber
,cust.Name1as ProjectCustomerName
,cust.Corporate_Cust_IDas ProjectCorporateCustomerNumber
,corp_Cust.Name1as ProjectCorporateCustomerName
,CAST(NULL as BIGINT)as ProjectContractID
,prj2.Cotract_Numas ProjectContractNumber
,cnt.descr
,prj.Project_Type
,pt.descr
,CAST(ef.xlatlogname as NVARCHAR(30))
,prj.PC_MSP_USR1
,CAST(prj.Start_DT as Date)
,CAST(prj.End_DT as Date)
,ORG.PRJ_LEVEL1_CF_VAL
,LE.DESCR
,CRM.crmaccount
,CRM.opportunityID
,CAST(NULL AS VARCHAR(3))
,P_inf.PA
,CAST(emp_PA.LIST_NAME as NVARCHAR(50))
,P_inf.PM
,CAST(emp_PM.LIST_NAME as NVARCHAR(50))
,P_inf.CA
,CAST(emp_CA.LIST_NAME as NVARCHAR(50))
,P_inf.CL
,CAST(emp_CL.LIST_NAME as NVARCHAR(50))
,P_inf.BA
,CAST(emp_BA.LIST_NAME as NVARCHAR(50))
,P_inf.BS
,CAST(emp_BS.LIST_NAME as NVARCHAR(50))
,NULLas ProjectOrganizationID
,NULLas ProjectOrganizationName
,org.PRJ_LEVEL2_CF_VAL
,OP.descr
,org.PRJ_LEVEL3_CF_VAL
,CAST(DEP.descr as NVARCHAR(30))
,NULLas ProjectOrganizationSegmentID
,NULLas ProjectOrganizationSegmentName
,NULLas ProjectOrganizationSubSegmentID
,NULLas ProjectOrganizationSubSegmentName
,NULLas ProjectOrganizationDeptRollupID
,NULLas ProjectOrganizationDeptRollupName
,NULLas ProjectOrganizationGeographyID
,NULLas ProjectOrganizationGeographyName
,NULLas ProjectOrganizationMarketID
,NULLas ProjectOrganizationMarketName
,CNT.Ship_To_Cust_ID
,CNT.Cust_Name_Ship
,CAST(S.project_status as NVARCHAR(3))
,CAST(s.effdt as date)
,CAST(prj2.last_bill_date as date)
,CAST(prj2.last_Coll_date as date)
,CAST(PS1.effdt1 as date)
,CAST(PS2.effdt2 as date)
,CAST(prj.FMS_DTTM_STAMP as date)
,IIF(S.Project_Status = 'I', CAST(s.effdt as Date), NULL) as ProjectCloseDate
,CAST(prj.DTTM_STAMP as date)
From ps.project as prj
Left Join (select setid, project_Type, effdt, descr from ps.proj_type_tbl a where effdt = (select max(effdt) from ps.proj_type_tbl where setid = a.setid and project_type = a.project_type and effdt <=getdate())) as PT
on prj.project_type = PT.project_type
and prj.BusinessUnit = pt.setid
LEFT JOIN ps.customer as CUST
on cust.cust_id = prj.project_user2
and cust.setid = 'SHARE'
Left Join PS.customer as Corp_cust
on corp_cust.cust_id = cust.customer_cust_id
and cust.setid = 'SHARE'
Left Join PS.PSA_ORGPRN_DEFN as org
on org.project_id = prj.project_id
and prj.Business_unit.org.Business_unit
and org.effdt = (select max(H_ED.effdt) from PS.PSA_ORGPRN_DEFN H_.ED where org.Business_unit = H_ED.Business_unit and org.project_type = H_ED.project_ID and H_ED.effdt <=getdate())
Left Join (Select fieldname, fieldvalue, effdt, eff_status, xlatloginname from ps.xlatitem where fieldname = 'Eff_Status' and eff_status = 'A') as ef
On ef.fieldvalue = prj.eff_status
LEFT JOIN (select Project_ID, CA, CL, PM, PA, BS, BA From
(
Select project_id, user_Type_mgmt, emplid
From Prog_mgmt p_inf
Where user_Type_mgmt in ('CA', 'CL', 'PM', 'PA', 'BS', 'BA')
and p_inf.DTTM_STAMP = (Select max(DTTM_STAMP)
from Prog_mgmt p_inf2
Where p_inf.emplid = p_inf2.emplid
and P_inf.Business_unit = P_inf2.Business_unit
and P_inf.user_Type_mgmt = P_inf2.user_Type_mgmt) ) d
PIVOT (max (emplid)) for user_Type_mgmt in ( [CA], [CL], [PM], [PA], [BS], [BA]) ) piv) p_inf
on prj.Project_id = p_inf.Project_id
Left join HCM.Employee_Info_Masteras emp_pm
on p.inf.PM = emp_pm.Person_Number
and emp_pm.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Masteras emp_pa
on p.inf.Pa = emp_pa.Person_Number
and emp_pa.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Masteras emp_ca
on p.inf.sa = emp_sa.Person_Number
and emp_sa.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Masteras emp_cl
on p.inf.cl = emp_cl.Person_Number
and emp_cl.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Masteras emp_ba
on p.inf.ba = emp_ba.Person_Number
and emp_ba.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Masteras emp_bs
on p.inf.bs = emp_bs.Person_Number
and emp_bs.TOP_OF_STOCK_TODAY = 1
Left Join ps.PRJ_RPT_TBLas PRJ2
on prj.Business_Unit = prj2.Business_Unit
And prj.project_ID = prj2.project_ID
Left Join ps.CON_RPT_TBLas CNT
on prj2.Contract_num = cnt.contract_num
Left Join CRM.CRM_Projectsas CRM
on crm.order_number = prj.project_id
Left Join Statusas S
on S.project_ID = prj.project_ID
AND S.Business_Unit = prj.Business_Unit
Left Join BUS_UNIT_TBL_FSas B
on B.Business_Unit = prj.Business_Unit
Left Join BUS_UNIT_TBL_FSas LE
on LE.Business_Unit = org.PRJ_LEVEL1_CF_VAL
Left Join ps.dept_tblas DEP
on dep.deptid = org.PRJ_LEVEL3_CF_VAL AND dep.eff_status = 'A' and dep.effdt = (Select max(effdt) from dept_tbl DEP1 Where dep1.deptID = org.PRJ_LEVEL3_CF_VAL and dep1.eff_status = 'A')
Left join ps.oper_unit_tblas OP
on op.operating_unit = org.PRJ_LEVEL2_CF_VAL AND OP.eff_status = 'A' and OP.effdt = (Select max(effdt) from ps.oper_unit_tbl OP1 Where op1.operating_unit = org.PRJ_LEVEL2_CF_VAL AND OP1.eff_status = 'A')
Left join (Select Business_Unit, Project_ID, min(effdt) as EFFDT1 from Status Where project_status = 'O' group by Business_unit, Project_ID) as PS1
on PS1.Business_Unit = prj.Business_Unit and PS1.Project_ID = prj.Project_ID
Left join (Select Business_Unit, Project_ID, min(effdt) as EFFDT2 from Project_Status Where project_status = 'I' group by Business_unit, Project_ID) as PS2
on PS2.Business_Unit = prj.Business_Unit and PS2.Project_ID = prj.Project_ID
Where s.rn = 1
GO
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 8, 2024 at 6:02 pm
and the views used? user_Type_mgmt is a view as well - but in this case I don't think it won't matter its content
if you can replace this query and stop using the view the first thing to do is to remove the following block from the code and do it in advance of the main query, outputting into a temp table
LEFT JOIN (select Project_ID, CA, CL, PM, PA, BS, BA
From ( Select project_id, user_Type_mgmt, emplid
From Prog_mgmt p_inf
Where user_Type_mgmt in ('CA', 'CL', 'PM', 'PA', 'BS', 'BA')
and p_inf.DTTM_STAMP = (Select max(DTTM_STAMP)
from Prog_mgmt p_inf2
Where p_inf.emplid = p_inf2.emplid
and P_inf.Business_unit = P_inf2.Business_unit
and P_inf.user_Type_mgmt = P_inf2.user_Type_mgmt)
) d
PIVOT (max (emplid))
for user_Type_mgmt in ( [CA], [CL], [PM], [PA], [BS], [BA])
) piv
) p_inf
on prj.Project_id = p_inf.Project_id
Left join HCM.Employee_Info_Masteras emp_pm
on pmg.PM = emp_pm.Person_Number
and emp_pm.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Masteras emp_pa
on pmg.Pa = emp_pa.Person_Number
and emp_pa.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Masteras emp_ca
on pmg.sa = emp_sa.Person_Number
and emp_sa.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Masteras emp_cl
on pmg.cl = emp_cl.Person_Number
and emp_cl.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Masteras emp_ba
on pmg.ba = emp_ba.Person_Number
and emp_ba.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Masteras emp_bs
on pmg.bs = emp_bs.Person_Number
and emp_bs.TOP_OF_STOCK_TODAY = 1
this will require experimentation - and potentially 2 temp tables as the existing code is converting person_number (from employee_info_master) into a NVARCHAR(11) so it can join the elplid (from prog_mgmt above)
the output from the pivot above needs to match the datatype of employee_info_master so correct join can be made and indexes used fully
so possible way - not necesseraly the best!!!!!
below IS LIKELY to contain syntax errors.
-- temp table to contain the pivoted data
drop table if exists #prog_mgmt;
select p_inf.Project_ID
, p_inf.CA
, p_inf.CL
, p_inf.PM
, p_inf.PA
, p_inf.BS
, p_inf.BA
-- following ones do a convert to the datatype of employee_info_master.person_number
, convert(--datatype_and_size--, p_inf.CA) as CA_key
, convert(--datatype_and_size--, p_inf.CL) as CL_key
, convert(--datatype_and_size--, p_inf.PM) as PM_key
, convert(--datatype_and_size--, p_inf.PA) as PA_key
, convert(--datatype_and_size--, p_inf.BS) as BS_key
, convert(--datatype_and_size--, p_inf.BA) as BA_key
into #prog_mgmt
from (select Project_ID, CA, CL, PM, PA, BS, BA
From ( Select project_id, user_Type_mgmt, emplid
From Prog_mgmt p_inf
Where user_Type_mgmt in ('CA', 'CL', 'PM', 'PA', 'BS', 'BA')
and p_inf.DTTM_STAMP = (Select max(DTTM_STAMP)
from Prog_mgmt p_inf2
Where p_inf.emplid = p_inf2.emplid
and P_inf.Business_unit = P_inf2.Business_unit
and P_inf.user_Type_mgmt = P_inf2.user_Type_mgmt)
) d
PIVOT (max (emplid))
for user_Type_mgmt in ( [CA], [CL], [PM], [PA], [BS], [BA])
) piv
) p_inf
;
-- temp table to contain the the employee data
-- note that if the LIST_NAME is not added to the underliying index on Employee_Info_Master it may be best to create yet another temp table
-- with the output if a select Person_number and LIST_NAME from Employee_Info_Master where TOP_OF_STOCK_TODAY = 1 - that table should then have an index on person_number
-- and the query below should remove the TOP_OF_STOCK_TODAY = 1 filter
drop table if exists #prog_employee;
select pmg.* -- expand to have all column names
-- cast being done here instead of main query - should be removed from it
, CAST(emp_PA.LIST_NAME as NVARCHAR(50)) as LIST_NAME_PA
, CAST(emp_PM.LIST_NAME as NVARCHAR(50)) as LIST_NAME_PM
, CAST(emp_CA.LIST_NAME as NVARCHAR(50)) as LIST_NAME_CA
, CAST(emp_CL.LIST_NAME as NVARCHAR(50)) as LIST_NAME_CL
, CAST(emp_BA.LIST_NAME as NVARCHAR(50)) as LIST_NAME_BA
, CAST(emp_BS.LIST_NAME as NVARCHAR(50)) as LIST_NAME_BS
into #prog_employee
from #prog_mgmt pmg
Left join HCM.Employee_Info_Master as emp_pm
on pmg.PM = emp_pm.Person_Number
and emp_pm.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Master as emp_pa
on pmg.Pa = emp_pa.Person_Number
and emp_pa.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Master as emp_ca
on pmg.sa = emp_sa.Person_Number
and emp_sa.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Master as emp_cl
on pmg.cl = emp_cl.Person_Number
and emp_cl.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Master as emp_ba
on pmg.ba = emp_ba.Person_Number
and emp_ba.TOP_OF_STOCK_TODAY = 1
Left join HCM.Employee_Info_Master as emp_bs
on pmg.bs = emp_bs.Person_Number
and emp_bs.TOP_OF_STOCK_TODAY = 1
-- although we are creating the clustered index here it may be better to create the table in advance
-- add the index and only then insert the data from the query above into it - testing of which one is best required
create clustered index CI_#prog_employee on #prog_employee
(Project_ID
)
-- with (data_compression = page) -- try with and without to see which one behaves better
this table would then be the one used intead of the block at the top, and the select columns would be the ones from this table
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply