我为一个客户创建了一个查询，我一直在调整它，试图让它正好满足他们的需要。我几乎把它调好了，除了一些奇数。经过一个严肃的掌心时刻，我意识到，在计数和总和不同的扔了我的数字，所以我删除了他们。
但是，现在查询似乎永远运行，以前需要2-3分钟。我让它运行了一夜，5小时后崩溃，错误如下：
无法为数据库“tempdb”分配新页，因为文件组“default”中的磁盘空间不足。通过删除文件组中的对象、向文件组中添加其他文件或为文件组中的现有文件启用自动增长来创建必要的空间。
tempdb.mdf是16gig的，我有7个tempdb_mssql#ndf文件，每个文件都是16gig的，我的日志文件是4gig的，非常高兴地把我的硬盘填满了。
我重新启动了服务器，缩小了数据库，所以我回到了原点，但我不明白为什么它会这样做。
以下是导致问题的查询：

SELECT
    DISTINCT ap.[SourcePartID] AS [Assembly Part ID],
    p.[PART_X] AS [Assembly Part #],
    p.[DESCR_X] AS [Assembly Part Description],
    oa2.[Part Count],
    oa2.[Total # of Parts],
    ([dbo].[fn_getFactoryStdCost](ap.[SourcePartID])) AS [Factory Std Cost],
    oa2.[# of Docs],
    oa2.[# of Software],
    oa2.[# of Std Cost Parts],
    oa2.[# of HR Devices],
    oa2.[Sum HR Devices],
    oa2.[# of 3rd Party Devices],
    oa2.[Sum 3rd Party Devices],
    oa2.[# of Robots],
    oa2.[Sum of Robots],
    oa2.[# of Buy Parts],
    oa2.[# of Make Parts],
    oa2.[# of Ref Parts],
    bom.[Make/Buy]

  INTO bomSummary   
  FROM AllPartsList ap
    LEFT JOIN visuser.EN_PART p
      ON p.[EN_Part_ID] = ap.[SourcePartID]
    LEFT JOIN bomBreakdown bom
      ON bom.[SourcePartID] = ap.[SourcePartID]
    OUTER APPLY (
        SELECT
            [Part Count]                = COUNT(    DISTINCT IIF(   bom.[Qty] = 0, null,                                                bom.[Component Part #]) ),  
            [Total # of Parts]          = SUM(bom.[Qty]),
            [# of Std Cost Parts]       = COUNT(    IIF( ( [dbo].[fn_getFactoryStdCost]([ComponentPartID]) > 0 ),                   bom.[Component Part #], null)   ),
            [# of Docs]                 = COUNT(    IIF(   bom.[Commodity Code] IN ('009', '072', '073', '075', '079', '082'),  bom.[Component Part #], null)   ),  -- Commodity Codes: 009, 072, 073, 075, 079, 082  :  Commodity ID: 15, 84, 85, 87, 81, 92
            [# of Software]             = COUNT(    IIF(   bom.[Commodity Code] IN ('034'),                                 bom.[Component Part #], null)   ),  -- Commodity Code 034  :  Commodity ID: 28

            [# of HR Devices]           = COUNT(    IIF( ( bom.[Commodity Code] IN ('002') AND [dbo].[fn_getFactoryStdCost]([ComponentPartID]) > 0  AND bom.[Qty] > 0), bom.[Component Part #], null)   ),  -- Commodity Code 002  :  Commodity ID: 11
            [Sum HR Devices]            = SUM(      IIF( ( bom.[Commodity Code] IN ('002') AND [dbo].[fn_getFactoryStdCost]([ComponentPartID]) > 0  AND bom.[Qty] > 0), bom.[Qty], null)    ),              -- Commodity Code 002  :  Commodity ID: 11

            [# of 3rd Party Devices]    = COUNT(    IIF(   bom.[Commodity Code] IN ('007'),                                 bom.[Component Part #], null)   ),  -- Commodity Code 007  :  Commodity ID: 5
            [Sum 3rd Party Devices]     = SUM(      IIF( ( bom.[Commodity Code] IN ('007') AND bom.[Qty] > 0),                  bom.[Qty], null)    ),              -- Commodity Code 007  :  Commodity ID: 5

            [# of Robots]               = COUNT(    IIF( ( bom.[Commodity Code] IN ('005') AND bom.[Make/Buy] = 'B' ),          bom.[Component Part #], null)   ),  -- Commodity Code 005  :  Commodity ID: 13
            [Sum of Robots]             = SUM(      IIF( ( bom.[Commodity Code] IN ('005') AND bom.[Make/Buy] = 'B' ),          bom.[Qty], null)    ),              -- Commodity Code 005  :  Commodity ID: 13
            [# of Make Parts]           = COUNT(    IIF( ( bom.[Make/Buy] = 'M' AND [dbo].[fn_getFactoryStdCost]([ComponentPartID]) > 0 AND bom.[Qty] > 0 ),                bom.[Component Part #], null)   ),
            [# of Buy Parts]            = COUNT(    IIF( ( bom.[Make/Buy] = 'B' AND [dbo].[fn_getFactoryStdCost]([ComponentPartID]) > 0 AND bom.[Qty] > 0),             bom.[Component Part #], null)   ),
            [# of Ref Parts]            = COUNT(    IIF( ( bom.[Make/Buy] = 'B' AND [dbo].[fn_getFactoryStdCost]([ComponentPartID]) = 0 ),                              bom.[Component Part #], null)   )
          FROM bomBreakdown bom

          WHERE
            bom.[ComponentPartID] IS NOT NULL AND 
            bom.[SourcePartID] = ap.[SourcePartID]
          GROUP BY bom.[SourcePartID]
    ) oa2
    ORDER BY p.[PART_X]

allpartslist有130万条记录
e\u部分有20500条记录
轰炸镇有130万个记录
以前，在outer apply中，除了前两个查询外，所有查询都有不同的结果，但我删除了它们。由于查询运行得很好，所以数字正好相反。
我不认为这是一个数据/表布局问题，我认为可能有交叉连接或我缺少的东西。