SQL query filling up tempdb

Question

I am running the below query which is failing when it fills up the tempdb (170GB). It fails after around 1 hour.

the script below :

--Select Query BOM collection retail report

Declare @Company Nvarchar(50) ='HMFI'

Declare @Product Nvarchar(50) =Null

select Upper (Be.DataAreaId)Company ,BE.BOM,BE.Product

,Max(ProItemName)ProItemName

,Max(ProUnitID)ProUnitID

,Be.Material,Max(MaterialItemName)MaterialItemName

,Be.UNITID MaterialUnitID

,Sum (Be.BOMQTY)MaterialQty

,Max (MaterialService) MaterialType

from ExpBom_HMFI BE

Outer Apply (SELECT UNITID ProUnitID FROM INVENTTABLEMODULE A With (Nolock) WHERE DATAAREAID = @Company AND A.ITEMID =BE.Product AND MODULETYPE = 0)ProUnitID

Outer Apply(SELECT ITEMNAME ProItemName FROM INVENTTABLE B With (Nolock) WHERE DATAAREAID = @Company AND B.ITEMID = BE.Product)ProItemName

Outer Apply(SELECT ITEMNAME MaterialItemName FROM INVENTTABLE C With (Nolock) WHERE DATAAREAID = @Company AND C.ITEMID = Be.Material)MaterialItemName

Outer Apply(SELECT Case When ITEMTYPE=0 Then 'Item' When ITEMTYPE=1 Then 'BOM' When ITEMTYPE=2 Then 'Service Item' End MaterialService

FROM INVENTTABLE With (Nolock) WHERE DATAAREAID = @Company AND ITEMID = Be.Material)MaterialService

Where BE.DataAreaId in (@Company) and (@Product Is null Or Be.Product In(Select StringValue From Split(@Product,',')))

Group by Be.DataAreaId,BE.BOM,BE.Product,Be.Material ,Be.UNITID

Order By Be.DataAreaId,BE.BOM,BE.Product,Be.Material

option (maxrecursion 0)

--now Viewing the data collected

with ExpBom (

DataAreaId,

Bom,

Product,

Material,

BomDepth,

   BOMQTY,

   Unitid,

BomPath

) as (

select

    bv.DataAreaId,

    bv.BomId,

    bv.ItemId,

    b.ItemId,

    1,

          Convert (NUMERIC(18,8), b.BOMQTY) BOMQTY,

          Convert (Nvarchar(10),b.UNITID )Unitid,

    convert(Nvarchar(max), bv.ItemId + '|' + b.ItemId) as BomPath

  

from BomVersion bv With (Nolock)

join InventTable ibv With (Nolock)

    on ibv.DataAreaId = bv.DataAreaId

    and ibv.ItemId = bv.ItemId

join Bom b With (Nolock)

    on b.DataAreaId = bv.DataAreaId

    and b.BomId = bv.BomId

join InventTable ib With (Nolock)

    on ib.DataAreaId = b.DataAreaId

    and ib.ItemId = b.ItemId

  

where bv.Approved = 1

    and bv.Active = 1

    and bv.FromDate < getdate()

    and (bv.ToDate = '01-01-1900' or bv.ToDate >= getdate())

    and b.FromDate < getdate()

    and (b.ToDate = '01-01-1900' or b.ToDate >= getdate())

      and b.DATAAREAID  in ('HMFI')

union all

select

    bv.DataAreaId,

    bv.BomId,

    bv.ItemId,

    eb.Material,

    eb.BomDepth + 1,

          Convert (NUMERIC(18,8),B.BOMQTY * eb.BOMQTY)BOMQTY,

          Convert (Nvarchar(10),eb.UNITID )Unitid,

    convert(Nvarchar(max), bv.ItemId + '|' + eb.BomPath) as BomPath

         

from BomVersion bv With (Nolock)

join InventTable ibv With (Nolock)

    on ibv.DataAreaId = bv.DataAreaId

    and ibv.ItemId = bv.ItemId

join Bom b With (Nolock)

    on b.DataAreaId = bv.DataAreaId

    and b.BomId = bv.BomId

join ExpBom eb

    on eb.DataAreaId = b.DataAreaId

    and eb.Product = b.ItemId

  

where bv.Approved = 1

    and bv.Active = 1

    and bv.FromDate < getdate()

    and (bv.ToDate = '01-01-1900' or bv.ToDate >= getdate())

    and b.FromDate < getdate()

    and (b.ToDate = '01-01-1900' or b.ToDate >= getdate())

      and b.DATAAREAID  in ('HMFI')

)

select * from ExpBOM

Where Material Not in (Select BOMV.ITEMID From BomVersion BOMV With (Nolock) Where BOMV.DataAreaId In( 'HMFI' ) and BOMV.Approved = 1

    and BOMV.Active = 1

    and BOMV.FromDate < getdate()

    and (BOMV.ToDate = '01-01-1900' or BOMV.ToDate >= getdate()) )

I'm not sure if the JOINS are causing the issue

Estimated execution plan is below:

Data collection : https://www.brentozar.com/pastetheplan/?id=S1UsXn4Po

Data view: https://www.brentozar.com/pastetheplan/?id=BJDUBn4wi

Please advise

this report was working fine on daily basis without filling tempdb usualy it was taking 1 min to execute suddenly it stoped for unknown reason although there's no changes done on server/database levels

SQL query filling up tempdb

0 Answers0