sql2008 T-sql query

Posted on 2014-11-20
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
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 48

Accepted Solution

PortletPaul earned 500 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 48

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

821 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