时间戳

首页 > 解决方案 > 虽然旋转 1300 万条记录 tempDB 在 SQL Server 中已满，但旋转需要超过 28 小时

sql - 虽然旋转 1300 万条记录 tempDB 在 SQL Server 中已满，但旋转需要超过 28 小时

问题描述

我有一张表，它消耗 1300 万条记录（未来数据会增加），大约 2.5gb 另外，这个表不是临时表。当我们尝试对表进行透视时，SQL 给我如下结果

消息 1105，级别 17，状态 2，第 56 行
无法为数据库“tempdb”中的对象“dbo.WORKFILE GROUP 大型记录溢出存储：140761897762816”分配空间，因为“PRIMARY”文件组已满。通过删除不需要的文件、删除文件组中的对象、向文件组添加其他文件或为文件组中的现有文件设置自动增长来创建磁盘空间

此外，它需要很长时间才能旋转。（超过 28 小时）当时没有其他任务在该 SQL 服务器上运行。

我们在机器中总共有 16gb 物理内存，其中 16gb，12gb 分配给 SQL server。这台机器有4个核心。ROWS Data 文件类型共有 4 个文件组，LOG 文件组有 1 个文件组，所有 5 个文件都位于单独的驱动器中，该驱动器的总大小为 50gb。tempdb 初始大小为 14gb。所有数据文件的自动增长是 100MB，Maxsize 是无限的。

SQL查询如下：

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

synk_isheet_1_int的表结构

请注意，字段名可以更多，我们必须旋转。

我在 Microsoft Azure 中使用 SQL Server 2016。我主要担心的是旋转需要超过 28 小时，并且由于 tempdb 大小已达到该驱动器的最大大小。

我不知道在这个阶段该做什么。我是否必须增加 tempdb 数据库文件所在的驱动器的大小？并增加该机器的物理内存并为 SQL Server 提供更多内存？

查询执行计划

预期的枢轴看起来像

谢谢大家

标签： sqlsql-servertsqlpivot

解决方案

首先：请不要发布数据图片。没有人愿意输入这个。最好尝试提供一个独立的示例来重现您的问题。

其次：即使这张表是临时创建的：对于如此昂贵的查询，创建索引也是值得的。

而不是使用PIVOT你可能会使用条件聚合。这个想法是，使用一个分组集，它将您的目标集减少到每个实体一行（无论这是什么），并使用 a CASE WHENwithMAX()来设置数据透视列。

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;

不知道你的表格和数据，这是蒙着眼睛的。但我的魔法水晶球告诉我，这可能会有所帮助……

一个简化的例子

这是 1) 展示原理和 2) 展示如何设置独立样本。

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

推荐阅读