Hi,

In one of our database while executing a procedure serving an important business logic, which has 11 joins, then a filtering on a specific column, sorting using order by on a specific column. In the join condition, as I see them separately, there are millions of rows fetched. Now I see that TempDB is ballooning to 75 GB size, which is the available disk space and the query fails.

What could be the possible reason:

- Is the TempDB, behaving incorrectly, can I really expect that kind of size  (75 GB).

- If the TempDB behavior is correct, is there something I can do to mitigate the situation, will the faster execution of the query, having more statistics, relevant index, more seeking than scanning of index / table, will that help is solving the situation.

I know a possible situation would be relocate the db to a much a disk space, but I want to figure out the tuning options first, since I do not know, what is the maximum size TempDb will bloat up to

Any relevant suggestion would be great.

thanks,

Mrinal

Hello,

Assuming you've already narrowed down the TempDB issue to be this query (as in you're not using any optimistic concurrency, AGs, or anything that is versioning in general, Temp Tables, etc) then I would look into:

1. How big is the retrieved dataset?

2. Are you doing hash joins and having hash bailouts?

3. When grouping or sorting, how large is that dataset?

Remember that TempDB has different uses for normal query processing such as sorting, hashing, dataset overflow, etc, in addition to versioning, temp table space, etc.

-Sean

Hello,

Based on your description, the following active may cause tempdb to grow:
1.Sorting that requires more memory than has been allocated to SQL Server.
2.Large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables.

As for tempdb size, it is recommend to set the file size to a value large enough to accommodate the typical workload in the environment. And allow for tempdb files to automatically grow. This allows for the file to grow until the disk is full. After restart Microsoft SQL Server, the disk space which allocated to tempdb will releasing.
Reference:http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

If you want to shrink tempdb to a size that is smaller than its configured size, you can refer to the methods in the following KB article.
http://support.microsoft.com/kb/307487

Regards,
Fanny Liu

If you have any feedback on our support, please click  here.

I would try splitting out your operations into separate steps to help the optimizer. Definitely use a temp table to write your results into and only when you have the correct data there perform the necessary grouping and then sorting in different statements. 

Also possibly break out your joining into multiple statements too. Monitor tempdb as these steps execute and if it doesnt provide a fix you should still be able to see exactly which step causes the bloat.

Go through the link

http://www.mssqltips.com/sqlservertip/2833/sql-server-2012-best-practices-analyzer-tempdb-recommendations/

Sql Query for reference:

SELECT 
     S.SHIPMENTID, 
     LMapped.LANEID AS ServID, 
     LOrig.LANEID AS OrigServID, 
     SCOrig.DOWID AS DepDay, 
     DATEPART(HOUR, S.DEPCUTOFFTIME) * 60 + DATEPART(MI, S.DEPCUTOFFTIME) AS DepTime, 
     SCOrig.DAYSINROUTE AS DIR, 
     (((SCOrig.DOWID + SCOrig.DAYSINROUTE - 1) % 7) + 1) AS ArrDay, 
     DATEPART(HOUR, S.ARRCUTOFFTIME) * 60 + DATEPART(MI, S.ARRCUTOFFTIME) AS ArrTime, 
     S.TOTALWEIGHT AS ComSWeight, 
     ceiling(DATEDIFF(MI, S.DEPCUTOFFTIME, DATEADD(MI, (OT.TIMEOFFSET - DT.TIMEOFFSET) * 60 + SCOrig.DAYSINROUTE * 1440, S.ARRCUTOFFTIME))) AS ComSMinute 
  FROM 
     dbo.PLANSHIPMENT  AS S 
        JOIN dbo.SERVICECOMMITMENT  AS SCOrig 
        ON SCOrig.COMMITMENTID = S.COMMITMENTID AND SCOrig.SCENARIOID = S.SCENARIOID 
        JOIN dbo.LANE  AS LOrig 
        ON LOrig.LANEID = SCOrig.LANEID AND LOrig.SCENARIOID = SCOrig.SCENARIOID 
        JOIN dbo.PARTNERGATEWAY  AS GW 
        ON 
           GW.ORIGTERMINALID = LOrig.ORIGTERMINALID AND 
           GW.DESTTERMINALID = LOrig.DESTTERMINALID AND 
           GW.SCENARIOID = LOrig.SCENARIOID 
        JOIN dbo.LANE  AS LMapped 
        ON 
           LMapped.ORIGTERMINALID = GW.ORIGGWTERMINALID AND 
           LMapped.DESTTERMINALID = GW.DESTGWTERMINALID AND 
           LMapped.SCENARIOID = GW.SCENARIOID 
        JOIN dbo.SERVICECOMMITMENT  AS SCMapped 
        ON 
           SCMapped.LANEID = LMapped.LANEID AND 
           SCMapped.DOWID = SCOrig.DOWID AND 
           SCMapped.SCENARIOID = LMapped.SCENARIOID 
        JOIN dbo.TERMINAL  AS MappedOrig 
        ON MappedOrig.NODEID = GW.ORIGGWTERMINALID AND MappedOrig.SCENARIOID =    
GW.SCENARIOID 
        JOIN dbo.TERMINAL  AS MappedDest 
        ON MappedDest.NODEID = GW.DESTGWTERMINALID AND MappedDest.SCENARIOID =   
GW.SCENARIOID 
        JOIN dbo.NODE  AS Orig 
        ON Orig.NODEID = MappedOrig.NODEID AND Orig.SCENARIOID = MappedOrig.SCENARIOID 
        JOIN dbo.NODE  AS Dest 
        ON Dest.NODEID = MappedDest.NODEID AND Dest.SCENARIOID = MappedDest.SCENARIOID 
        JOIN dbo.TIMEZONE  AS OT 
        ON Orig.TIMEZONEID = OT.TIMEZONEID 
        JOIN dbo.TIMEZONE  AS DT 
        ON Dest.TIMEZONEID = DT.TIMEZONEID
  WHERE S.SCENARIOID = @p_scenarioid // Integer
  ORDER BY S.SHIPMENTID

Also as per my analysis all tables are providing data via Cartesian product, tables are independent of each other, which I assume could be the reason, that's why such a huge data is processed in memory.

is my analysis correct