AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • 主页
  • 系统&网络
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding

Mobile menu

Close
  • 主页
  • 系统&网络
    • 最新
    • 热门
    • 标签
  • Ubuntu
    • 最新
    • 热门
    • 标签
  • Unix
    • 最新
    • 标签
  • DBA
    • 最新
    • 标签
  • Computer
    • 最新
    • 标签
  • Coding
    • 最新
    • 标签
主页 / dba / 问题

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

Martin Hope
SlowMagic
Asked: 2023-02-27 01:07:31 +0800 CST

带有 UNION ALL 的 CTE 未按预期工作

  • 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
  • 3 个回答
  • 193 Views
Martin Hope
Muflix
Asked: 2022-10-17 08:31:23 +0800 CST

如何在 SSDT 部署中将 CLR 程序集注册为受信任

  • 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
  • 1 个回答
  • 241 Views
Martin Hope
Manan Adhvaryu
Asked: 2022-10-06 07:28:46 +0800 CST

SQL Server Standard 安装错误:登录失败。登录来自不受信任的域,不能与集成身份验证一起使用

  • 1
这个问题是从 Stack Overflow迁移过来的,因为它可以在 Database Administrators Stack Exchange 上得到回答。 14 天前迁移 。

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

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

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

错误图片

sql-server sql-server-2017
  • 1 个回答
  • 80 Views
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
  • 1 个回答
  • 95 Views
Martin Hope
Dave C
Asked: 2022-08-17 18:44:57 +0800 CST

随机错误 824 - 所有环境

  • 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
  • 2 个回答
  • 221 Views
Martin Hope
Mattia Nocerino
Asked: 2022-08-11 02:18:58 +0800 CST

逻辑读取和 LOB 逻辑读取

  • 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
  • 2 个回答
  • 215 Views
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
  • 1 个回答
  • 44 Views
Martin Hope
John Eisbrener
Asked: 2022-06-23 08:58:55 +0800 CST

遏制浪费的内存授予 - MSSQL 2017

  • 3

我正在运行支持供应商软件的最新补丁/CU 级别的本地 SQL Server 2017 Enterprise,这将在稍后变得重要。

服务器统计:

  • 虚拟机(内存已固定)
  • Windows Server 2016 数据中心
  • 16 个虚拟 CPU
  • 192 GB RAM(179 GB 分配给 SQL Server)
  • 30 GB 页面文件(我宁愿这个不存在,但这不是我赢得的战斗)
  • 托管 5 个用户数据库,其中 1 个大小约为 4TB
  • 供应商要求的服务器默认值为 1max degree of parallelism

如上所述,该系统支持流行的供应商平台,虽然供应商在自定义索引或其他透明数据模型调整方面做得很好,但他们往往不赞成需要调整其应用程序代码的功能更改(原因很明显,因为这会影响成千上万的客户)。

我遇到的情况是这个供应商应用程序运行长时间运行的row mode查询,这些查询将在其应用程序代码中逐项处理的项目列表排队。如果我们允许这些查询可以(并且确实)运行数天甚至数周(ASYNC_NETWORK_IO如预期的那样显示等待)。当这些查询被启动时,他们请求的数量与他们真正需要SerialDesiredMemory的数量相比是巨大的。SerialRequiredMemory结果是MEMORYCLERK_SQLQERESERVATIONS内存管理员中的请求/授予的内存远远超过了使用的内存。例如:

-- What amount of query execution memory is asked for and used
SELECT    SUM(granted_memory_kb) / 1024 AS granted_memory_mb
        , SUM(requested_memory_kb) / 1024 AS requested_memory_mb
        , SUM(used_memory_kb) / 1024 AS used_memory_mb
        , (SUM(granted_memory_kb) - SUM(used_memory_kb)) / 1024 AS excess_memory_grant_mb
FROM    sys.dm_exec_query_memory_grants
OPTION (RECOMPILE)

回报:

在此处输入图像描述

这绝对是可怕的,这是在启用资源池RESOURCE GOVERNOR并将REQUEST_MAX_MEMORY_GRANT_PERCENT这些查询降至% 之后,尽管根据我所看到5的情况,我可能会一直将其降低到SQL 2017 的最小值。1

REQUEST_MAX_MEMORY_GRANT_PERCENT我的问题是,当我被困在 SQL 2017 时,除了进一步将资源池的价值降低到1%之外,我还能做些什么来限制查询执行计划中浪费的 RAM 数量?

由于这是一个供应商应用程序,因此查询计划指南可能是一种选择,但调整MAX_GRANT_PERCENT并没有给我带来任何好处,而不仅仅是调整REQUEST_MAX_MEMORY_GRANT_PERCENT资源池中的值。我可以在计划指南中做些什么来强制batch mode执行,希望这会触发 SQL 2017 中提供的批处理模式内存反馈功能?同样,由于这是我正在使用的供应商应用程序代码,因此我无法更改查询,因此标准的汤姆愚弄方法可能在这里不起作用。

升级到 SQL 2019(或者最好是 SQL 2022)是显而易见的答案,因为它使我可以访问在这种情况下可以使用的许多功能,例如行模式内存反馈和浮点值REQUEST_MAX_MEMORY_GRANT_PERCENT,但是还有其他可用的选项吗使用 SQL 2017 我还没有接触过?如果没有,那很好,我只是想用尽我还没有想到的任何出色的选择。

sql-server sql-server-2017
  • 1 个回答
  • 68 Views
Martin Hope
ahmed barbary
Asked: 2022-06-09 16:58:41 +0800 CST

根据特征名称和 Partid 将 %S 替换为特征字符串上的特征值

  • 0

我需要编写一个 select 语句,将列%S中的字符串替换为FeatureString存储在同一表中给定的实际值PartID。

使用以下示例数据:

+--------+--------------+---------------+------------------------+
| PartID |  FeatureName |  FeatureValue |      FeatureString     |
+--------+--------------+---------------+------------------------+
|   1211 | AC           | 5V            | AC(%S)Boil(%S)Temp(%S) |
|   1211 | Boil         | 10v           | AC(%S)Boil(%S)Temp(%S) |
|   1211 | Temp         | 5V            | AC(%S)Boil(%S)Temp(%S) |
+--------+--------------+---------------+------------------------+

我想检索以下内容FeatureValueString:

+--------+-------------------------+
| PartID |       FeatureName       |
+--------+-------------------------+
|   1211 | AC(5V)Boil(10v)Temp(5V) |
+--------+-------------------------+

解释

我需要用存储在相应列组合中的值替换%S部分。FeatureStringFeatureNameFeatureValue

最后一个PartID带有值7791的情况有点特殊,因为它只需要存储在表中的两个值。这些是AC和Boil。返回的值Temp不需要FeatureString。

样本数据

  create table #partsfeature
 (
 PartId int,
 FeatureName varchar(300),
 FeatureValue varchar(300),
 FeatureString varchar(300)
 )
  insert into #partsfeature(PartId,FeatureName,FeatureValue,FeatureString)
  values
  (1211,'AC','5V','AC(%S)Boil(%S)Temp(%S)'),
  (1211,'Boil','10v','AC(%S)Boil(%S)Temp(%S)'),
  (1211,'Temp','5V','AC(%S)Boil(%S)Temp(%S)'),
  (2421,'grail','51V','Alc(%S)Coil(%S)grail(%S)'),
  (2421,'Coil','9V','Alc(%S)Coil(%S)grail(%S)'),
  (2421,'Alc','5V','Alc(%S)Coil(%S)grail(%S)'),
  (6211,'compress','33v','compress(%S)heat(%S)push(%S)'),
  (6211,'heat','90v','compress(%S)heat(%S)push(%S)'),
  (6211,'push','80v','compress(%S)heat(%S)push(%S)'),

  (5442,'compress','33v','compress(%S)heat()push(%S)'),
  (5442,'heat','90v','compress(%S)heat()push(%S)'),
  (5442,'push','80v','compress(%S)heat()push(%S)'),

  (7791,'AC','5V','AC(%S)Boil(%S)'),
  (7791,'Boil','10v','AC(%S)Boil(%S)'),
  (7791,'Temp','5V','AC(%S)Boil(%S)'),


  (8321,'Angit','50V','Angit(%S)Fan(%S)Hot(%S),Wether(%S)'),
  (8321,'Fan','9v','Angit(%S)Fan(%S)Hot(%S),Wether(%S)'),
  (8321,'Hot','3V','Angit(%S)Fan(%S)Hot(%S),Wether(%S)'),
  (8321,'Wether','12V','Angit(%S)Fan(%S)Hot(%S),Wether(%S)')

预期结果截图

用特征值替换 %S

可以有三个以上的特征。

sql-server sql-server-2017
  • 1 个回答
  • 57 Views
Martin Hope
Aleksey Vitsko
Asked: 2022-06-07 05:55:28 +0800 CST

SQL Server 的内存要求

  • 7

我们有一台 256 GB RAM 的机器。
SQL Server 的最大服务器内存设置为 180 GB。

在 180 GB 中,SQL Server 通常使用:

  • 数据库缓存内存 - ~ 140-150 GB
  • 计划缓存 - ~ 10 GB
  • 被盗服务器内存:~ 30 GB
  • 可用内存:~ 9 GB
  • 授予的工作区内存:这通常很低,峰值可以是 0.5-1 GB

缓冲区缓存命中率 - 一直徘徊在 99.9% 以上。
页面预期寿命是相当高的数字。

数据库数据文件的总大小 - 650 GB。
数据增长率约为每天 500-1500 MB(但是!旧数据每 6-8 个月被删除一次,所以基本上数据文件的增长速度要慢得多)。

问题

需要将 SQL Server 迁移到另一台计算机。面向 SQL Server 2022 推出时。

它是混合 OLTP 和 OLAP 类型的工作负载,许多应用程序使用相同的数据库;大部分 RAM 被缓存的数据库页面使用,这意味着 SQL Server 不必一直从磁盘读取它。

感觉新机器 128 GB 就足够了,最大服务器内存设置为 ~ 110 GB,为操作系统留下 18-13 GB。

目标服务器将位于 Azure VM 中,您无法在其中独立扩展 vCPU 和 RAM。与 128 GB 机器相比,具有 256 GB RAM 的机器具有两倍的内核数,从而导致相当大的成本差异。我认为与最初预计的相比降低成本,从长远来看可能对我有好处,所以我认为值得探索。在 Azure 中,您可以根据需要随时进行扩展。

如果你是我,你将如何科学地向你的经理证明,将内存减半不会降低 SQL Server 的性能,不会破坏缓冲区缓存命中率或类似的东西?

我知道对于 DBA,考虑到工作负载不会改变,将这台机器缩小到 128 GB 看起来很安全。但是,根据您的经验,您将如何说服经理呢?

sql-server sql-server-2017
  • 1 个回答
  • 1214 Views

Sidebar

Stats

  • 问题 200806
  • 回答 265436
  • 最佳答案 132718
  • 用户 66935
  • 热门
  • 回答
  • Marko Smith

    连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目

    • 12 个回答
  • Marko Smith

    如何让sqlplus的输出出现在一行中?

    • 3 个回答
  • Marko Smith

    选择具有最大日期或最晚日期的日期

    • 3 个回答
  • Marko Smith

    如何列出 PostgreSQL 中的所有模式?

    • 4 个回答
  • Marko Smith

    列出指定表的所有列

    • 5 个回答
  • Marko Smith

    如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

    • 4 个回答
  • Marko Smith

    你如何mysqldump特定的表?

    • 4 个回答
  • Marko Smith

    使用 psql 列出数据库权限

    • 10 个回答
  • Marko Smith

    如何从 PostgreSQL 中的选择查询中将值插入表中?

    • 4 个回答
  • Marko Smith

    如何使用 psql 列出所有数据库和表?

    • 7 个回答
  • Martin Hope
    Jin 连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane 如何列出 PostgreSQL 中的所有模式? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh 为什么事务日志不断增长或空间不足? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland 列出指定表的所有列 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney MySQL 能否合理地对数十亿行执行查询? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx 如何监控大型 .sql 文件的导入进度? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison 你如何mysqldump特定的表? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 对 SQL 查询进行计时? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas 如何从 PostgreSQL 中的选择查询中将值插入表中? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 列出所有数据库和表? 2011-02-18 00:45:49 +0800 CST

热门标签

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • 主页
  • 问题
    • 最新
    • 热门
  • 标签
  • 帮助

Footer

AskOverflow.Dev

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve