select @@version

sp_helpdb 'tempdb'

UPDATE cost
  SET DimDataQualitySK = 18
FROM dbo.FactCost cost
  INNER JOIN dbo.Dim1 afe ON ( afe.Dim1SK = cost.Dim1SK )    
  INNER JOIN dbo.Dim2 j ON ( j.Dim2SK = cost.Dim2SK )
  INNER JOIN (
    SELECT BK, Number
    FROM (
        SELECT DISTINCT afe.Dim2SK, j.BK, afe.Number
        FROM dbo.Dim1 afe
           INNER JOIN dbo.Dim2 j ON ( afe.Dim2SK = j.Dim2SK )
       WHERE afe.Number <> '<undefined>'
    ) t
    GROUP BY BK, Number 
    HAVING COUNT(*) > 1  
   )  T on ( T.BK = j.BK AND t.Number = AFE.Number ) 

exec sp_spaceused

tempdb	35927.06 MB	34006.98 MB

select top 1000000 o1.* into #tmp
from sys.objects o1, sys.objects o2,sys.objects o3 

exec sp_spaceused

tempdb	35927.06 MB	33914.91 MB

|--Compute Scalar(DEFINE:([Expr1012]=CONVERT_IMPLICIT(int,[globalagg1017],0)))
       |--Stream Aggregate(DEFINE:([globalagg1017]=SUM([partialagg1016])))
            |--Parallelism(Gather Streams)
                 |--Stream Aggregate(DEFINE:([partialagg1016]=Count(*)))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([cost].[Dim1SK], [j].[BK], [Expr1023]) OPTIMIZED WITH UNORDERED PREFETCH)
                           |--Hash Match(Inner Join, HASH:([afe].[Dim1SK])=([cost].[Dim1SK]))
                           |    |--Bitmap(HASH:([afe].[Dim1SK]), DEFINE:([Bitmap1022]))
                           |    |    |--Parallelism(Distribute Streams, Broadcast Partitioning)
                           |    |         |--Hash Match(Inner Join, HASH:([afe].[AFENumber])=([afe].[AFENumber]), RESIDUAL:([WPOTD].[dbo].[Dim1].[AFENumber] as [afe].[AFENumber]=[WPOTD].[dbo].[Dim1].[AFENumber] as [afe].[AFENumber]))
                           |    |              |--Parallelism(Gather Streams)
                           |    |              |    |--Filter(WHERE:([Expr1011]>(1)))
                           |    |              |         |--Compute Scalar(DEFINE:([Expr1011]=CONVERT_IMPLICIT(int,[Expr1021],0)))
                           |    |              |              |--Stream Aggregate(GROUP BY:([afe].[AFENumber], [j].[BK]) DEFINE:([Expr1021]=Count(*)))
                           |    |              |                   |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([afe].[AFENumber], [j].[BK]), ORDER BY:([afe].[AFENumber] ASC, [j].[BK] ASC))
                           |    |              |                        |--Sort(DISTINCT ORDER BY:([afe].[AFENumber] ASC, [j].[BK] ASC, [afe].[Dim1SK] ASC))
                           |    |              |                             |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([afe].[AFENumber], [j].[BK], [afe].[Dim1SK]))
                           |    |              |                                  |--Hash Match(Inner Join, HASH:([afe].[Dim1SK])=([j].[Dim1SK]))
                           |    |              |                                       |--Bitmap(HASH:([afe].[Dim1SK]), DEFINE:([Bitmap1020]))
                           |    |              |                                       |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([afe].[Dim1SK]))
                           |    |              |                                       |         |--Clustered Index Scan(OBJECT:([WPOTD].[dbo].[Dim1].[PK_Dim1] AS [afe]), WHERE:([WPOTD].[dbo].[Dim1].[AFENumber] as [afe].[AFENumber]<>N'<undefined>'))
                           |    |              |                                       |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([j].[Dim1SK]))
                           |    |              |                                            |--Clustered Index Scan(OBJECT:([WPOTD].[dbo].[Dim1].[PK_Dim1] AS [j]), WHERE:(PROBE([Bitmap1020],[WPOTD].[dbo].[Dim1].[Dim1SK] as [j].[Dim1SK],N'[IN ROW]')))
                           |    |              |--Clustered Index Scan(OBJECT:([WPOTD].[dbo].[Dim1].[PK_Dim1] AS [afe]))
                           |    |--Table Scan(OBJECT:([WPOTD].[dbo].[FactCost] AS [cost]), WHERE:(PROBE([Bitmap1022],[WPOTD].[dbo].[FactCost].[Dim1SK] as [cost].[Dim1SK],N'[IN ROW]')))
                           |--Clustered Index Seek(OBJECT:([WPOTD].[dbo].[Dim1].[PK_Dim1] AS [j]), SEEK:([j].[Dim1SK]=[WPOTD].[dbo].[FactCost].[Dim1SK] as [cost].[Dim1SK]),  WHERE:([WPOTD].[dbo].[Dim1].[BK] as [j].[BK]=[WPOTD].[dbo].[Dim1].[BK] as [j].[BK]) ORDERED FORWARD)

exec xp_fixeddrives 
go
use tempdb
go
SELECT db_name() [DB],file_id,name,type_desc, physical_name,size/128. [Size (MB)],
	CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128. [Used Space (MB)],
             size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128. AS [Available Space (MB)]
FROM sys.database_files
go

set showplan_text  on
go

select * 
from sys.objects 
order by create_date desc, type_desc desc
go

set showplan_text  off
go

select * 
from master.dbo.spt_values v1,master.dbo.spt_values v2, master.dbo.spt_values v3
order by v1.high, v2.low, v3.name