Kin Shah's questions

尝试了解在调用存储过程时使用可重复读取隔离级别发生的死锁。

这是死锁 xml：

    <TextData>
  <deadlock-list>
 <deadlock victim="process6fac029848">
  <process-list>
   <process id="process6fac029848" taskpriority="0" logused="0" waitresource="KEY: 8:72078600230076416 (d3c590b351cd)" waittime="1171" 
            ownerId="26116633497" transactionname="delete" lasttranstarted="2021-01-07T10:35:38.317" XDES="0x12553cbe3b0" 
            lockMode="U" schedulerid="3" kpid="19776" status="suspended" spid="92" sbid="0" ecid="0" priority="0" trancount="2" 
            lastbatchstarted="2021-01-07T10:35:38.310" lastbatchcompleted="2021-01-07T10:35:38.310" lastattention="1900-01-01T00:00:00.310" 
            clientapp=".Net SqlClient Data Provider" hostname="web01" hostpid="34852" loginname="someuser" 
            isolationlevel="repeatable read (3)" xactid="26116633497" currentdb="8" currentdbname="db1" lockTimeout="4294967295" clientoption1="673316896" 
            clientoption2="128056">
    <executionStack>
     <frame procname="db1.schema.deleteSP" line="124" stmtstart="8308" stmtend="8870" 
            sqlhandle="somehandle">
UPDATE [schema].[TableName] WITH(UPDLOCK)
        SET
            Status = 1 /* STATUS_DELETED */,
            userid = @id,
            UpdateTime = GETUTCDATE()
        FROM @oldatt oa
        WHERE [TableName].[Id] = oa.Id     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 8 Object Id = 1688915952]    </inputbuf>
   </process>
   <process id="process6fab017088" taskpriority="0" logused="284" waitresource="KEY: 8:72064801531101184 (4b819525e255)" waittime="1283" 
   ownerId="26116632474" transactionname="delete" lasttranstarted="2021-01-07T10:35:38.290" XDES="0x44037ec3b0" 
   lockMode="X" schedulerid="25" kpid="25152" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" 
   lastbatchstarted="2021-01-07T10:35:38.287" lastbatchcompleted="2021-01-07T10:35:38.283" lastattention="1900-01-01T00:00:00.283" 
   clientapp=".Net SqlClient Data Provider" hostname="web02" hostpid="57552" loginname="someuser" 
   isolationlevel="repeatable read (3)" xactid="26116632474" currentdb="8" currentdbname="db1" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
    <executionStack>
     <frame procname="db1.schema.deleteSP" line="124" stmtstart="8308" stmtend="8870" sqlhandle="somehandle">
UPDATE [schema].[TableName] WITH(UPDLOCK)
        SET
            Status = 1 /* STATUS_DELETED */,
            userid = @id,
            UpdateTime = GETUTCDATE()
        FROM @oldatt oa
        WHERE [TableName].[Id] = oa.Id     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 8 Object Id = 1688915952]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72078600230076416" dbid="8" objectname="db1.schema.TableName" indexname="IX_TableName_Id_Status_userid_UpdateTime" id="lock287c93a100" mode="U" associatedObjectId="72078600230076416">
    <owner-list>
     <owner id="process6fab017088" mode="U" />
    </owner-list>
    <waiter-list>
     <waiter id="process6fac029848" mode="U" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72064801531101184" dbid="8" objectname="db1.schema.TableName" indexname="IX_TableName_SomeId_Id" id="lock55fc471880" mode="S" associatedObjectId="72064801531101184">
    <owner-list>
     <owner id="process6fac029848" mode="S" />
    </owner-list>
    <waiter-list>
     <waiter id="process6fab017088" mode="X" requestType="convert" />
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
</TextData>

具有相关索引的创建表：

CREATE TABLE [schema].[TableName](
    [Id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [SomeId] [int] NOT NULL,
    [Userid] [int] NOT NULL,
    [UpdateTime] [datetime] NOT NULL,
    [RecordStatus] [tinyint] NOT NULL,
 CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--- 2 indexes that are part of deadlock 
IX_TableName_SomeId_Id = filtered Index - SomeId, Id (include status) where status = 0
IX_TableName_Id_Status_userid_UpdateTime - non clustered index - Id include(status, userid, updatetime)

SP调用是：

• 隔离级别可重复读
• 开始交易
• 从基表中进行选择并将受影响的行插入到临时变量中。
• 执行另一个 SP
• 更新基表。<<--- 这是发生死锁的地方
• 将数据与一些业务逻辑合并
• 如果没有错误，则提交事务

基表具有对审计表进行插入和更新以跟踪更改的触发器。

我只是想弄清楚为什么我会遇到太多死锁。

更新 ：

declare @oldtable(
        Id bigint,
        someId int
        PRIMARY key clustered (id)
    )
    insert into @oldtable (
        id,
        someid
    )
    select id, someid
    from [schema].[TableName]
    where id = @id and status = 0 and someid = @someid 

存储过程流控制：

variables 
    @id 
    @someid 
    
begin 
    set nocount on
    set transaction isolation level repeatable read
    set xact_abort on

    begin tran delete
    begin try

    declare @oldtable(
        Id bigint,
        someId int
        PRIMARY key clustered (id)
    )
    insert into @oldtable (
        id,
        someid
    )
    select id, someid
    from [schema].[TableName]
    where id = @id and status = 0 and someid = @someid 

    some SP runs

    -- update statememt that causes deadlock 
    -- it basically sets status = 1 -- deleted (soft delete)

    -- merge data into tables using CTE and MERGE 
    END TRY

    begin catch 
        -- error handling 
        -- roll back tran 
    end catch

    if @@trancount > 0
    begin
        commit tran delete
    END

END

我正在阅读ALTER PARTITION FUNCTION 最佳实践，上面写着 -

始终在分区范围的两端保留空分区，以保证分区拆分（加载新数据之前）和分区合并（卸载旧数据之后）不会发生任何数据移动。避免拆分或合并填充的分区。这可能非常低效，因为这可能导致多达四倍的日志生成，并且还可能导致严重的锁定。

问题 ：

• 通过确保新边界对应于未来日期是否足以确保不会有数据移动，或者我真的需要空分区吗？
• 我认为如果分区为空，则可以进行某些优化，但是说主要成本在数据移动本身是否正确？
• SQL 2014 中是否有任何新功能可以提供帮助（我们正在从 2008 年过渡到 2014 年……是的，我知道 SQL Server 2019 已经过时了……但我们最终会到达那里）？

我在托管锁优先级上看到了很多（例如https://blogs.technet.microsoft.com/dataplatforminsider/2013/08/16/improved-application-availability-during-online-operations-in-sql-server-2014/ ); 然而，这似乎只适用于表分区切换和在线索引重建，而不适用于ALTER PARTITITON FUNCTION.

更新：@AmitBanerjee - Microsoft SQL Server 产品组的高级项目经理确认 MS 将调查该问题，因为它是一个缺陷。

MAXTRANSFERSIZE是否有人在启用 TDE 并使用> 65536（在我的情况下，我选择了 65537 以便我可以压缩 TDE 数据库）的情况下恢复在 SQL Server 2016 上进行的备份时遇到问题CHECKSUM？

下面是一个复制品：

--- create database 
create database test_restore
go
-- create table
create table test_kin (fname char(10))
go
-- Enable TDE 

use master
GO
CREATE CERTIFICATE test_restore WITH SUBJECT = 'test_restore_cert'
GO
SELECT name, pvt_key_encryption_type_desc, * FROM sys.certificates WHERE name = 'test_restore'
GO
use test_restore
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE test_restore
GO 
alter database test_restore set encryption ON

只备份完整副本.. 做两次..

backup database test_restore 
to disk = 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak' -- change as per your location !!
with init, stats =10  -- overwrite ..using INIT !!
, maxtransfersize = 65537
, compression
,CHECKSUM

现在做一个verifyonly...

restore verifyonly from disk = 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak'

错误信息 ：

消息 3241，级别 16，状态 40，第 11 行设备“D:\temporary-short-term\test_restore_KIN_test_restore_1.bak”上的媒体系列格式不正确。SQL Server 无法处理此媒体系列。消息 3013，级别 16，状态 1，第 11 行验证数据库异常终止。

不同组合的结果（1 = ON，0 = OFF）：

+-------------------------+-------------+----------+--------+
| MAXTRANSFERSIZE (65537) | COMPRESSION | CHECKSUM | RESULT |
+-------------------------+-------------+----------+--------+
|                       1 |           1 |        1 | FAIL   |
|                       1 |           1 |        0 | PASS   |
|                       1 |           0 |        1 | FAIL   |
|                       0 |           0 |        0 | PASS   |
|                       0 |           1 |        1 | PASS   |
|                       0 |           1 |        0 | PASS   |
+-------------------------+-------------+----------+--------+

问题发生在：

Microsoft SQL Server 2016 (RTM-CU1) (KB3164674) - 13.0.2149.0 (X64) 2016 年 7 月 11 日 22:05:22 版权所有 (c) Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows Server 2012 R2 Standard 6.3 (Build 9600) :)

我在 tsql 下运行以从主副本中查找辅助 AlwaysON 服务器：

set transaction isolation level read uncommitted 
set statistics time, io on
SELECT  
ISNULL(agstates.primary_replica, '') AS [Primary_AG_Server],
dbcs.database_name AS [DatabaseName]
 ,ar.replica_server_name as [Secondary_AG_Servers]

   FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local <> 1 -- is_local is not 1 .. so we get secondary server names
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
    group by agstates.primary_replica, dbcs.database_name, ar.replica_server_name
    set statistics time, io off

上面的查询需要12 分 38 秒才能运行并返回 34 行。下面是统计IO和时间输出：

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 10 ms, elapsed time = 10 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(34 row(s) affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysguidrefs'. Scan count 0, logical reads 212, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 1266 ms,  elapsed time = 747852 ms.

这是实际执行计划的链接。

有什么办法可以让它更快？

以下来自计划资源管理器

点击放大

点击放大

谢谢 ！

我有一个具有以下结构的表：

CREATE TABLE [dbo].[AUDIT_SCHEMA_VERSION](
    [SCHEMA_VER_MAJOR] [int] NOT NULL,
    [SCHEMA_VER_MINOR] [int] NOT NULL,
    [SCHEMA_VER_SUB] [int] NOT NULL,
    [SCHEMA_VER_DATE] [datetime] NOT NULL,
    [SCHEMA_VER_REMARK] [varchar](250) NULL
);

一些样本数据（sqlfiddle 似乎有问题..所以放一些样本数据）：

INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,6,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,6,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,7,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,10,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,12,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,12,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20140417 18:10:44.100' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,5,0,CAST('20140417 18:14:14.157' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,6,0,CAST('20140417 18:14:23.327' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,7,0,CAST('20140417 18:14:32.270' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,8,0,CAST('20141209 09:38:40.700' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,9,0,CAST('20141209 09:43:04.237' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,10,0,CAST('20141209 09:45:19.893' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,13,0,CAST('20150323 14:54:30.847' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,10,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,14,CAST('20140417 18:11:07.977' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,15,CAST('20140417 18:11:13.130' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,2,0,CAST('20140417 18:12:11.200' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,3,0,CAST('20140417 18:12:33.330' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,4,0,CAST('20140417 18:12:48.803' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,13,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,11,0,CAST('20141209 09:45:58.993' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,12,0,CAST('20141209 09:46:50.070' as DATETIME),'Stored procedure build');

这是SQLFiddle一些示例数据。

具有 T-sql 专业知识的人可以指导我如何实现最终结果吗？我知道PIVOT（使用动态列）将是正确的方法，但无法弄清楚。

预期成绩 ：

到目前为止，我有以下内容：

select row_number() over (
        partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_DATE 
        ) as rownum
    ,CONVERT(varchar(10), SCHEMA_VER_DATE, 110) as UPG_DATE
    ,CONVERT(varchar(1), SCHEMA_VER_MAJOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_MINOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_SUB) as SCHEMA_VER
from audit_schema_version
where SCHEMA_VER_REMARK like 'Stored procedure build'
order by UPGRADE_DATE 

问题

1. 为什么尽管这两个数据库的 log_send_queue_size 越来越大，但 log_send_rate 却一直在下降？
2. 此时网络上没有带宽问题，其他数据库也没有遇到此问题。如果再次发生这种情况，除了必须在辅助数据库上手动恢复主数据库以重新同步该对之外，是否有推荐的修复方法？

环境 ：

SQL 2012，SP1 CU7（内部版本 3393）

Windows Server 2012 标准版（内部版本 9200）

10 个数据库的可用性组 (PRDDB1-AG1)

2 个 AG 副本，一个在伦敦，一个在纽约（LDSERVER1 和 NYSERVER1），主要在纽约，次要在伦敦。

AG1 内的 2 个数据库，E-DB1（50GB 日志文件）和T-DB2（250GB 日志文件）

数据库从客户端导入文件，T-DB2处理它们（大量日志活动），然后输出/更新数据库中的E-DB1数据。

此过程会针对两个数据库生成大量数据变动和日志活动。我们偶尔会在伦敦和纽约的数据库副本之间出现延迟高峰，最多可能每周最多一次或两次，但这些总是在几个小时内清除。

问题 ：

上周我们看到 log_send_queue_size 增加，而 log_send_rate 减少。这从星期一开始，一直持续到星期五晚上，当它被手动解决时（请参阅下面的修复部分）。在最低时，E-DB1 数据库的 log_send_rate 刚刚超过 100KB/秒，而 log_send_queue 超过 40GB。T-DB2 数据库的 log_send_rate 为 2000KB/秒，缩小到 300KB/秒，log_send_queue 超过 300GB。

这导致可用性组中这两个数据库的主副本和辅助副本之间的延迟量增加。其特点是在每个受影响的数据库的事务日志中累积日志活动，这是意料之中的。由于这种延迟，受影响的每个数据库的日志都会扩展到日志驱动器有空间不足的危险。

这种延迟只发生在这两个数据库上，尽管可用性组内所有数据库的事务活动出现了相当大的峰值，这是正常的。

在整个问题中，辅助节点上的重做队列没有增加，redo_rate 保持在高位。这意味着问题是由于两个受影响数据库的低发送率造成的。

尝试的步骤

1. 暂停 T-DB2 数据库的数据移动。我希望这会为优先数据库 E-DB1 释放网络带宽。没有效果。

2. 重新启动辅助节点 (LDPRDENTDB1)。没有效果。

使固定

3. 以下步骤解决了该问题。随着日志文件增长到超过 300GB，我需要在磁盘空间用完之前尽快清除和缩小它们。

   一个。从可用性组中删除了数据库。

   湾。删除了辅助数据库。

   C。将数据库重新添加回主要的可用性组（NYSERVER1，手动同步选项）。

   d。备份主数据库并恢复到辅助数据库（70GB 从纽约复制到 LD，不到 24 小时）

   e. 将数据库重新添加回辅助服务器上的可用性组。

我试图找出谁更改了 SQL Server 2008 R2 中的登录密码。

我已经检查了默认跟踪 - 它不会记录该事件。默认跟踪将包括这些与安全相关的事件：

/*
    Audit Add DB user event
    Audit Add login to server role event
    Audit Add Member to DB role event
    Audit Add Role event
    Audit Add login event
    Audit Backup/Restore event
    Audit Change Database owner
    Audit DBCC event
    Audit Database Scope GDR event (Grant, Deny, Revoke)
    Audit Login Change Property event
    Audit Login Failed
    Audit Login GDR event
    Audit Schema Object GDR event
    Audit Schema Object Take Ownership
    Audit Server Starts and Stops 
*/

另外，查看事务日志备份以找出答案，但没有运气。

有没有其他方法可以找到它？

另外，我知道服务器端跟踪会有所帮助，但不幸的是，在我们的服务器端跟踪中，我们没有包含Audit Login Change Password Event.

我发现的最佳文章来自 Aaron Bertrand：Tracking Login Password Changes in SQL Server

我想了解为什么在 UAT（3 秒内运行）与 PROD（23 秒内运行）上执行相同的查询会有如此巨大的差异。

UAT 和 PROD 都有准确的数据和索引。

询问：

set statistics io on;
set statistics time on;

SELECT CONF_NO,
       'DE',
       'Duplicate Email Address ''' + RTRIM(EMAIL_ADDRESS) + ''' in Maintenance',
       CONF_TARGET_NO
FROM   CONF_TARGET ct
WHERE  CONF_NO = 161
       AND LEFT(INTERNET_USER_ID, 6) != 'ICONF-'
       AND ( ( REGISTRATION_TYPE = 'I'
               AND (SELECT COUNT(1)
                    FROM   PORTFOLIO
                    WHERE  EMAIL_ADDRESS = ct.EMAIL_ADDRESS
                           AND DEACTIVATED_YN = 'N') > 1 )
              OR ( REGISTRATION_TYPE = 'K'
                   AND (SELECT COUNT(1)
                        FROM   CAPITAL_MARKET
                        WHERE  EMAIL_ADDRESS = ct.EMAIL_ADDRESS
                               AND DEACTIVATED_YN = 'N') > 1 ) ) 

在 UAT 上：

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 11 ms, elapsed time = 11 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(3 row(s) affected)
Table 'Worktable'. Scan count 256, logical reads 1304616, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PORTFOLIO'. Scan count 1, logical reads 84761, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2418 ms,  elapsed time = 2442 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

在产品上：

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(3 row(s) affected)
Table 'PORTFOLIO'. Scan count 256, logical reads 21698816, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 23937 ms,  elapsed time = 23935 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

请注意，在 PROD 上，查询建议缺少索引，正如我测试过的那样，这是有益的，但这不是讨论的重点。

我只想了解：在 UAT 上 - 为什么 sql server 创建一个工作表而在 PROD 上却没有？它在 UAT 而不是 PROD 上创建表假脱机。另外，为什么 UAT 与 PROD 的执行时间如此不同？

笔记 ：

我在两台服务器上都运行 sql server 2008 R2 RTM（很快就会用最新的 SP 打补丁）。

UAT：最大内存 8GB。MaxDop、处理器亲和性和最大工作线程数为 0。

Logical to Physical Processor Map:
*-------  Physical Processor 0
-*------  Physical Processor 1
--*-----  Physical Processor 2
---*----  Physical Processor 3
----*---  Physical Processor 4
-----*--  Physical Processor 5
------*-  Physical Processor 6
-------*  Physical Processor 7

Logical Processor to Socket Map:
****----  Socket 0
----****  Socket 1

Logical Processor to NUMA Node Map:
********  NUMA Node 0

产品：最大内存 60GB。MaxDop、处理器亲和性和最大工作线程数为 0。

Logical to Physical Processor Map:
**--------------  Physical Processor 0 (Hyperthreaded)
--**------------  Physical Processor 1 (Hyperthreaded)
----**----------  Physical Processor 2 (Hyperthreaded)
------**--------  Physical Processor 3 (Hyperthreaded)
--------**------  Physical Processor 4 (Hyperthreaded)
----------**----  Physical Processor 5 (Hyperthreaded)
------------**--  Physical Processor 6 (Hyperthreaded)
--------------**  Physical Processor 7 (Hyperthreaded)

Logical Processor to Socket Map:
********--------  Socket 0
--------********  Socket 1

Logical Processor to NUMA Node Map:
********--------  NUMA Node 0
--------********  NUMA Node 1

更新 ：

UAT 执行计划 XML：

http://pastebin.com/z0PWvw8m

产品执行计划 XML：

http://pastebin.com/GWTY16YY

UAT 执行计划 XML - 从 PROD 生成的计划：

http://pastebin.com/74u3Ntr0

服务器配置：

产品：PowerEdge R720xd - Intel(R) Xeon(R) CPU E5-2637 v2 @ 3.50GHz。

UAT：PowerEdge 2950 - Intel(R) Xeon(R) CPU X5460 @ 3.16GHz

我已在answers.sqlperformance.com上发布

更新 ：

感谢@swasheck 的建议

将 PROD 的最大内存从 60GB 更改为 7680 MB，我可以在 PROD 中生成相同的计划。查询与 UAT 同时完成。

现在我需要了解 - 为什么？另外，这样一来，我就无法证明这个怪物服务器取代旧服务器是合理的！

我正在尝试在 2 节点 NUMA 服务器上分配一组 CPU，如下所示：

以下来自 coreinfo，显示了服务器上的物理处理器、逻辑处理器和 NUMA 节点。

Logical to Physical Processor Map:
**--------------  Physical Processor 0 (Hyperthreaded)
--**------------  Physical Processor 1 (Hyperthreaded)
----**----------  Physical Processor 2 (Hyperthreaded)
------**--------  Physical Processor 3 (Hyperthreaded)
--------**------  Physical Processor 4 (Hyperthreaded)
----------**----  Physical Processor 5 (Hyperthreaded)
------------**--  Physical Processor 6 (Hyperthreaded)
--------------**  Physical Processor 7 (Hyperthreaded)

Logical Processor to Socket Map:
********--------  Socket 0
--------********  Socket 1

Logical Processor to NUMA Node Map:
********--------  NUMA Node 0
--------********  NUMA Node 1

我正在运行 2 个 sql server 2008R2 标准版实例。

我所做的是：

将NODE 0 绑定到实例 1。因此实例1将有 8 个逻辑处理器。

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0
GO

2013-11-07 13:01:20.79 Server      Processor affinity turned on: node 0, processor mask 0x00000000000000ff. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-11-07 13:01:20.79 Server      Processor affinity turned on: node 1, processor mask 0x0000000000000000. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-11-07 13:01:20.82 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2013-11-07 13:01:20.82 Server      Lock partitioning is enabled.  This is an informational message only. No user action is required.
2013-11-07 13:01:20.83 Server      Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-11-07 13:01:20.83 Server      Node configuration: node 1: CPU mask: 0x000000000000ff00:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

我可以看到实例 1 能够监听 NODE 0，如下所示：

2013-11-07 13:01:21.05 服务器 SQL Server 网络接口在节点关联掩码 0x0000000000000001 的多节点 (NUMA) 服务器配置的节点 0 上初始化侦听器。这只是一条信息性消息。无需用户操作。

现在在实例 2 上：

将NODE 1 绑定到实例 2。因此实例2将有 8 个逻辑处理器。

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 1
GO

错误日志也证实了这一点：

2013-11-07 13:03:51.16 Server      Detected 16 CPUs. This is an informational message; no user action is required.
2013-11-07 13:03:51.49 Server      Processor affinity turned on: node 0, processor mask 0x000000000000ff00. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-11-07 13:03:51.49 Server      Processor affinity turned on: node 1, processor mask 0x0000000000000000. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-11-07 13:03:51.52 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2013-11-07 13:03:51.52 Server      Lock partitioning is enabled.  This is an informational message only. No user action is required.
2013-11-07 13:03:51.53 Server      Node configuration: node 0: CPU mask: 0x000000000000ff00:0 Active CPU mask: 0x000000000000ff00:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-11-07 13:03:51.53 Server      Node configuration: node 1: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

但是为什么 INSTANCE2 监听 NUMA 0 而不是 NUMA 1 呢？

两个实例都将 MAXDOP 设置为 4。

2013-11-07 13:03:51.78 服务器 SQL Server 网络接口在节点关联掩码 0x0000000000000001 的多节点 (NUMA) 服务器配置的节点 0 上初始化侦听器。这只是一条信息性消息。无需用户操作。

此外，以下是我的 TCP/IP 设置：

笔记：

我也尝试过以下事情：

1. 先关闭 CPU 亲和力，然后绑定端口，看看是否有效。这工作正常，我能够看到 NUMA 0 在一个端口上侦听，NUMA 1 在第二个端口上侦听。这就是我想要的，但我不想关闭 CPU Affinity。我的方向正确吗？

2. CPU 亲和力开启。尝试 NUMA 分配给端口。这在实例 1 绑定到 NUMA 0 并且实例 2 未在错误日志中显示它正在侦听 NUMA 1 的意义上不起作用。

谢谢 ！

需要以下代码的帮助，因为它因截断错误而失败

发生截断错误。命令已中止。

create table monCacheQuality (
        ServerName sysname
        ,CollectionDateTime smalldatetime not null
        ,PhysicalWrites decimal(15, 0) not null
        ,PhysicalReads decimal(15, 0) not null
        ,LogicalReads decimal(15, 0) not null
        ,CacheQuality decimal(15, 0) not null
        ,CacheHitPct decimal(15,4) not null
        )

-- Main code starts here 
declare @physical_read1 decimal(15, 0)
    ,@logical_read1 decimal(15, 0)
    ,@physical_write1 decimal(15, 0)
    ,@cache_search1 decimal (15,4)

declare @physical_read2 decimal(15, 0)
    ,@logical_read2 decimal(15, 0)
    ,@physical_write2 decimal(15, 0)
    ,@cache_search2 decimal (15,4)

while (1=1)
begin
    select @physical_write1 = PhysicalWrites
        ,@physical_read1 = PhysicalReads
        ,@logical_read1 = LogicalReads
        ,@cache_search1 = CacheSearches
    from master..monDataCache

    waitfor delay '00:00:20' -- Log every 20 sec

    select @physical_write2 = PhysicalWrites
        ,@physical_read2 = PhysicalReads
        ,@logical_read2 = LogicalReads
        ,@cache_search2 = CacheSearches
    from master..monDataCache

    insert monCacheQuality
    select @@servername as ServerName
        ,getUTCdate()
        ,@physical_write2 - @physical_write1
        ,@physical_read2 - @physical_read1
        ,@logical_read2 - @logical_read1
        ,case 
            when @physical_read2 - @physical_read1 = 0
                then - 1
            else (@logical_read2 - @logical_read1) / (@physical_read2 - @physical_read1)
            end as CacheQuality
        ,100-(((@physical_read2-@physical_read1)/(@cache_search2-@cache_search1))*100) as CacheHitPct
end