Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql query fills up tempdb with greater than 300 gb Expand / Collapse
Author
Message
Posted Thursday, February 06, 2014 10:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 5:49 AM
Points: 44, Visits: 179
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


Post #1539010
Posted Friday, February 21, 2014 10:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 10:56 PM
Points: 1,111, Visits: 535
Share the execution plan of the query.
Also check whether the table(s) has required index(s).
Post #1544126
Posted Monday, February 24, 2014 1:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:22 PM
Points: 1,192, Visits: 2,174
Post the execution plan ..

--
SQLBuddy
Post #1544694
Posted Monday, February 24, 2014 2:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:52 PM
Points: 235, Visits: 1,174
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.


Post #1544721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse