sql2008 T-sql query

Posted on 2014-11-20
Medium Priority
Last Modified: 2014-12-10
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'
 ,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)
 ,'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)

 ,'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)

  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

Open in new window

Question by:motioneye
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 23

Expert Comment

ID: 40456355
Please post the show plan..

you get it by :
> run
set showplan_all on;

Open in new window

> then highlight and run your query
> post the output on the thread
LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 40456495
Joins involving concatenations will always be inefficient
Joins involving functions (such as rtrim) will always be inefficient
"SELECT DISTINCT" adds time and effort to the query, there are usually better ways.

1. You should be able to avoid the concatenations

left outer join dbo.ADUsers pdy ON jxt.AccountDomain = pdy.domain
           AND  jxt.AccountName = pdy.BaccName

 left outer join dbo.vWSPrivilegedUsersOneWeek wspuow ON xbn.AccountDomain = jxt.AccountDomain
           AND xbn.AccountName = jxt.AccountName
           AND xbn.[WSLG ServerName]=rtrim(jxt.[Network Node Primary Name])

2. Try it without the RTRIM()

Is that RTRIM() really needed?
AND xbn.[WSLG ServerName]=rtrim(jxt.[Network Node Primary Name]

Try it without that.
If it is necessary is there anyway you can keep that data clean?

3. Get rid of the DISTINCT

Look at each of the joined tables. Find a way to use subqueries that don't multiply the number of result rows, often these subqueries will involve using GROUP BY.

Select Distinct is returning duplicates ...
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40456806
I'm wondering why are you using MAXDOP 6?
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40457778
I don't see the source of the "jxt" alias anywhere ... where does it come from?

Featured Post

Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question