SQL Server Tempdb goes over to 200GB

Question

SQL Syntax as below

    DELETE V From Table1 V
INNER JOIN Table2 AS A ON A.Col1 = V.Col1 AND A.Col2 = V.Col2
AND A.Col3 = V.Col3 And A.Col4 = V.Col4
AND A.Col5 = V.Col5 AND A.Col6 = V.Col6

Info for Table1:

1. Rows: 32,00,000
2. NonClust_Index1_Col1
3. NonClust_Index2_Col2
4. NonClust_Index3_Col3
5. NonClust_Index4_Col4
6. NonClust_Index5_Col5
7. No Cluster index and primary key

Info for Table 2:

1. Rows: 50,000

2. NonClust_Index1_Col1_Col2_Col3_Col4_Col5_Col6 (Non Clust Composite index on six column)

3. Clust_Index2_Col1_Col2_Col3_Col4_Col5_Col6 (Clust Composite index on six column)

Please help me to identify what is the reason of growing TempDB size (>200GB) for this simple delete syntax (Delete syntax is in Transaction Scope)

Answer 1

Just suggestion on the query performance here:

If in Table2 you have a single non-clustered index for all the columns, then it will be used only for the first column in that index, and for the rest it will not be used.

So I guess you can create the index in the same way as you have created for table1.