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 45

Expert Comment

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

Expert Comment

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now