While pivoting 13 million records tempDB gets full in SQL Server and pivoting takes more than 28 hours

Question

I have one table which consumes 13 million records(data will increase in the future) which is approx 2.5gb Also, this table is not a Temporary table. When We try to pivot a table SQL give me the result as below

Msg 1105, Level 17, State 2, Line 56
Could not allocate space for object 'dbo.WORKFILE GROUP large record overflow storage: 140761897762816' 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

Also, it takes too long to pivot. (more than 28 hours) there is no other task is running on that SQL server at that time.

we have a total 16gb physical memory in the machine out of 16gb, 12gb allocated to SQL server. this machine has 4 core. there are total 4 filegroups for file type ROWS Data and one for LOG that all 5 files are located in the separate drive, the total size of that drive is 50gb. tempdb initial size is 14gb. Autogrowth for all datafile is 100MB and Maxsize are Unlimited.

SQL query as below:

SET nocount ON 

SELECT * 
INTO isheetnewdata1_4_27 
FROM 
    (SELECT 
         historyid, 
         requestentityid, 
         fieldname, 
         fieldvalue, 
         siteid, 
         isheetid 
     FROM   
         synk_isheet_1_int  
     WHERE  
         historyid = 6 
         AND group1id = 27 
         AND group2id = 4) AS A 
PIVOT 
    (MAX(fieldvalue) 
     FOR fieldname IN ([ID], [LastName], [FirstName], [Age], [externalId])
    ) AS pvt 
ORDER BY 
    historyid, requestentityid

Please note that fieldname can be more which we have to pivot.

I am using SQL Server 2016 in Microsoft Azure. My main concern is pivoting is taking more than 28 hours and due to that tempdb size is reached the maximum size of that drive.

I have no idea what to do in this stage. should I have to increase the size of the drive where the tempdb database file is located? and increase physical memory of that machine and provide more memory to SQL Server?

Thanks all

Answer 1

First of all: Please do not post pictures of data. Nobody wants to type this in. Better try to provide a stand-alone sample to reproduce your issue.

Secondly: Even if this table is created temporarily: With such expensive queries it is worth to create indexes.

Instead of using PIVOT you might use conditional aggregation. The idea is, to use a grouping set, which reduces your target set to one-row-per-entity (what ever this is) and use a CASE WHEN together with MAX() to set the pivot columns.

SELECT t.historyid
      ,t.requestentityid
      ,MAX(CASE WHEN fieldname='ID' THEN fieldvalue END) AS ID
      ,MAX(CASE WHEN fieldname='LastName' THEN fieldvalue END) AS LastName
      ,MAX(CASE WHEN fieldname='Age' THEN fieldvalue END) AS Age
      ,MAX(CASE WHEN fieldname='externalId' THEN fieldvalue END) AS externalId
FROM synk_isheet_1_int t
WHERE t.historyid = 6 
  AND t.group1id = 27 
  AND t.group2id = 4
GROUP BY t.historyid,t.requestentityid;

Not knowing your table and data this is flying blind-folded. but my magic crystal ball told me, that this might help...

A reduced example

This is 1) to show the principles and 2) to show you how you might set up a stand alone sample.

DECLARE @tbl TABLE(id INT,fieldname varchar(max),fieldvalue varchar(max));
insert into @tbl VALUES(1,'lastname','hugo')
                      ,(2,'age','23')
                      ,(1,'ID','12')
                      ,(2,'LastName','test');

SELECT t.id
      ,MAX(CASE WHEN t.fieldname='ID' THEN t.fieldvalue END) AS ID
      ,MAX(CASE WHEN t.fieldname='LastName' THEN t.fieldvalue END) AS LastName
      ,MAX(CASE WHEN t.fieldname='Age' THEN t.fieldvalue END) AS Age
      ,MAX(CASE WHEN t.fieldname='externalId' THEN t.fieldvalue END) AS externalId
FROM @tbl t
GROUP BY t.id