0

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 [Table is TB sized]). 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

Rickd43
  • 1
  • 1
  • If you're doing to whole lot in one go, then yes, it'll try to do the whole parse in one go, which will (at least) cause the transaction log in your database to grow, and likely a lot of processing will happy in `tempdb`. If you are working with a huge dataset, then often performing the operations in batches if the best solution. – Thom A Jan 13 '21 at 11:41
  • It seems the execution plan includes a sort operator for all rows in the main table. Ensure a unique index exists on the joined columns. – Dan Guzman Jan 13 '21 at 12:47
  • Thanks Larnu, Not fussed about the TLog as disk space isn't the issue. I am doing this as a merge due to the system design, only want to split it out if I really have to and no other choice. My question was more about whether the Target is loaded into TempDb (which is what I think is happening), rather than the subset that matches the source and if so, under what conditions can I make sure it doesn't (indexes, query structure etc). – Rickd43 Jan 13 '21 at 12:49
  • Thanks Dan, that may prove to be the case (need to check a bit more) and very useful information. Was just reading about optimising merge (although I can only find the 2008 R2 version weirdly), so tallies with what I have found as well. :) – Rickd43 Jan 13 '21 at 12:52
  • Are there Spools (Lazy or Eager) in the execution plan? These indicate that data loads into tempdb - maybe multiple times. SQL Server might create a copy of destination table, new data and various additional intermittent work objects to be able to process the merge statement. This issue might come from the query analyzer instead of the merge statement itself. – Knut Boehnert Jan 13 '21 at 13:37

1 Answers1

0

The answer to this was down to indexes, there wasn't a covering one on the target table due to the nature of the data. I am still trying to figure a way around this currently, but looks like surrogate keys and a proper indexing strategy.

Thanks for the comments.

Rickd43
  • 1
  • 1