主页 / dba / 问题

问题[sql-server-2017](dba)

Martin Hope
Racer SQL
Asked: 2024-08-28 00:36:11 +0800 CST
  • 5

将数据库从一台服务器移动到另一台服务器后，我们开始遇到一个问题：

> Msg 15581, Level 16, State 7, Procedure xxxxx, Line 24 [Batch Start Line 2]
Please create a master key in the database or open the master key in the session before performing this operation.

Msg 15315, Level 16, State 1, Procedure xxxxx, Line 62 [Batch Start Line 2]
The key '<CustomKeyName>' is not open. Please open the key before using it.

所讨论的程序具有：

 OPEN SYMMETRIC KEY MySymKey
 DECRYPTION BY CERTIFICATE MySymCert;

我可以验证移动数据库后，我可以通过查询和来看到asymmetric key和a 。 certificatesys.symmetric_keyssys.certificates在此处输入图片描述

是的，我们没有主密钥。我很好奇这个过程是否可行：

  1. 在旧服务器中（在数据库范围内）：

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'a_cool_pass';

  1. 再次备份数据库

  2. 在新服务器中再次恢复数据库（使用替换）

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'the_cool_Pass'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

这是解决此情况的适当方法吗？

或者，我可以删除 symetric_key 和 cert，然后重新创建吗？

编辑：REGENERATING将主密钥传递到旧服务器，备份/恢复到新服务器， OPEN MASTER KEY DECRYPTION BY PASSWORD =遇到了问题The key is not encrypted using the specified decryptor.（我认为这很奇怪。我明天会再试一次。

（我需要对证书进行备份恢复吗？

sql-server-2017
Martin Hope
Andy
Asked: 2024-04-02 17:25:40 +0800 CST
  • 5

我有一个 SQL Server 存储过程，它本质上将复杂的数据库“扁平化”为单独数据库中的单个简化表，以允许用户查询数据而无需了解数据库结构。SP 截断目标表，插入主键和其他字段，然后使用用户函数和各种查询来执行一系列更新以填充表中的所有其他字段以格式化数据。有大约。表中有 900 万行，SP 运行大约需要 2.5 小时，在此期间日志文件增长到超过 30GB。该数据库位于磁盘空间有限的 Azure 服务器上，最近由于日志文件空间不足，SP 崩溃了。

目标数据库处于简单恢复模式，并且 SP 运行了一个周末，因此没有其他进程或用户使用它。SP 不包含任何事务或提交，也不会对工作进行分块，它只是从开始到结束插入、更新（偶尔删除）行。如何提高效率并使用更少的日志文件空间？它应该在运行时使用事务、提交或收缩日志文件吗？或者是其他东西？

sql-server-2017
Martin Hope
ktakmn
Asked: 2024-01-16 02:21:17 +0800 CST
  • 6

我的基础设施如下图所示。

Web 服务器 1 位于 Windows 域之外。Web 服务器 2 位于 Windows 域内。

SQL Server 1 和 2 都在域内。

存在从 SQL Server 1 到 SQL Server 2 的链接服务器连接。

两个 SQL Server 都只接受 Windows 身份验证，而不接受 SQL 身份验证。

两个 SQL Server 都具有具有不受约束委派的 Kerberos SPN 设置。

Web Server 2 上的域用户在 SQL Server 1 和 SQL Server 2 上都配置了 SQL 登录名，并且可以通过链接服务器查询 SQL Server 1 和 SQL Server 2 上的数据。

Web Server 1 上的本地用户具有在 SQL Server 1 和 SQL Server 2 上配置的相同名称和密码的本地登录名，并且可以查询 SQL Server 1 上的数据，但不能通过链接服务器查询 SQL Server 2 上的数据。SQL Server 日志中出现以下错误：

用户“NT AUTHORITY\ANONYMOUS LOGON”登录失败。原因：找不到与所提供的名称匹配的登录名。

问题：是否可以使 Windows 域之外的 Web Server 1 通过 SQL Server 1 上的链接服务器连接成功查询来自 SQL Server 2 的数据？如果是这样，怎么办？

在此输入图像描述

sql-server-2017
Martin Hope
SlowMagic
Asked: 2023-02-27 01:07:31 +0800 CST
  • 5

下面的查询看起来简单明了，但它会产生意想不到的结果。


CREATE TABLE #NUMBERS
(
    N BIGINT
);

INSERT INTO #NUMBERS VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9)
;



WITH
A AS
(   
    -- CHOOSE A ROW AT RANDOM
    SELECT   TOP 1 *
    FROM     #NUMBERS            
    ORDER BY NewID()           
),
B AS
(
    SELECT A.N AS QUANTITY, 'METERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*100 AS QUANTITY, 'CENTIMETERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*1000 AS QUANTITY, 'MILLIMETERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*1000000 AS QUANTITY, 'MICRONS' AS UNIT FROM A

    UNION ALL

    SELECT A.N*1000000000 AS QUANTITY, 'NANOMETERS' AS UNIT FROM A
)
SELECT   *
FROM     B
ORDER BY B.QUANTITY
;

我希望它执行一次 CTE A，然后将这些结果带入 CTE B 以产生如下所示的结果：

数量 单元
4个 米
400 厘米
4000 毫米
400万 微米
4000000000 纳米

但是，它会产生如下结果：

数量 单元
8个 米
700 厘米
1000 毫米
600万 微米
3000000000 纳米

这意味着它将返回并执行 CTE A 五次，每次在 CTE B 中提及 A 一次。这不仅是不需要的和不直观的，而且看起来效率也不必要地低。

发生了什么，CTE 天才将如何重写它以产生预期的结果？


顺便说一句，关于 CTE 的 Microsoft 文档页面包含这个可能相关也可能不相关的神秘声明：

如果定义了多个 CTE_query_definition，则查询定义必须由以下集合运算符之一连接：UNION ALL、UNION、EXCEPT 或 INTERSECT。


最后，重写查询以消除 CTE B 并没有帮助：

WITH
A AS
(   
    -- CHOOSE A ROW AT RANDOM
    SELECT   TOP 1 *
    FROM     #NUMBERS            
    ORDER BY NewID()           
)
SELECT   *
FROM     (
          SELECT A.N AS QUANTITY, 'METERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*100 AS QUANTITY, 'CENTIMETERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*1000 AS QUANTITY, 'MILLIMETERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*1000000 AS QUANTITY, 'MICRONS' AS UNIT FROM A

          UNION ALL

          SELECT A.N*1000000000 AS QUANTITY, 'NANOMETERS' AS UNIT FROM A

         ) AS B
ORDER BY B.QUANTITY
;
sql-server-2017
Martin Hope
Muflix
Asked: 2022-10-17 08:31:23 +0800 CST
  • 8

我在 SSDT 中有 CLR 程序集，并且必须对其进行部署。据我了解，有 4 个选项可以做到这一点

第一个选项，使用 TRUSTWORTHY

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
  
ALTER DATABASE SourceDatabase SET TRUSTWORTHY ON;

第二个选项，禁用严格的安全性

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
  
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;

第三种选择，使用密钥或证书签署程序集

Seems complicated and I was not able to manage that yet. I will appreciate the instructions, because the workflow is not clear here.

第四种选择，使用sp_add_trusted_assembly

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;

declare @assembly varbinary(max) = 0x4D5A90000300000004000000FFFF0000... -- I have to manually copy that from the failed SQL publish file.

declare @hash varbinary(64) = HASHBYTES('SHA2_512', @assembly);

EXEC sys.sp_add_trusted_assembly @hash, N'Foo Assembly';

在第四个选项中，我必须手动将程序集注册为受信任，然后才能发布程序集。有可能以某种方式自动化这个过程吗？

我正在考虑创建pre-deployment script可以运行第 4 个选项代码的程序，但我不知道如何从程序集的文件中填充 @assembly 变量.dll。

或者，如果可以将程序集部署为不受信任，我可以使用以下代码使其在服务器上受信任（post-deployment script）

-- Register all database assemblies as trusted
declare @name nvarchar(4000),
@content varbinary(max);

DECLARE appCursor CURSOR FAST_FORWARD FOR
    SELECT [name], content
    FROM   SourceDatabase.sys.assembly_files
 
OPEN appCursor
FETCH NEXT FROM appCursor INTO @name, @content
 
WHILE @@FETCH_STATUS = 0
BEGIN
   
   DECLARE @hash varbinary(64) = HASHBYTES('SHA2_512', @content);

    EXEC sys.sp_add_trusted_assembly @hash, @name;

   FETCH NEXT FROM appCursor INTO @name, @content
END
CLOSE appCursor
DEALLOCATE appCursor
GO

任何想法或你的方法是什么？

sql-server-2017
Martin Hope
Manan Adhvaryu
Asked: 2022-10-06 07:28:46 +0800 CST
  • 1

尝试安装 SQL Server 2017 标准时出现此错误。我也在新格式化的系统上安装它。网络设置为私有，我不确定这是否重要。

我也以管理员身份开始安装，然后将当前用户添加到 SQL 系统管理员列表中，但我不断收到此错误。我什至重新格式化了这台电脑，但我一直收到同样的错误。

有谁知道如何解决这个问题？该系统也不在任何域策略下。

错误图片

sql-server sql-server-2017
Martin Hope
Klam Huggeren
Asked: 2022-09-07 07:03:00 +0800 CST
  • 0

尽管关于同一主题的许多问题我一直无法找到正确的解决方案。我正在使用一款软件将 ID 作为逗号分隔值而不是联结表聚合到另一个表中。

该软件公开了三个视图Department，Team并且Task.

为了Department

| ID     |  NAME |
|--------|-------|
|  12345 |    IT |
|  25695 |    HR |
|  89685 |   PAY |
|  47896 |   OPS |

Team

| ID     |  NAME |    DEPARTMENT |
|--------|-------|---------------|
|  34252 | TEAM1 |         12345 |
|  54234 | TEAM2 |         12345 |
|  52385 | TEAM1 |          NULL |
|  91231 | TEAM1 |         89685 |

Task

| ID     |  NAME |             Team |
|--------|-------|------------------|
|      1 |    Ta |34252,54234,52385 |
|      2 |    Tb |      52385,91231 |
|      3 |    Tc |             NULL |
|      4 |    Td |            34252 |

我尝试实现以下结构：

| DepID  |   Dep | TeamID |   Team | TaskID |  Task |
|--------|-------|--------|--------|--------|-------|

每个任务都有一行，如果分配给多个团队，可能会出现不止一次，如果分配给没有团队或团队尚未分配给部门，则根本不会出现。同样，我只有这三种观点。创建所述结构的最有效方法是什么？我在 SQLServer 2017 上。

我尝试从任务表开始CROSS APPLY STRING_SPLIT，然后RIGHT JOIN到团队以及从部门LEFT JOIN团队开始。

sql-server sql-server-2017
Martin Hope
Dave C
Asked: 2022-08-17 18:44:57 +0800 CST
  • 5

我们在 DEV/QA/PROD SQL 2017 Enterprise 服务器上遇到了看似随机的错误 824。服务器运行几乎相同的代码，通过 ETL 流程将相同的日常文件摄取到我们的数据仓库中。这些错误最早是在 2022 年 5 月左右发现的，但由于日志清理，我们无法确定（供应商提供的）ETL 进程是否正在捕获这些错误、记录警告并继续处理而不是失败！

DEV/QA 已修补到 CU30（最新的 CU）——条件仍然存在。CU22 的生产落后了几个补丁，并计划在未来几周内进行修补。

例子：

SQL Server 检测到基于逻辑不一致的 I/O 错误：校验和不正确（预期为 0xc30164e7；实际为 0x9f2bc675c）。它发生在读取文件“H:\tempdb_mssql_6.ndf”中偏移量 0x0000027de40000 的数据库 ID 2 中的页面 (7:1306400) 期间。

如前所述，这在我们所有的环境中都是随机发生的。所有服务器都是虚拟化的。DEV/QA 都使用相同的 SAN。生产在不同数据中心的单独 SAN 上。我没有关于 SAN 设备的品牌/型号的详细信息。

在大多数情况下，当这种情况发生时，它似乎主要在 tempdb 中（但并非总是如此）。此外，suspect_pages 通常是空的。它似乎也更频繁地发生在星期六，因为我们连续发生了 3-4 次。

还要注意的是，错误中列出的预期/实际值通常是相同的——但并非总是如此。

还注意到，特定的存储过程似乎更容易受到引发此错误的影响，但是，它已在 ETL 作业的多个其他位置发生，再次影响不同的数据库。似乎触发此错误的存储过程通常添加一个 PERSISTED 计算列，然后基于该计算列添加一个 ROW_NUMBER() 到 5 个表，大小范围从 200K 到 7.5M 行。我们昨天（在 QA 中）修改了此过程，以限制使用 ROW_NUMBER() 值更新的行数（仅当 rownum=1 时）并将该更新从一次全部更改为 25K 批处理方法。该错误今天在 QA 中再次发生——因此我们删除了计算列上的 PERSISTED 选项。我们实际上正在尝试任何事情来阻止质量检查中的这种情况，因为它似乎受到的影响最大。

DBCC CHECKDB 每天在所有生产数据库上运行，一个除外。一个跳过的数据库非常大，接近 4TB，DBCC CHECKDB 大约需要 12 小时才能完成。DBCC CHECKDB 已在此大型数据库的 QA 中最近的生产备份上运行，并且很干净。

到目前为止，只有 tempdb 在生产中受到影响。DEV/QA 已经看到其他几个数据库受到影响，但 DBCC CHECKDB 没有产生任何结果，即使怀疑页面包含一个条目。当它不是 tempdb 时，我们已将生产数据库恢复到 DEV/QA，只是为了安全。

当 tempdb 受到影响时，我们一直在停止 SQL，并删除有问题的 tempdb 文件并重新启动服务。

值得注意的是，H: 在生产中的使用率为 100%，因为 mdf/ndf 文件占用了整个 150GB 驱动器，但文件中有足够的可用空间。DEV/QA 没有达到 100%，并且有大约 30-40GB 的可用空间，并且都显示 tempdb 受到影响。所以我认为这不是驱动器空间问题。

我已经联系了我们的 DBA，他认为问题是由数据本身引起的。虽然我不同意数据有问题，但我们确实在 2022 年 4 月升级了生成由 ETL 作业读取的 CSV 文件的系统。源系统现在是基于 linux 而不是基于 Windows。ETL 过程无需更改即可读取新文件。文件中的数据在结构上发生了一些变化，但它们在很大程度上是相同的，CLR procs 可以根据每天发送的模式布局文件来适应结构变化。这些文件足够大（每天 55+GB），它们填充了 300 多个表，每个表都包含大量的 nvarchar(max) 字段。

我已经联系了我们的基础架构团队以检查 VM/SAN 运行状况——虽然生产的 VM 在文件摄取阶段报告了高 IO（时间戳完美对齐）——但没有报告错误。我们的基础架构团队在调查期间将 prod 虚拟机迁移到更快的主机，并将文件移动到新的相对未使用的较新的 SAN。

7 月 26 日，DEV/QA SAN 发生驱动器故障，导致两个驱动器被更换和重建。由于驱动器故障，我们在与 Microsoft 合作时没有发生任何事故，但是上周六和上周一 - QA 遇到了更多与 tempdb 相关的错误。

我们已经向 Microsoft 开了一张票，经过几周/电话，我们被告知他们是错误的团队（他们只帮助修复腐败），我们需要一张高级票。因此，我们目前正在向 Microsoft 索取报价，以获取帮助进行根本原因分析的首要票证。

同时，我希望生产不会有进一步的问题，我希望有人能够提供一些进一步的见解或指导，我们可以做些什么来分析这个问题，或者限制它的发生，直到我们能得到微软正确参与。

编辑 1a - 完整的 @@VERSION 输出

DEV: Microsoft SQL Server 2017 (RTM-CU30) (KB5013756) - 14.0.3451.2 (X64)   Jun 22 2022 18:20:15   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor) 
QA: Microsoft SQL Server 2017 (RTM-CU30) (KB5013756) - 14.0.3451.2 (X64)   Jun 22 2022 18:20:15   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor) 
PROD: Microsoft SQL Server 2017 (RTM-CU22-GDR) (KB4583457) - 14.0.3370.1 (X64)   Nov  6 2020 18:19:52   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)

编辑 1b - 可疑程序 失败发生在以下注释的代码中：BATCH UPDATE RN - CURRENT/PAST ROWS

SET NOCOUNT ON;
DECLARE @schema SYSNAME, @table SYSNAME, @RNCurrDepth SMALLINT, @RNFutureDepth SMALLINT, @SQL NVARCHAR(MAX);

BEGIN TRY
    DECLARE IDComp3 CURSOR LOCAL
    FOR
    SELECT DISTINCT S.name, T.name, P.RNCurrentDescDepth, P.RNFutureAscDepth
    FROM sys.columns C
    JOIN sys.tables T ON C.object_id=T.object_id
    JOIN sys.schemas S ON T.schema_id=S.schema_id
    JOIN dbo.ParseIDComp3Control P ON S.name=P.TableSchema AND T.name=TableName /* Control Table to limit the tables parsed, and how many ROW_NUMBERS to update */
    WHERE C.name='ID_COMP_3'
    AND P.Active=1
    ORDER BY T.name;
    OPEN IDComp3;
    FETCH NEXT FROM IDComp3 INTO @schema, @table, @RNCurrDepth, @RNFutureDepth;
    WHILE @@FETCH_STATUS=0
    BEGIN
        --RAISERROR(@TABLE,0,1) WITH NOWAIT;

        DROP TABLE IF EXISTS #TMP;
        CREATE TABLE #TMP ([@ID] NVARCHAR(150)
                          ,LEAD_CO_MNE NVARCHAR(50)
                          ,RNCURRENTDESC INT
                          ,RNFUTUREASC INT);

        DROP TABLE IF EXISTS #TMP_BATCH;
        CREATE TABLE #TMP_BATCH ([@ID] NVARCHAR(150)
                                ,LEAD_CO_MNE NVARCHAR(50)
                                ,RNCURRENTDESC INT
                                ,RNFUTUREASC INT);

        /* DROP ID_COMP_3 COLUMNS IF EXISTS, TO ENSURE WE HAVE NO VALUES */
        SET @SQL='';
        SELECT @SQL+='ALTER TABLE '+QUOTENAME(S.name)+'.'+QUOTENAME(T.name)+' DROP COLUMN '+QUOTENAME(C.name)+';'+CHAR(10)
            FROM sys.columns C
            JOIN sys.tables T ON C.object_id=T.object_id
            JOIN sys.schemas S ON T.schema_id=S.schema_id
            WHERE C.name IN ('ID_COMP_3_DATE','ID_COMP_3_ID')
            AND S.name=@schema
            AND T.name=@table
        EXEC SP_EXECUTESQL @SQL;

        /* ADD COMPUTED COLUMNS IF NOT EXISTS */
        IF NOT EXISTS (SELECT 1
                        FROM sys.columns C
                        JOIN sys.tables T ON C.object_id=T.object_id
                        JOIN sys.schemas S ON T.schema_id=S.schema_id
                        WHERE C.name='ID_COMP_3_DATE'
                        AND S.name=@schema
                        AND T.name=@table)
        BEGIN   
            SET @SQL = 'ALTER TABLE '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+'ADD ID_COMP_3_DATE AS TRY_CONVERT(DATE,LEFT(ID_COMP_3,8),112),
                                                                                    ID_COMP_3_ID AS TRY_CONVERT(INT,RIGHT(ID_COMP_3,LEN(ID_COMP_3)-CHARINDEX(''.'',ID_COMP_3)));'
            EXEC SP_EXECUTESQL @SQL;
        END

        /* DROP INDEXES IF EXISTS (SO WE CAN DROP COLUMNS) */
        SET @SQL='';
        SELECT @SQL+='DROP INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(S.name)+'.'+QUOTENAME(T.name)+CHAR(10)
            FROM sys.indexes I
            JOIN sys.tables T ON I.object_id=T.object_id
            JOIN sys.schemas S ON T.schema_id=S.schema_id
            WHERE T.name=@table
            AND S.name=@schema
            AND I.name IN ('IX_'+t.name+'_RN_Current_Desc', 'IX_'+t.name+'_RN_Future_Asc')
        EXEC SP_EXECUTESQL @SQL;

        /* DROP RN COLUMNS IF EXISTS, TO ENSURE WE HAVE NO VALUES */
        SET @SQL='';
        SELECT @SQL+='ALTER TABLE '+QUOTENAME(S.name)+'.'+QUOTENAME(T.name)+' DROP COLUMN '+QUOTENAME(C.name)+';'+CHAR(10)
            FROM sys.columns C
            JOIN sys.tables T ON C.object_id=T.object_id
            JOIN sys.schemas S ON T.schema_id=S.schema_id
            WHERE C.name IN ('RN_Current_Desc','RN_Future_Asc')
            AND S.name=@schema
            AND T.name=@table
        EXEC SP_EXECUTESQL @SQL;

        /* ADD RN COLUMNS */
        IF NOT EXISTS (SELECT 1
                        FROM sys.columns C
                        JOIN sys.tables T ON C.object_id=T.object_id
                        JOIN sys.schemas S ON T.schema_id=S.schema_id
                        WHERE C.name='RN_Current_Desc'
                        AND S.name=@schema
                        AND T.name=@table)
        BEGIN   
            SET @SQL = 'ALTER TABLE '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+'ADD RN_Current_Desc SMALLINT,
                                                                                    RN_Future_Asc SMALLINT;'
            EXEC SP_EXECUTESQL @SQL;
        END

        /* ADD INDEX TO @ID/RNS */
        SET @SQL = 'CREATE NONCLUSTERED INDEX [IX_'+@table+'_RN_Current_Desc] ON '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+' ([@ID], LEAD_CO_MNE, RN_Current_Desc ASC) WHERE (RN_Current_Desc <= '+CONVERT(VARCHAR(6),@RNCurrDepth)+');
                    CREATE NONCLUSTERED INDEX [IX_'+@table+'_RN_Future_Asc] ON '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+' ([@ID], LEAD_CO_MNE, RN_Future_Asc DESC)  WHERE (RN_Current_Desc <= '+CONVERT(VARCHAR(6),@RNFutureDepth)+');
                    CREATE NONCLUSTERED INDEX [IX_TMP_Current_Desc] ON #TMP ([@ID], LEAD_CO_MNE) INCLUDE(RNCURRENTDESC, RNFUTUREASC);
                    CREATE NONCLUSTERED INDEX [IX_TMPBATCH_Current_Desc] ON #TMP_BATCH ([@ID], LEAD_CO_MNE) INCLUDE(RNCURRENTDESC, RNFUTUREASC);';
        EXEC SP_EXECUTESQL @SQL;

        /* BATCH UPDATE RN - CURRENT/PAST ROWS */
        SET @SQL = ';WITH X AS
                    (
                    SELECT [@ID]
                          ,LEAD_CO_MNE
                          ,ROW_NUMBER() OVER(PARTITION BY ID_COMP_1, LEAD_CO_MNE ORDER BY ID_COMP_3_DATE DESC, ID_COMP_3_ID DESC) AS RNCURRENTDESC
                    FROM '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+'
                    WHERE ID_COMP_3_DATE <= MIS_DATE
                    )
                    INSERT INTO #TMP ([@ID],LEAD_CO_MNE,RNCURRENTDESC)
                    SELECT [@ID], LEAD_CO_MNE, RNCURRENTDESC
                    FROM X
                    WHERE RNCURRENTDESC <= @RNCurrDepth;
                    
                    DECLARE @BATCH INT = 25000;
                    WHILE @BATCH > 0
                    BEGIN
                        UPDATE TOP(@BATCH) X
                        SET RN_Current_Desc = T.RNCURRENTDESC
                        FROM '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+' X
                        JOIN #TMP T ON X.[@ID]=T.[@ID] AND X.LEAD_CO_MNE=T.LEAD_CO_MNE
                        WHERE X.RN_Current_Desc IS NULL;

                        SET @BATCH=@@ROWCOUNT;
                    END'
        EXEC SP_EXECUTESQL @SQL, N'@RNCurrDepth SMALLINT', @RNCurrDepth=@RNCurrDepth;

        TRUNCATE TABLE #TMP;

        /* RN - FUTURE ROWS */
        SET @SQL = ';WITH X AS
                    (
                    SELECT [@ID]
                          ,LEAD_CO_MNE
                          ,ROW_NUMBER() OVER(PARTITION BY ID_COMP_1, LEAD_CO_MNE ORDER BY ID_COMP_3_DATE ASC, ID_COMP_3_ID DESC) AS RNFUTUREASC
                    FROM '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+'
                    WHERE ID_COMP_3_DATE > MIS_DATE
                    )
                    INSERT INTO #TMP ([@ID],LEAD_CO_MNE,RNFUTUREASC)
                    SELECT [@ID],LEAD_CO_MNE,RNFUTUREASC
                    FROM X
                    WHERE RNFUTUREASC <= @RNFutureDepth

                    DECLARE @BATCH INT = 25000;
                    WHILE @BATCH > 0
                    BEGIN
                        UPDATE TOP(@BATCH) X
                        SET RN_Future_Asc = T.RNFUTUREASC
                        FROM '+QUOTENAME(@schema)+'.'+QUOTENAME(@table)+' X
                        JOIN #TMP T ON X.[@ID]=T.[@ID] AND X.LEAD_CO_MNE=T.LEAD_CO_MNE
                        WHERE X.RN_Future_Asc IS NULL;

                        SET @BATCH=@@ROWCOUNT;
                    END'
        EXEC SP_EXECUTESQL @SQL, N'@RNFutureDepth SMALLINT', @RNFutureDepth=@RNFutureDepth;

        FETCH NEXT FROM IDComp3 INTO @schema, @table, @RNCurrDepth, @RNFutureDepth;
    END
    CLOSE IDComp3
    DEALLOCATE IDComp3
END TRY
BEGIN CATCH

    DECLARE @Message NVARCHAR(MAX) = ERROR_MESSAGE(), 
            @Severity INT = ERROR_SEVERITY(), 
            @State SMALLINT =ERROR_STATE()
    RAISERROR(@Message, @Severity, @State);
    RETURN(1);
END CATCH
    
RETURN(0)

编辑 1c - 示例表架构

CREATE TABLE dbo.ABC123
(
    [LEAD_CO_MNE] [nvarchar](50) NOT NULL,
    [BRANCH_CO_MNE] [nvarchar](50) NULL,
    [MIS_DATE] [date] NOT NULL,
    [@ID] [nvarchar](150) NOT NULL,
    [ACTIVITY] [nvarchar](150) NULL,
    [ACTION] [nvarchar](max) NULL,
    [CHANGE_DATE_TYPE] [nvarchar](50) NULL,
    [CHANGE_PERIOD] [nvarchar](150) NULL,
    [CHANGE_DATE] [datetime2](7) NULL,
    [CHANGE_ACTIVITY] [nvarchar](150) NULL,
    [PRIOR_DAYS] [int] NULL,
    [CHG_TO_PRODUCT] [nvarchar](150) NULL,
    [ALLOWED_PRODUCT] [nvarchar](max) NULL,
    [RESERVED_6] [nvarchar](150) NULL,
    [RESERVED_5] [nvarchar](150) NULL,
    [INITIATION_TYPE] [nvarchar](50) NULL,
    [DEFAULT_ACTIVITY] [nvarchar](150) NULL,
    [RESERVED_4] [nvarchar](150) NULL,
    [RESERVED_3] [nvarchar](150) NULL,
    [RESERVED_2] [nvarchar](150) NULL,
    [RESERVED_1] [nvarchar](150) NULL,
    [LOCAL_REF] [nvarchar](max) NULL,
    [PR_ATTRIBUTE] [nvarchar](max) NULL,
    [PR_VALUE] [nvarchar](max) NULL,
    [PR_BRK_RES] [nvarchar](max) NULL,
    [PR_BRK_MSG] [nvarchar](max) NULL,
    [PR_BRK_CHARGE] [nvarchar](max) NULL,
    [PR_RESERVED_3] [nvarchar](max) NULL,
    [PR_RESERVED_2] [nvarchar](max) NULL,
    [PR_RESERVED_1] [nvarchar](max) NULL,
    [PR_APP_METHOD] [nvarchar](max) NULL,
    [PR_APP_PERIOD] [nvarchar](max) NULL,
    [SYS_RESERVE7] [nvarchar](150) NULL,
    [SYS_RESERVE6] [nvarchar](150) NULL,
    [SYS_RESERVE5] [nvarchar](150) NULL,
    [SYS_RESERVE4] [nvarchar](150) NULL,
    [SYS_RESERVE3] [nvarchar](150) NULL,
    [SYS_RESERVE2] [nvarchar](150) NULL,
    [SYS_RESERVE1] [nvarchar](150) NULL,
    [DEFAULT_ATTR_OPTION] [nvarchar](150) NULL,
    [DEFAULT_NEGOTIABLE] [nvarchar](50) NULL,
    [NR_ATTRIBUTE] [nvarchar](max) NULL,
    [NR_OPTIONS] [nvarchar](max) NULL,
    [NR_RESERVED2] [nvarchar](max) NULL,
    [NR_RESERVED1] [nvarchar](max) NULL,
    [NR_STD_COMP] [nvarchar](max) NULL,
    [NR_TYPE] [nvarchar](max) NULL,
    [NR_VALUE] [nvarchar](max) NULL,
    [NR_MESSAGE] [nvarchar](max) NULL,
    [CHANGED_FIELDS] [nvarchar](max) NULL,
    [NEGOTIATED_FLDS] [nvarchar](max) NULL,
    [ID_COMP_1] [nvarchar](150) NULL,
    [ID_COMP_2] [nvarchar](150) NULL,
    [ID_COMP_3] [nvarchar](150) NULL,
    [ID_COMP_4] [nvarchar](150) NULL,
    [ID_COMP_5] [nvarchar](150) NULL,
    [ID_COMP_6] [nvarchar](150) NULL,
    [RESERVED2_ID] [nvarchar](150) NULL,
    [TARGET_PRODUCT] [nvarchar](50) NULL,
    [STMT_NOS] [nvarchar](max) NULL,
    [OVERRIDE] [nvarchar](max) NULL,
    [RECORD_STATUS] [nvarchar](50) NULL,
    [CURR_NO] [int] NULL,
    [INPUTTER] [nvarchar](max) NULL,
    [DATE_TIME] [nvarchar](max) NULL,
    [AUTHORISER] [nvarchar](150) NULL,
    [CO_CODE] [nvarchar](50) NULL,
    [DEPT_CODE] [nvarchar](50) NULL,
    [AUDITOR_CODE] [nvarchar](50) NULL,
    [AUDIT_DATE_TIME] [int] NULL,
    [ARRANGEMENT_KEY] [nvarchar](150) NULL,
    [ETL_DQ_RevisionCount] [int] NULL,
    [ETL_DQ_ColumnsRevised] [nvarchar](4000) NULL,
    [ETL_DQ_ErrorMessage] [nvarchar](4000) NULL,
    [ETL_CHANGE_PERIOD] [nvarchar](100) NULL,
    [API_ATTRIBUTE] [nvarchar](max) NULL,
    [NR_ATTRIBUTE_RULE] [nvarchar](max) NULL,
    [NR_VALUE_SOURCE] [nvarchar](max) NULL,
    [OWNING_COMPANY] [nvarchar](max) NULL,
    [ID_COMP_3_DATE]  AS (TRY_CONVERT([date],left([ID_COMP_3],(8)),(112))),
    [ID_COMP_3_ID]  AS (TRY_CAST(right([ID_COMP_3],len([ID_COMP_3])-charindex('.',[ID_COMP_3])) AS [int])),
    [RN_Current_Desc] [smallint] NULL,
    [RN_Future_Asc] [smallint] NULL,
 CONSTRAINT [PK_ABC123] PRIMARY KEY NONCLUSTERED 
(
    [@ID] ASC,
    [LEAD_CO_MNE] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
sql-server sql-server-2017
Martin Hope
Mattia Nocerino
Asked: 2022-08-11 02:18:58 +0800 CST
  • 3

我有一个查询应用程序扫描带有文本字段的整个表格。

查询执行了这么多次读取：

扫描计数 1，逻辑读取 170586，物理读取 3，预读读取 174716，lob 逻辑读取 7902578，lob 物理读取 8743，lob 预读读取 0。

具有 lob 逻辑读取的查询计划

如果我从选择中删除文本字段，则读取内容如下：

扫描计数 1，逻辑读取 170588，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

没有 lob 逻辑读取的查询计划

我不明白的是 lob 读取的工作原理：

如果我将逻辑读取与 lob 逻辑读取相加，我总共得到8.073.164 逻辑读取，如果我是正确的，大约是 64GB。

但是整个数据库只有7GB！

我可能遗漏了一些关于添加逻辑读取和 lob 逻辑读取的内容。

lob逻辑读取的数量实际上代表什么？

sql-server sql-server-2017
Martin Hope
ktakmn
Asked: 2022-08-02 09:18:52 +0800 CST
  • 1

我正在尝试使用正在设置链接服务器的服务器上的本地 Windows 主机文件在 SQL Server 2017 中设置链接服务器。这样做的原因是允许我对链接服务器名称进行硬编码，但通过更改本地主机文件来配置它指向的服务器。

我在两个数据库服务器上都使用 Windows 身份验证。我可以访问两者。

当我创建一个引用 IP 地址的链接服务器时，它可以工作。我可以连接和查询示例表。

EXEC sp_addlinkedserver @server = '192.168.1.109'
, @srvproduct = 'SQL Server'

当我将以下行添加到主机文件时，上面创建的通过 IP 地址连接的链接服务器不再工作。

192.168.1.109 主机文件测试

我收到以下错误：

Msg 18456, Level 14, State 1, Line 18
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

通常我会认为这与缺少 SPN 有关，但在这种情况下，两台服务器都设置了 SPN，如果我再次从主机文件中删除上述行，我可以连接。

此外，如果我尝试使用主机文件中的名称设置另一个链接服务器，就像这样，它也会失败，并出现同样的错误。

EXEC sp_addlinkedserver @server = 'hostsfiletest'
, @srvproduct = 'SQL Server'

我不确定我哪里出错了。

我可以从我正在设置链接服务器的服务器 ping 目标服务器，并且我可以使用 hostsfiletest 作为实例名称从同一台服务器连接到目标 SQL 实例，因此主机文件中的行显然在做某事。当我从主机文件中删除该行时，我无法使用 hostsfiletest 作为实例名称进行连接。

作为另一个测试，当我将与上面相同的行放入笔记本电脑的本地主机文件时，我能够使用 hostsfiletest 作为服务器名称连接到 SSMS 中的目标服务器。

如有任何帮助，我将不胜感激。如果有更简单的方法来设置指向可配置位置的链接服务器，我也愿意接受其他实现相同目标的方法。

sql-server sql-server-2017