Guys,
I have a query below which running very long and takes too much resources, any idea what will be the better way to improve this query ? if I don't specific MaxDOP, this query can cause CPU fluctuate at 80-100% consume rate, and this such query run almost 8 hours without complete.
select distinct xbn.[WSLG ServerName] 'WS P ServerName' ,xbn.[WSLG Groupname] 'WS P Groupname' ,xbn.AccountDomain 'WS P AccountDomain' ,xbn.AccountName 'WS P AccountName' ,'WS Type' = case xbn.[WSLG Type] when 'LUM' then '(LUM) Local User Member' when 'GUM' then '(GUM) Global User Member' when 'GGM' then '(GGM) Global Group Member' when 'LU' then '(LU) Local User' end ,qdt..Account 'WS Account Exist in Server' ,jxt.[Classification] 'NQ Classification' ,jxt.[Platform] 'NQ Platform' ,jxt.[Network Node Name] 'NQ Log Source (N.N.N.)' ,jxt.[Target User] 'NQ Target User' ,jxt.[Native Event Classification]'NQ Event ID' ,jxt.[UTC Date] 'NQ UTC Date' ,jxt.[Count] 'NQ Count' ,jxt.[AccountDomain] 'NQ AccountDomain' ,jxt.[AccountName] 'NQ AccountName' ,jxt.[Source IP] 'NQ Source IP'' ,'Account is Disabled' = case IsNull(qdt..Account,'') when '' then (case pdy.[Disabled] when 1 then 'Yes' when 0 then 'No' else NULL end) else (case qdt..[Disabled] when 'True' then 'Yes' when 'False' then 'No' else NULL end) end ,'Account is Locked' = case IsNull(qdt..Account,'') when '' then (case pdy.[Locked] when 1 then 'Yes' when 0 then 'No' else NULL end) else (case qdt..[Locked] when 'True' then 'Yes' when 'False' then 'No' else NULL end) end ,'Passw. Never Expires' = case IsNull(qdt..Account,'') when '' then (case pdy.[PWneverexpires] when 1 then 'Yes' when 0 then 'No' else NULL end) else (case qdt..[PWneverexpires] when 'True' then 'Yes' when 'False' then 'No' else NULL end) end from dbo.DataLoginST inf left outer join dbo.ADUsers pdy on (jxt.AccountDomain+jxt.AccountName)=(pdy.domain+pdy.BaccName) left outer join dbo.WSLocalUsers qdt. on qdt..ServerName=jxt.AccountDomain and qdt..Account=jxt.AccountName left outer join dbo.vWSPrivilegedUsersOneWeek wspuow on (xbn.AccountDomain+xbn.AccountName)=(jxt.AccountDomain+jxt.AccountName)and xbn.[WSLG ServerName]=rtrim(jxt.[Network Node Primary Name]) where jxt.[Count] >=2 OPTION (MAXDOP 6);
you get it by :
> run
Open in new window
> then highlight and run your query> post the output on the thread