sql query fills up tempdb with greater than 300 gb

  • Hi,

    Below mentioned query, whenever it runs it fills up tempdb space and resulting disk space issues and it's taking more than 10 hours to complete. I have checked the indexes and statistics of the related tables of that query they are not fragmented. Please help me resolving the issue-

    Query-

    SELECT distinct '0' AS sno, a.iOdfno AS id, a.iAcNo, a.iShipToParty, a.cPType, REPLACE(a.cPType, '62', 'OTS') AS Ptype,

    a.cSiteCode, a.iId, a.cPName, a.iId AS scopeid, a.iFranchiseeAcNo,a.icno as ContactNo,

    isnull(d.vcAddr_Street,'')+isnull(d.vcaddr_street1,'')+isnull(d.vcaddr_loc,'') as Address ,a.dtExpDate, a.cSONo,

    b.vcAcName, c.vcAcName AS FName, d .vcAddr_City, e.vcCityName, ISNULL(a.cUsedFor, '') AS UsedFor, a.iJSNo,

    ISNULL(a.vcRodlcNo, '') AS vcRodlcNo, ISNULL(a.dtActDate, '') AS ActDate, IsNull(a.crono, '') AS LinEid,

    CASE WHEN a.vcRodlcNo IS NULL THEN 'RODLC NO. not filled'

    WHEN a.iFranchiseeAcNo IS NULL THEN 'Franchisee assignment not done'

    WHEN a.dtActDate = '1/1/1900' OR a.dtActDate IS NULL THEN 'ACD not filled'

    WHEN a.dtActDate IS NOT NULL AND a.dtActDate <> '1/1/1900' THEN 'Complete'

    END AS status,

    z.cname AS Hub, a.csapcode,case when o.vcfilename is null then 'No' else

    '<a href=OpenFileViewer1(''IC'',''' + rtrim(a.iodfno) + ''',''H'','

    + convert( varchar(30), a.iid) + ')> View File </a>' end as vcfile,a.dtjsheet_trigger,isnull(O.dtICUplaod,'') as dtICUplaod ,isnull(a.dtacdentereddate,'') as dtacdentereddate

    ,isnull(inv.invoiceno,'') as [InvoiceNo],case when o.vcfilename is null then 'No' else 'Yes' end as IcUploadStatus ---,inv1.Invoice_amt,inv1.Invoice_date

    FROM

    (

    select * FROM R_Scope WHERE (iOdfno <> '0-0') and (cPType = '62') AND (cSONo IS NOT NULL)

    ) a

    left outer join rl_jsheet_ots o on a.iid=o.iscopeid

    INNER JOIN SFA.dbo.Account b ON a.iAcNo = b.iAcNo

    inner join sFA.dbo.codes z ON b.chub = z.ccode

    left outer JOIN SFA.dbo.Account c ON c.iAcNo = a.iFranchiseeAcNo

    INNER JOIN dbo.R_sitemaster d ON a.cSiteCode = d .vcSite_Code

    LEFT OUTER JOIN SFA.dbo.State_City e ON d .vcAddr_City = e.iId

    inner join

    rec_revenue_sap_new inv on ltrim(rtrim(a.icno))=convert(varchar,inv.icno) and ltrim(rtrim(a.crono))=convert(varchar,inv. crono)-- left outer join

    Thanks & Regards

    Rahul Singh

  • Share the execution plan of the query.

    Also check whether the table(s) has required index(s).

  • Post the execution plan ..

    --

    SQLBuddy

  • http://technet.microsoft.com/en-us/library/ms190646.aspx

    To save an execution plan by using SQL Server Management Studio options

    Generate either an estimated execution plan or an actual execution plan by using Management Studio.

    In the Execution plan tab of the results pane, right-click the graphical execution plan, and choose Save Execution Plan As.

    As an alternative, you can also choose Save Execution Plan As on the File menu.

    In the Save As dialog box, make sure that the Save as type is set to Execution Plan Files (*.sqlplan).

    In the File name box provide a name, in the format <name>.sqlplan, and then click Save.

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

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