Help in query optimization

Question

Post reply

Help in query optimization

shaikh.kurshid

Old Hand

Points: 301
More actions
June 30, 2011 at 8:03 am

#238672

Hello Friends,
Pls help to optimize the query....
SELECT VW.MEMBERID,VW.SITEID,VW.Site_Name, L1.REGID,VW.ResourceName,L1.CAPTION,L1.CFREESPACE,LastScanDate,VW.resmonstatus ,'10584' AS ConditionID
FROM ViwResourceDPMA_BOS VW WITH (NOLOCK)
LEFT JOIN MstAlertSUP_RegID SUP WITH(NOLOCK) ON SUP.REGID=VW.REGID AND ConditionID=10584
INNER JOIN
(
SELECT REGID,CAPTION,CFREESPACE,CONVERT(VARCHAR(10),SCANDATETIME,101) LastScanDate,FILESYSTEM,USEDSPACE
FROM AP_LOGICALDRIVESINFORMATION L WITH (NOLOCK)
WHERE L.SCANDATETIME=(SELECT MAX(SCANDATETIME)
FROM AP_LOGICALDRIVESINFORMATION WITH (NOLOCK)
WHERE REGID = L.REGID )
AND L.DRIVETYPE = @drive
and(case when Regid= 893487 and VolumeName='RECOVERY' then 1
When Regid=1316678 and VolumeSerialNumber='300B7B1E' then 1
When Regid=1251617 and VolumeSerialNumber='3D214137' then 1
When Regid=1434490 and VolumeSerialNumber='B43C0AF9' then 1
When Regid=1434874 and VolumeSerialNumber='B43C0AF9' then 1
When Regid=1435806 and VolumeSerialNumber='B43C0AF9' then 1
else 0 end
)=0
AND (CONVERT(Numeric(18,0),FREESPACE) < @freespace AND CONVERT(Numeric(18,0),FREESPACE) <> 0)
and VolumeName not in('System Reserved','HP_TOOLS')
) L1 ON L1.REGID=VW.REGID
WHERE RESTYPE = 1 AND REGTYPE =@DPMA and resmonstatus <> 4
AND DCTIME >=DATEADD(D,-3,GETDATE())
AND SUP.REGID IS NULL and VW.REGID>@id
GROUP BY VW.MEMBERID,VW.SITEID,VW.Site_Name,L1.CAPTION,L1.CFREESPACE,L1.REGID,VW.ResourceName,LastScanDate,VW.resmonstatus
ORDER BY VW.SITEID
Option (Optimize for (@Drive='-1',@DPMA='-1'))
I have tried all tricks but still facing problem in optimization.....
Cost is 38 with parallelism
Cache 424
if i tried to remove parallelism thn cost increased to 699....
Please help me out

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

Check the execution paln or share it here.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass