Don's questions

我使用以下脚本制作生产数据库的 .bak 文件并将其保存到测试服务器，如果需要，可以在该服务器上恢复。该脚本未说明要压缩文件。运行 SQL Server 2019 Enterprise。

BACKUP DATABASE [PINK] 
    TO DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH NOFORMAT, INIT,  NAME = N'PINK', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO

DECLARE @backupSetId AS int

SELECT @backupSetId = position 
FROM msdb..backupset 
WHERE database_name = N'PINK' 
  AND backup_set_id = (SELECT MAX(backup_set_id) 
                       FROM msdb..backupset 
                       WHERE database_name = N'PINK')

IF @backupSetId IS NULL
BEGIN
    RAISERROR(N'Verify failed. Backup information for database ''PINK'' not found.', 16, 1) 
END

RESTORE VERIFYONLY 
    FROM DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

.bak测试服务器上的内存大小根据您在 Explorer 中查看的位置而定，为 162GB 或 154GB ：

.bak为了节省空间，我尝试通过添加COMPRESSION脚本来压缩：

BACKUP DATABASE [PINK] 
    TO DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH NOFORMAT, INIT, NAME = N'PINK', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM
GO

DECLARE @backupSetId AS int

SELECT @backupSetId = position 
FROM msdb..backupset 
WHERE database_name = N'PINK' 
  AND backup_set_id = (SELECT MAX(backup_set_id) 
                       FROM msdb..backupset 
                       WHERE database_name = N'PINK')

IF @backupSetId IS NULL
BEGIN
    RAISERROR(N'Verify failed. Backup information for database ''PINK'' not found.', 16, 1) 
END

RESTORE VERIFYONLY 
    FROM DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

执行包含的命令后COMMPRESSION，它在测试服务器上的大小相同。我检查了.ndf和.mdf文件，它们总共是 770GB 或 732GB，具体取决于您查看的位置。此外，它还包含大约 23GB 的索引信息。

.bak尽管我没有声明要这么做，但这是否已经被压缩了？

运行 SQL Server 2019 Enterprise。我有一个 PRIMARY 文件组，但也在我创建的 Data1 文件组中添加了四个 ndf 文件。正如预期的那样，随着更多数据的添加，我不得不增加 Data1 文件组上的可用空间，但 mdf PRIMARY 文件组的大小保持相对稳定。在过去的几个月里，我不得不增加 PRIMARY 文件组中的空间。它从 5GB 开始，几周前我扩展到 10GB。再次用完空间，刚刚增加到 15GB。Data1 文件组是默认文件组。为什么 PRIMARY 文件组现在突然增长，而它不是默认文件组？每个 Data1 文件都设置为 160GB，因此 PRIMARY 仍然小得多。只是想知道是否有办法确定为什么它在过去停滞不前时会意外增长。

我有一位开发人员需要能够在 SQL Server 代理中创建作业。它们是安全组 SQLDA 的一部分，我在 msdb 中授予了该组 SQLAgentUserRole。他们仍然只能查看访问权限。我是否还需要在 msdb 中授予他们 db_datawriter 以便他们能够创建工作？

运行 SQLServer 2019。权限通过 Active Directory 组进行管理。

昨天下午晚些时候，我将 4 个 tempdb 数据文件添加到现有的 4 个中，总共 8 个（SQL 服务器上的 16 个处理器）。我还预先将它们种植到接近 100% 的可用空间。

今天，我们的一位开发人员联系了我，她说昨天她正在运行一个查询，该查询将在 3-5 秒内将一组初始结果返回到 ssms 显示，整个查询将在 2-3 分钟内完成。今天查询需要 5 分钟才能完成，大约 2.5 分钟内没有结果显示在 ssms 中。

当时没有其他查询在运行。CPU 使用率很低。我反弹了服务器，没有任何改善。所以现在我想知道我的 tempdb 更改是否导致性能下降。我看不出如何，但偏执狂开始了。我运行了查询并在它执行时监视了 tempdb，它似乎根本没有使用 tempdb，我觉得这很奇怪，因为它有一个我认为使用的 GROUP BY 语句临时数据库。该表包含大约 2200 万行，查询返回大约 780 万行。

    select
      DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0) as Quarter
      ,[PartnerCD]
      ,[PartnerNM]
      ,[PartnerGRP]
      ,[BizMemberID]
      ,[MemberID]
      ,[BizType]
      ,[RateCD]
      ,[Rate]
      ,sum([MemberMonth]) as MemberQuarter
      ,[CurrentAge]
      ,[AgeAtTime] = CASE WHEN dbo.fn_CalculateAge(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)), 'YEAR') < 0 THEN NULL ELSE dbo.fn_CalculateAge(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)), 'YEAR') END
      ,AgeCategory = dbo.fn_CalculateAgeCatYrsOrdered(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)))
      ,[BirthDT]
      ,[ZipCD]
FROM [Partner].[dbo].[Membership]  
group by DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)
      ,[PartnerCD]
      ,[PartnerNM]
      ,[PartnerGRP]
      ,[BizMemberID]
      ,[MemberID]
      ,[BizType]
      ,[RateCD]
      ,[Rate]
      ,[CurrentAge]
      ,[BirthDT]
      ,[ZipCD]

可能是我的更改导致性能下降还是巧合？

我正在运行 SQL Server 2019 企业版。每个月，我们都会运行一个存储过程来加载数百万条服务日期可以追溯到五年前的记录。我为 4 个数据文件分配了 400GB 的空间，并为日志文件分配了 100GB 的空间。作业经常失败，因为日志文件因活动事务而被填满。数据库处于简单恢复模式。所以，我相信它应该在每笔交易结束时清除。开发人员更改了工作，使其一次循环并加载一年的记录。

DROP TABLE IF EXISTS #UnpvtDx;

SELECT ClaimHeader_ID
    ,ClaimDetail_ID
    ,ClaimServiceLine
    ,Unpvt.CodeLine
    ,Unpvt.DxCode 
INTO #UnpvtDx
FROM PRINCE.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT 
(
    DxCode FOR CodeLine
    IN
    (
        Diagnosis1CD,Diagnosis2CD,Diagnosis3CD,Diagnosis4CD,Diagnosis5CD,
        Diagnosis6CD,Diagnosis7CD,Diagnosis8CD,Diagnosis9CD,
        Diagnosis10CD,Diagnosis11CD,Diagnosis12CD,Diagnosis13CD
    )
) as Unpvt ---53 secs
WHERE YEAR(ServiceFromDT) = @year;

DROP TABLE IF EXISTS #UnpvtPointer;

SELECT ClaimHeader_ID
    ,ClaimDetail_ID
    ,ClaimServiceLine
    ,Unpvt.CodeLine
    ,Unpvt.Pointer 
INTO #UnpvtPointer
FROM PRINCE.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT 
(
    Pointer FOR CodeLine 
    IN (DiagPointer1,DiagPointer2,DiagPointer3,DiagPointer4)
) as Unpvt ---40 secs
WHERE YEAR(ServiceFromDT) = @year;

INSERT INTO PROD.Claim.ClaimDiag
(
    ClaimHeader_ID,ClaimDetail_ID,SourceID,EDWLoadDTS,PartnerCD,
    PartnerNM,ClaimID,ClaimServiceLine,ClaimStatus,CCOMemberID,
    MemberID,PlaceOfServiceCD,ServiceFromDT,ServiceToDT,ClaimForm,
    TypeOfBillCD,DiagnosisCD,DiagnosisDESC,DiagPointer
)
SELECT DISTINCT
    det.ClaimHeader_ID,det.ClaimDetail_ID,det.SourceID,det.EDWLoadDTS,
    det.PartnerCD,det.PartnerNM,det.ClaimID,det.ClaimServiceLine,
    det.ClaimStatus,det.CCOMemberID,det.MemberID,det.PlaceOfServiceCD,
    det.ServiceFromDT,det.ServiceToDT,det.ClaimForm,det.TypeOfBillCD,
    DiagnosisCD = dx.DxCode,
    DiagnosisDESC = diag.DiagnosisDESC,
    DiagPointer = point.Pointer
FROM PROD.Claim.ClaimDetail det WITH (NOLOCK)
INNER JOIN PROD.Claim.ClaimHeader ch WITH (NOLOCK)
    ON ch.ClaimHeader_ID = det.ClaimHeader_ID
INNER JOIN #UnpvtDx dx
    ON dx.ClaimDetail_ID = det.ClaimDetail_ID
        AND dx.ClaimHeader_ID = det.ClaimHeader_ID
        AND dx.ClaimServiceLine = det.ClaimServiceLine
LEFT JOIN #UnpvtPointer point
    ON point.ClaimDetail_ID = det.ClaimDetail_ID
        AND point.ClaimHeader_ID = det.ClaimHeader_ID
        AND point.ClaimServiceLine = det.ClaimServiceLine
LEFT OUTER JOIN Reference.Reference.Diagnosis diag WITH (NOLOCK)
    ON dx.DxCode = diag.DiagnosisCD
        AND diag.ICDVersion = 'ICD10CM'
        AND diag.ActiveFLG = 1
WHERE YEAR(det.ServiceFromDT) = @year;

使用以下命令从 SQL 代理作业执行存储过程：

DECLARE @year INT
DECLARE cur CURSOR FOR 

SELECT yr = YEAR(hdr.MinServiceFromDT)
FROM PROD.Claim.ClaimHeader hdr WITH (NOLOCK)   
GROUP BY YEAR(hdr.MinServiceFromDT)
ORDER BY YEAR(hdr.MinServiceFromDT)

OPEN cur
FETCH NEXT FROM cur INTO @year
WHILE @@FETCH_STATUS = 0  
BEGIN  
    EXEC Claim.sp_UpdateClaimDiag @year

    FETCH NEXT FROM cur INTO @year      
END 

CLOSE cur
DEALLOCATE cur

循环的结束是否被视为一个事务，因此日志文件应该在处理完每一年的记录后清空，或者日志文件是否继续填充，直到作业遍历每个循环并加载所有年份的记录？

我还将日志文件增加到 150GB，但这会最大化可用空间（不会低于 10% 的缓冲区）。

发布整个存储过程代码。

USE [Prod]
GO
    
SET ANSI_NULLS ON
GO
    
SET QUOTED_IDENTIFIER ON
GO
    
CREATE PROCEDURE [Claim].[sp_UpdateClaimDiag] @year INT
AS
    
BEGIN
    SET ANSI_DEFAULTS, ARITHABORT, NOCOUNT ON
    SET IMPLICIT_TRANSACTIONS OFF
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    
    -- variable declaration
    DECLARE
        @transactional BIT
        , @trancount INT
        , @err INT
        , @procname SYSNAME
        , @error INT
        , @message VARCHAR(4000)
        , @xstate INT
        , @RecordCount int;
    
    SELECT @procname = OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()) + '.' + OBJECT_NAME(@@PROCID, DB_ID())
        -- 0 =  no: will not execute batches inside a transaction; a partial success of procedure is possible
        -- 1 = yes: batches in procedure will be bound together by a transaction, partial success is impossible
        , @transactional = 0 
    
        -- optionally begin transaction and begin try block
        IF @transactional = 1 SET @trancount = @@TRANCOUNT

        BEGIN TRY
            IF @trancount = 0 and @transactional = 1
                BEGIN TRANSACTION

            ELSE IF @transactional = 1
                SAVE TRANSACTION p1
    
            ----------------------------------------------------------------------------------------
            ---Unpivot columns to Rows into Temp tables
            ----------------------------------------------------------------------------------------
                
            ---ICD9CM  & ICD10CM
            DROP TABLE IF EXISTS #UnpvtDx;

            SELECT
                ClaimHeader_ID
                ,ClaimDetail_ID
                ,ClaimServiceLine
                ,Unpvt.CodeLine
                ,Unpvt.DxCode 
            INTO #UnpvtDx
            FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
            UNPIVOT 
            (
                DxCode FOR CodeLine IN
                (
                    Diagnosis1CD,Diagnosis2CD,Diagnosis3CD,
                    Diagnosis4CD,Diagnosis5CD,Diagnosis6CD,
                    Diagnosis7CD,Diagnosis8CD,Diagnosis9CD,
                    Diagnosis10CD,Diagnosis11CD,Diagnosis12CD,
                    Diagnosis13CD
                )
            ) as Unpvt ---53 secs
            WHERE YEAR(ServiceFromDT) = @year;

            --Select top 100 * from #UnpvtDx where DxCode is null
    
            DROP TABLE IF EXISTS #UnpvtPointer;

            SELECT
                ClaimHeader_ID
                ,ClaimDetail_ID
                ,ClaimServiceLine
                ,Unpvt.CodeLine
                ,Unpvt.Pointer 
            INTO #UnpvtPointer
            FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
            UNPIVOT 
            (
                Pointer FOR CodeLine IN
                (
                    DiagPointer1, DiagPointer2,
                    DiagPointer3,DiagPointer4
                )
            ) as Unpvt ---40 secs
            WHERE YEAR(ServiceFromDT) = @year;
    
            --Select top 100 * from #UnpvtPointer
                
            ----------------------------------------------------------------------------------------
            --- INSERT INTO yearly records from the temp table
            ----------------------------------------------------------------------------------------
        
            INSERT INTO Prod.Claim.ClaimDiag (
                ClaimHeader_ID,
                ClaimDetail_ID,
                SourceID,
                EDWLoadDTS,
                PartnerCD,
                PartnerNM,
                ClaimID,
                ClaimServiceLine,
                ClaimStatus,
                CCOMemberID,
                MemberID,
                PlaceOfServiceCD,
                ServceFromDT,
                ServiceToDT,
                ClaimForm,
                TypeOfBillCD,
                DiagnosisCD,
                DiagnosisDESC,
                DiagPointer
            )
    
            SELECT DISTINCT
                det.ClaimHeader_ID,
                det.ClaimDetail_ID,
                det.SourceID,
                det.EDWLoadDTS,
                det.PartnerCD,
                det.PartnerNM,
                det.ClaimID,
                det.ClaimServiceLine,
                det.ClaimStatus,
                det.CCOMemberID,
                det.MemberID,
                det.PlaceOfServiceCD,
                det.ServiceFromDT,
                det.ServiceToDT,
                det.ClaimForm,
                det.TypeOfBillCD,
                DiagnosisCD = dx.DxCode,
                DiagnosisDESC = diag.DiagnosisDESC,
                DiagPointer = point.Pointer
            FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
            INNER JOIN Prod.Claim.ClaimHeader ch WITH (NOLOCK)
                ON ch.ClaimHeader_ID = det.ClaimHeader_ID
            INNER JOIN #UnpvtDx dx
                ON dx.ClaimDetail_ID = det.ClaimDetail_ID
                    AND dx.ClaimHeader_ID = det.ClaimHeader_ID
                    AND dx.ClaimServiceLine = det.ClaimServiceLine
            LEFT JOIN #UnpvtPointer point
                ON point.ClaimDetail_ID = det.ClaimDetail_ID
                    AND point.ClaimHeader_ID = det.ClaimHeader_ID
                    AND point.ClaimServiceLine = det.ClaimServiceLine
            LEFT OUTER JOIN Reference.Reference.Diagnosis diag WITH (NOLOCK)
                ON dx.DxCode = diag.DiagnosisCD
                    AND diag.ICDVersion = 'ICD10CM'
                    AND diag.ActiveFLG = 1
            WHERE YEAR(det.ServiceFromDT) = @year
                --AND Year(det.ServiceFromDT) = 2021--for testing
                --and det.ClaimID ='21006E06455'--for testing
    
            ----------------------------------------------------------------------------------------
            --insert into updatelog table
            SET @RecordCount = @@ROWCOUNT;
    
            DECLARE @procName1 SYSNAME
            SET @procName1 = @procname + ' ' + CAST(@year AS varchar(4))
            INSERT INTO Prod.dbo.UpdateLog(EventTimestamp,EventDescription,ProcName,TableName)
            SELECT GETDATE(),
                'Inserted ' + CAST(@RecordCount AS varchar(100)) + ' records',
                @procName1,
                'Claim.ClaimDiag'
    ----------------------------------------------------------------------------------------
            DROP TABLE IF EXISTS #UnpvtDx
            DROP TABLE IF EXISTS #UnpvtPointer
    ----------------------------------------------------------------------------------------
            SPEXIT:

            IF @transactional = 1 and @trancount = 0 COMMIT
        END TRY

        ----------------------------------------------------------------------------------------
        -- error handling with catch
        BEGIN CATCH
            SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE()
            IF @transactional = 1 and @xstate = -1 ROLLBACK
            IF @transactional = 1 and @xstate = 1 and @trancount = 0 ROLLBACK
            IF @transactional = 1 and @xstate = 1 and @trancount > 0 ROLLBACK TRANSACTION p1
        
            DROP TABLE IF EXISTS #claims
            SET @procName1 = @procname + ' ' + CAST(@year AS varchar(4)) ---+ ', ' + CAST(@month AS varchar(4)) 
    
            RAISERROR ('%s, Error %d, %s', 16, 1, @procname1, @error, @message) 
            RETURN @error
        END CATCH
    
        RETURN 0  
    END
    GO

我有一个存储过程，它将三个大表连接在一起（每个大约 2000 万条记录）并将记录加载到一个临时表中。然后将临时表中的数据合并到一个包含大约 6000 万条记录的现有表中。

服务器离线并显示以下错误消息：

由于内存压力，AppDomain 2 (SSISDB.dbo.[runtime].1] 被标记为卸载。

让服务器重新联机后，我重新启动 SQL 服务以清除任何可能一直存在的进程。再次开始工作，并且没有问题地完成。

我正在运行具有 128GB RAM 的 SQL Server 2019。64 位虚拟服务器上的最大服务器内存为 117964MB。有人在任务管理器中告诉我内存使用率为 94%，这可能是问题所在。但是 SQL 不会占用所有可用内存并保留它吗？所以看起来它正在按预期运行。

sp_WhoIsActive揭示了一些状态为“暂停”和“等待命令”的查询，但我认为这些不会产生太大影响。128GB 的​​内存似乎足够了，但我想这与它被要求做的工作有关。知道如何排除故障或防止再次发生吗？

服务器的数据驱动器约为 1.6TB。连接中的两个较大的数据库是 10GB 的 1900 万行和 13GB 的 2000 万行。这些进入一个临时表，然后MERGE进入一个有 5300 万行的 26GB 表。

请求的内存授权为 45GB，实际为 30GB。这项工作是在正常工作时间之外进行的，所以应该没有竞争性查询，但我不能 100% 确认有人没有工作到很晚。

我确实注意到它也必须这样做CONVERT_IMPLICIT。这对所需的内存有重大影响吗？

查询计划链接： https ://www.brentozar.com/pastetheplan/?id=SyXaty7xK

我有一堆没有主键的表。有些确实已经有一个非聚集索引，该索引由主键（ClaimID、LineNBR）组成。使用的列当前设置为接受 NULL，因此我需要更改它。为此创建主键是否有价值，是否会使在相同列上的非聚集索引变得多余？最好只留下非聚集索引，删除非聚集索引并使其成为主键，还是添加主键并保留非聚集索引？

我在两个不同的服务器上有一个生产数据库和一个开发数据库。这两个数据库都是从登台服务器上的同一个数据仓库提供的。有一系列自动化的 SQL 作业可以运行和填充数据库中的表。作业从 Dev 复制到 Prod，因此它们也应该相同。然而，Prod 的增长速度远远快于 Dev。

数据库中大约有 10 亿行数据。Prod 中的总数据文件大约大 123GB。总索引文件大约大 31GB。我对此很陌生，但我希望这两个数据库的大小非常相似。如果有的话，我希望 Dev 有一些额外的“垃圾”并且可能是更大的数据库。

任何想法如何找到这种尺寸差异的来源？如果需要，我可以增加 Prod 上的磁盘空间，但向我表明可能存在需要解决的问题。如果可能的话，我想收回 153GB。

诚然，在这方面我是个菜鸟，但我检查了每个环境中的 Index 文件夹，它们似乎都有一个 Index。我检查了属性，它们看起来也一样。更多索引是否也会导致数据文件大小增加？

我正在 Prod 和 Dev 中运行来自 Ola Hallengren 的 IndexOptimize 脚本，并希望它能够充分处理任何重要的碎片。我实际上并没有将任何数据从 Dev 迁移到 Prod。我们有一个托管数据的临时服务器。一组 SSIS 和存储过程将数据从登台移动到 Dev 上的数据库。更多 SSIS 作业和存储过程填充了 Dev 中的表。SSIS 和存储过程从 Dev 提升到 Prod，并在 Prod 上独立运行。Prod 作业访问与 Dev 相同的登台服务器。