SQL Server Performance

To reduce CPU cost being used by SORT

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sqlwars, Apr 26, 2010.

  1. sqlwars New Member

    We are using a query and in its execution plan it utilizes 182% CPU cost while performing a sort. Please advice on how to reduce it. query structure: SELECT DISTINCT'Dupility' as Rery_Cls,'LINE' Rery_Tpe,b.clpe, b.Check_Ref_ID, min(a.claim_id) oriim,convert(varchar,getdate(),101) Load_Date,c.year_month_num load_month,0 as include_flag, 0 as isreviewedfrom DBO.PR_PaieDups a with(nolock, index(PR_PaidCls1))join DBO.PR_Paps b with(nolock, index(PR_Paups1))on a.subscriber_id = b.subscriber_id--Subscriberjoin dw.dbo.date c with(nolock) on convert(varchar,getdate(),101) = convert(varchar,c.date_id,101)left outer join CIes z on b.claim_id = z.claim_nbr and z.recovery_class = 'Dupllity'left outer join tbl_ble1 y on b.claid = y.claim_nbr and y.re_css = 'Dupllity'where a.from_date = b.from_date--From Date (DOS)and a.se_id = b.seer_id--Provider IDand a.billed_amount = b.billed_amount--Billed Amountand a.procode = b.procde--Proc Codeand a.tos = b.tos--TOSand a.rede = b.revode--Rev Codeand a.pos = b.pos--POSand a.mod_1 = '' and b.mod_1 = ''--null modifiersand a.mod_2 = '' and b.mod_2 = ''and a.mod_3 = '' and b.mod_3 = '' and a.mod_4 = '' and b.mod_4 = ''and a.root_claim_id < b.root_claim_id--original claim ID is less than the duplim IDand b.claicd = 'H'--Facility Claimsand b.pait > 0and a.claidate
  2. Adriaan New Member

    See what happens if you remove all or some of the WITH query hints.
  3. sqlwars New Member

    Thank you. We cheked but though the RID look up decreased by 1%, the sort which is taking 182% CPU, still remains the same.

Share This Page