asked on

sql2008 T-sql query

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);

Open in new window