Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
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 53

Expert Comment

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

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

580 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