GoodwinSQL's questions

我们有一个独特的情况，我们希望允许用户使用 SSMS 查询数据库的可读辅助副本以进行临时报告，但不允许他们从主副本中读取。我们已经设置了只读路由来实现这一点。这也是 SQL 2016 上的全部内容。

我最初的想法是在主副本和辅助副本上创建登录名，并授予对相关数据库的读取访问权限。然后我们会拒绝连接或禁用当前主副本上的登录。在 SSMS 中，用户可以使用 ApplicationIntent=ReadOnly 连接到侦听器并被路由到辅助副本，而无需接触主副本。

我们将使用基本逻辑在主副本服务器和辅助副本服务器上设置一个简单的作业：如果当前服务器 = 主然后禁用登录；如果当前服务器 = 辅助服务器，则启用登录。

问题是当登录在主服务器上被禁用时，我在连接到具有只读意图的侦听器时登录失败。当我在主副本上重新启用登录时，它工作得很好，并且连接被正确路由到可读的辅助副本。

我在主服务器上设置了一个跟踪，果然，我可以看到登录连接并在主副本上的 master 和 msdb 中运行一些系统类型查询——即使我在 SSMS 中使用 ApplicationIntent=ReadOnly 连接。我不确定这是否是 SSMS 在幕后所做的事情，或者它是否是通过只读路由过程的登录的默认行为。

以下是我在主数据库上使用快速分析器跟踪捕获的查询：

--msdb query

    select 
        case 
            when object_id('dbo.sysdac_instances') is not null then 1             
        else
            0 
        end

--master query

SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

以前有人处理过这种情况吗？看来我们基本上需要允许主副本上的登录连接权限，同时拒绝它对主副本上 AG 中的数据库的读取访问权限，但授予该登录权限以读取可读辅助副本上的数据库。

另一种选择是创建一个直接指向辅助副本的 DNS 条目，但我们不能保证副本始终是辅助副本，因为可能会发生故障转移。

我们遇到了一个问题，即扩展事件的内存使用量增长到超过 1 GB，然后将无法清除 - 即使我们停止了实例上的所有 XEvent 会话。这在大多数服务器上还不是问题，但它确实伤害了我们的一些较小的开发/测试服务器，只有几 GB 的内存分配给 SQL Server。我希望外面的人可能对如何解决这个问题有一些想法，而不是弹回 SQL Server。

一些相关的查询输出：

    SELECT TOP 5 type
       ,NAME
       ,sum(pages_kb) / 1024 AS pages_mb
    FROM sys.dm_os_memory_clerks
       GROUP BY type
       ,NAME
    ORDER BY sum(pages_kb) DESC
    ------------------------------
        type                         NAME                   pages_mb
        MEMORYCLERK_XE               XE Engine              1133
        USERSTORE_TOKENPERM          TokenAndPermUserStore  55
        MEMORYCLERK_SOSNODE          SOS_Node               43
        MEMORYCLERK_SQLBUFFERPOOL    Default                41
        MEMORYCLERK_SQLGENERAL       Default                18
    ------------------------------    
    SELECT NAME
      ,total_buffer_size / 1024 AS total_buffer_size_kb
    FROM sys.dm_xe_sessions
    ORDER BY total_buffer_size_kb DESC
    ------------------------------
        name                                total_buffer_size_kb
        system_health                       4223
        SpotlightResponseTimeWaitData_3843  4223
        SpotlightWorkloadAnalysis_3843      4223
        SpotlightDeadlockDetection_3843     4031
        sp_server_diagnostics session       383

SELECT @@VERSION
    Microsoft SQL Server 2012 (SP1) - 11.0.3437.0 (X64) 
        May 29 2014 16:03:40 
        Copyright (c) Microsoft Corporation
        Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

全面披露——我们运行 Dell Spotlight for SQL Server，默认情况下在每个实例上打开三个 XEvents 会话。这 3 个会话的代码如下。

我什至在此实例上禁用了 Spotlight，删除了三个 XEvent Spotlight 会话，并停止了 system_health 会话。MEMORYCLERK_XE 内存使用量保持在 1120 MB。DBCC FREESYSTEMCACHE ('ALL') 也没有效果。

3 个聚光灯会议：

CREATE EVENT SESSION [SpotlightDeadlockDetection_3843] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.ring_buffer (SET occurrence_number = (0))
    WITH (
            MAX_MEMORY = 4000 KB
            ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
            ,MAX_DISPATCH_LATENCY = 10 SECONDS
            ,MAX_EVENT_SIZE = 0 KB
            ,MEMORY_PARTITION_MODE = NONE
            ,TRACK_CAUSALITY = OFF
            ,STARTUP_STATE = OFF
            )
GO


CREATE EVENT SESSION [SpotlightResponseTimeWaitData_3843] ON SERVER ADD EVENT sqlos.wait_info (
    ACTION(package0.collect_system_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.username) WHERE (
        [opcode] = (1)
        AND (
            [duration] > (0)
            OR [signal_duration] > (0)
            )
        AND [sqlserver].[query_hash] <> (0)
        AND [package0].[counter] <= (1000)
        AND [wait_type] <> (214)
        )
    )
    ,ADD EVENT sqlos.wait_info_external (
    ACTION(package0.collect_system_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.username) WHERE (
        [opcode] = (1)
        AND [duration] > (0)
        AND [sqlserver].[query_hash] <> (0)
        AND [package0].[counter] <= (1000)
        AND [wait_type] <> (589)
        )
    ) ADD TARGET package0.ring_buffer (
    SET max_events_limit = (4000)
    ,occurrence_number = (1000)
    )
    WITH (
            MAX_MEMORY = 4096 KB
            ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
            ,MAX_DISPATCH_LATENCY = 30 SECONDS
            ,MAX_EVENT_SIZE = 0 KB
            ,MEMORY_PARTITION_MODE = NONE
            ,TRACK_CAUSALITY = ON
            ,STARTUP_STATE = OFF
            )
GO


CREATE EVENT SESSION [SpotlightWorkloadAnalysis_3843] ON SERVER ADD EVENT sqlserver.sp_statement_completed (
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.plan_handle, sqlserver.session_id, sqlserver.sql_text, sqlserver.username) WHERE (
        [sqlserver].[is_system] = (0)
        AND [package0].[counter] <= (1000)
        )
    )
    ,ADD EVENT sqlserver.sql_statement_completed (
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.plan_handle, sqlserver.session_id, sqlserver.sql_text, sqlserver.username) WHERE (
        [sqlserver].[is_system] = (0)
        AND [package0].[counter] <= (1000)
        )
    ) ADD TARGET package0.ring_buffer (
    SET max_events_limit = (4000)
    ,occurrence_number = (1000)
    )
    WITH (
            MAX_MEMORY = 4096 KB
            ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
            ,MAX_DISPATCH_LATENCY = 30 SECONDS
            ,MAX_EVENT_SIZE = 0 KB
            ,MEMORY_PARTITION_MODE = NONE
            ,TRACK_CAUSALITY = ON
            ,STARTUP_STATE = OFF
            )
GO

有谁知道可能是什么原因造成的？有什么办法可以释放这个用过的内存吗？

我们有一个将数据插入表的应用程序。不幸的是，我们遇到了死锁，而死锁仅来自插入。我们看到插入在导致问题的非聚集索引上以不同的顺序获取键锁。

为什么插入行为会这样？我们应该怎么做才能缓解死锁？任何帮助或见解表示赞赏。

在下面的示例中，只涉及两个插入，但我们有多达 4 个不同的插入涉及死锁。

这是死锁图：

    <deadlock>
    <victim-list>
        <victimProcess id="process3ab355868" />
    </victim-list>
    <process-list>
        <process id="process3ab355868" taskpriority="0" logused="1184" waitresource="KEY: 5:72057594043629568 (6234ed5bf036)" waittime="7493" ownerId="92332106" transactionname="implicit_transaction" lasttranstarted="2014-10-13T12:37:43.060" XDES="0x123699668" lockMode="X" schedulerid="3" kpid="3540" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-10-13T12:37:44.333" lastbatchcompleted="2014-10-13T12:37:44.333" lastattention="1900-01-01T00:00:00.333" clientapp="Microsoft JDBC Driver for SQL Server" hostname="" hostpid="0" loginname="" isolationlevel="read committed (2)" xactid="92332106" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
            <executionStack>
                <frame procname="adhoc" line="1" stmtstart="278" stmtend="818" sqlhandle="0x0200000053a65d302154b91e9fee55234669030a42479c050000000000000000000000000000000000000000">
                    INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)
                </frame>
                <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
            <inputbuf>
                (@P0 datetime2,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime2,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 decimal(38,1),@P7 int,@P8 int)INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)                                                                         select SCOPE_IDENTITY() AS GENERATED_KEYS
            </inputbuf>
        </process>
        <process id="process14b38c928" taskpriority="0" logused="2564" waitresource="KEY: 5:72057594043629568 (275232b7b238)" waittime="7491" ownerId="92325909" transactionname="implicit_transaction" lasttranstarted="2014-10-13T12:37:39.567" XDES="0x16b38b988" lockMode="X" schedulerid="3" kpid="3668" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-10-13T12:37:44.337" lastbatchcompleted="2014-10-13T12:37:44.337" lastattention="1900-01-01T00:00:00.337" clientapp="Microsoft JDBC Driver for SQL Server" hostname="" hostpid="0" loginname="" isolationlevel="read committed (2)" xactid="92325909" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
            <executionStack>
                <frame procname="adhoc" line="1" stmtstart="278" stmtend="818" sqlhandle="0x0200000053a65d302154b91e9fee55234669030a42479c050000000000000000000000000000000000000000">
                    INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)
                </frame>
                <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
            <inputbuf>
                (@P0 datetime2,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime2,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 decimal(38,1),@P7 int,@P8 int)INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)                                                                         select SCOPE_IDENTITY() AS GENERATED_KEYS
            </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594043629568" dbid="5" objectname="table1" indexname="unique_index" id="lock17bc3a480" mode="X" associatedObjectId="72057594043629568">
            <owner-list>
                <owner id="process14b38c928" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process3ab355868" mode="X" requestType="wait" />
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594043629568" dbid="5" objectname="table1" indexname="unique_index" id="lock10735ce00" mode="X" associatedObjectId="72057594043629568">
            <owner-list>
                <owner id="process3ab355868" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process14b38c928" mode="X" requestType="wait" />
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

这是 DDL 表：

CREATE TABLE [table1](
    [col0] [int] IDENTITY(1,1) NOT NULL,
    [col1] [int] NOT NULL,
    [col2] [int] NOT NULL,
    [col3] [decimal](15, 4) NULL,
    [col4] [datetime2](7) NOT NULL,
    [col5] [varchar](8) NOT NULL,
    [col6] [varchar](30) NOT NULL,
    [col7] [datetime2](7) NOT NULL,
    [col8] [varchar](8) NOT NULL,
    [col9] [varchar](30) NOT NULL,
 CONSTRAINT [PK] PRIMARY KEY CLUSTERED 
(
    [col0] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [unique_index] UNIQUE NONCLUSTERED 
(
    [col2] ASC,
    [col1] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE table1 ADD  DEFAULT (sysdatetime()) FOR [col4]
GO

ALTER TABLE table1 ADD  DEFAULT (sysdatetime()) FOR [col7]
GO