Fixed Size TempDB, MERGE into very large table failing due to space, is this a limitation of merge?

Question

Hi,

Thanks for reading, am a bit confused by this, think it is down to the size of TempDB, but unsure why it is happening in this case:

I have a MERGE statement:

DECLARE @LastUpdate DATETIME
SELECT @LastUpdate = ISNULL(MAX(LastUpdate),'1900-01-01') FROM dbo.StatusTable

DECLARE @CurTime DATETIME = GETDATE()

BEGIN TRANSACTION

    MERGE dbo.TableName AS targ
    USING
        (
            SELECT
                <fieldlist>
            FROM 
                JoinTablesEtc
            WHERE UpdateDateTime > @LastUpdate
        ) AS src
    ON
        src.JoinFields = targ.JoinFields
    WHEN MATCHED AND ISNULL(src.RemData,0) <> 1 THEN
        UPDATE
        SET
            fieldtoupdate = src.fieldtoupdate
    WHEN NOT MATCHED BY TARGET THEN
        INSERT
            (
                FieldList
            )
        VALUES
            (
                src.FieldList
            )
    WHEN MATCHED AND src.RemData = 1 THEN
        DELETE
    OUTPUT $action INTO @MergeOutput;
COMMIT

Obviously the source is a subset of the main table (usually a smallish % of the table, lets say 80-100Gb). TempDB is allocated 800Gb and is on it's own drive with no auto-growth. I am getting the following error:

Could not allocate space for object 'dbo.SORT temporary run storage: 140962158870528' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Now, does this mean it tries to put the whole of the target table into TempDb? If so, I will obviously need to stop using merge and write separate Insert/update/delete statements. I have been looking for the answer to this in all the technicals for MERGE, but can't find anything about it, so any help is very much appreciated.

Thanks

Answer 1

If you know which trunk of data in the target table to be involved in the MERGE statement, you may create a view to list the columns you need and to limit the rows based on the date and time. And then you can use the view as your target table.

Answer 2

You can track the tempdb usage by your statement with sys.dm_db_session_space_usage and sys.dm_db_task_space_usage views. Also other queries and version store may also consume tempdb a lot while you are running your merge.

Answer 3

Now, does this mean it tries to put the whole of the target table into TempDb? If so, I will obviously need to stop using merge and write separate Insert/update/delete statements.

Don't jump to conclusion. What happens there is that the query plan includes an operator that spills to disk. That could be a sort, hash or a spool operator. For hash and sorting operations at least, SQL Server tries to estimate how much memory they need and reserves this memory for the query. But if that estimate is to small, the operator will spill to disk.

This could happen for a number of reasons, including lack of good indexes to support the query. Or for that matter, to many indexes that needs to be updated.

The MERGE statement itself writes on thing to tempdb: the table variable in the OUTPUT clause.

Switich to INSERT + UPDATE could resolve the issue. Or it could be equally bad. Or worse.

Answer 4

Hi @Rick D ,

Let me add the capacity of tempdb.We recommend that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

1. Set autogrow on for tempdb.
2. Run individual queries or workload trace files and monitor tempdb space use.
3. Execute index maintenance operations such as rebuilding indexes, and monitor tempdb space.
4. Use the space-use values from the previous steps to predict your total workload usage. Adjust this value for projected concurrent activity, and then set the size of tempdb accordingly.
   Monitorin

Monitoring tempdb use
Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment. It can also prevent applications that are running from completing operations. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that's used in the tempdb files:

    -- Determining the amount of free space in tempdb  
    SELECT SUM(unallocated_extent_page_count) AS [free pages],  
      (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]  
    FROM sys.dm_db_file_space_usage;  
      
    -- Determining the amount of space used by the version store  
    SELECT SUM(version_store_reserved_page_count) AS [version store pages used],  
      (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]  
    FROM sys.dm_db_file_space_usage;  
      
    -- Determining the amount of space used by internal objects  
    SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],  
      (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]  
    FROM sys.dm_db_file_space_usage;  
      
    -- Determining the amount of space used by user objects  
    SELECT SUM(user_object_reserved_page_count) AS [user object pages used],  
      (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]  
    FROM sys.dm_db_file_space_usage;  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.