主页 / dba / 问题 / 323002
Accepted
Thomas
Asked: 2023-02-03 19:38:56 +0800 CST

大量“键值”连接导致 SQL Server 查询速度呈指数级下降

  • 772

我正在从使用对象 ID 和属性类型作为聚集索引的属性表中迁移一些存储为“键值”样式的数据（我也尝试过作为非聚集索引）：

CREATE TABLE [dbo].[#attrs](
    [DataMigrationEventObjectID] [int] NOT NULL,
    [AttributeType] [varchar](128) NOT NULL,
    [AttributeValue] [varchar](255) NULL
) 
CREATE CLUSTERED INDEX pk ON #attrs ([DataMigrationEventObjectID],AttributeType);

我添加了属性值来选择值，因为数据库中的属性表有很多其他数据，我可以只为这个迁移事件选择它。使用我的测试数据集填充此表的查询插入 ~3k 行并在不到一秒的时间内运行（我的数据集中总共有大约 50 个对象，每个对象都有几个属性）。

查询中表的连接如下所示，连接到聚簇索引：

        INNER JOIN #attrs obj_gvn
        ON obj_gvn.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
        AND obj_gvn.AttributeType = 'GivenName'

通过 14 个连接到这个临时表，查询在几秒钟内完成。对于 15 个连接，查询需要一分钟，对于 16+ 个连接，它在半小时后仍在运行。

我已经检查了所有连接是否存在会导致返回太多行的意外情况，当它在 1 分钟内返回时它只返回正确的行，所以我认为没有意外的笛卡尔连接。设置 MAXDOP 值不会影响它，查询运行一分钟后返回的查询计划不会标记任何问题。

关于 SQL，我错过了什么导致它以这种方式运行的聚集索引上的大量连接，理论上应该很快，记录数量如此之少？

我无法获得实际执行计划，因为查询未完成，并且因为它使用了临时表，所以我无法获得它的估计计划。我试图将临时表伪造为数据库中的真实表并生成一个估计计划，但 2 分钟后该计划仍未生成，所以看起来延迟是在“创建计划”方面

粘贴查询的缩短版本的计划：brentozar.com/pastetheplan/ ?id=Hy76dd92i

为了以防万一，我已经更新了数据库的统计信息，但它仍然没有生成计划。

我过去曾处理过越来越多的问题连接查询，其中计划编译仍然是即时的。我觉得它在“生成计划”步骤中失败的事实一定意味着什么。

不幸的是，更新到最新的 CU 没有帮助。sp_whoisactive仅显示 CPU 使用率攀升和攀升（屏幕截图），其他资源上没有任何问题。

这是我的开发机器，因此 SQL 上只有一个进程处于活动状态，这是我正在运行的查询。没有别的，所以我假设它必须是 SQL 试图生成计划。

我怀疑如果我部署它，Production 会很好地运行它，但是“在主键上有很多连接会导致性能下降”的问题是非常奇怪的。我可能会考虑干脆放弃开发服务器并从头开始。

sql-server

2 个回答

  1. 一般来说

    具有大量连接的查询的编译时间可能变化很大。

    原因之一是有 N！对 N 个表的内部连接进行排序的方法。对于小 N，这不是问题。对于较大的 N，这可能是一个问题，即使优化器不会尝试对可能的计划空间进行详尽搜索。

    您可能已经注意到，您提供的执行计划不会按照您编写它们的相同顺序访问表。例如，首先访问表DataMigrationEventObject （别名为“obj”）。没有明显的可用单一索引，因此优化器创建了一个索引交集（使用两个非聚集索引）和一个键查找（获取列TargetObjectKey1）来物理实现查询的那一小部分。

    优化器也不限于考虑不同的连接顺序。它可能探索不同的连接算法（散列、合并、嵌套循环、应用）、不同的索引策略、非连接运算符的位置和类型、并行性等。

    优化器具有启发式方法，如果迄今为止找到的最佳计划是合理的，则可以避免花费太多时间寻找更好的计划。给定一个相当简单的查询、一个体面的数据库逻辑设计和一个合适的物理实现（例如数据类型和索引），优化器通常会很快找到一个好的低成本计划。这似乎是你过去的经历。

    这种安排仍然可能在多个方面出错。如果优化器选择的初始连接顺序距离合理的低成本还有很长的路要走，则需要完成大量转换工作才能到达终点。如果数据库没有很好的支持索引，优化器可能会花费大量时间来寻找合适的策略。

    如果行计数和值分布信息无法准确导出（不限于过时的基表统计信息），计划片段候选者的成本计算可能不正确。这很容易导致启发式阈值被突破，并且优化器花费过多的精力探索、实施和计算替代方案的成本。

    发生这种情况时，有时唯一的解决方案是使用不同的语法、不同的算法（如枢轴建议）或将操作分解为更简单的独立步骤，并使用小型且索引良好的临时表作为保存领域。

    一般来说，您的问题似乎很可能是特定的数据分布导致早期成本计算不准确和优化器工作量过大。

    具体来说

    您的查询和数据库有多个问题，使它们容易受到计划编译时间过长的影响。我不打算列出所有这些，因为没有提供完整的模式，并且在提供的执行计划中找到的查询文本在关键点被截断了。即使提供了所有必要的信息，答案也会太长。几点：

    • 基表上的索引并不理想（请参阅上面关于索引交集和查找的评论）
    • 临时表上标记为 pk（对于主键？）的聚集索引#attrs未标记为UNIQUE。根据定义，键是唯一的。不向优化器提供这一关键保证意味着它无法知道对象 ID 和类型上的相等谓词最多会返回一行。还有其他后果，太多无法一一列举，但举个例子，合并连接必须是多对多的，而不是一对多的。这些事情对于勘探和成本核算很重要。
    • 该查询使用围绕“int_att”别名的嵌套连接语法。这可能无法避免，也可能无法避免，但它会使作为优化器输入生成的初始逻辑树更加复杂。不必要的复杂性很容易导致次优处理。
    • 该#attrs表有一个名为“clus”的重复索引。问题中提到了这一点，但这是向优化器提供的非显而易见的索引选择的示例，没有任何收益。使聚簇索引UNIQUE成为一个合适的键。
    • 加载表的查询#attrs有一个重复的谓词DataMigrationEventID = @MigrationEventID。这是无害的，但在与计算机打交道时注意细节很重要（它们非常字面意思）。
    • 数据库停留在兼容级别 130 (SQL Server 2016)。升级到最新的 2019 CU 一切都很好，但兼容性级别覆盖了许多改进。如果您还没有为数据库启用优化器修复，您也应该考虑将其打开。
    • #theClients使用SELECT INTO然后立即返回该表的所有结果来创建临时表没有任何好处（只有成本） 。如果使用得当，临时表是一个强大的调优工具。这不是此类用法的示例。

    复现

    对于任何想要在本地生成计划的人，我能够从问题和执行计划中推断出以下内容。可能会有一些遗漏和不准确之处。自然没有准确的统计数据，只是一个原始的表基数：

    表

    CREATE TABLE dbo.DataMigrationEventObject
(
    DataMigrationEventObjectID integer NOT NULL PRIMARY KEY,
    DataMigrationEventID integer NOT NULL,
    DataMigrationObjectType varchar(255) NOT NULL,
    TargetObjectKey1 integer NOT NULL,
    TargetObjectKey2 integer NOT NULL,
    TargetObjectKey3 integer NOT NULL,
    IsCompleted bit NOT NULL,
    IsDeleted bit NOT NULL,

    INDEX DataMigrationEventID (DataMigrationEventID),
    INDEX DataMigrationObjectType (DataMigrationObjectType)
);

UPDATE STATISTICS dbo.DataMigrationEventObject WITH ROWCOUNT = 8413, PAGECOUNT = 841;

CREATE TABLE dbo.DataMigrationEventObjectAttribute
(
    DataMigrationEventObjectID integer NOT NULL PRIMARY KEY,
    DataMigrationEventID integer NOT NULL,
    AttributeType varchar(128) NOT NULL,
    AttributeValue sql_variant NOT NULL,
    IsDeleted bit NOT NULL,
);

UPDATE STATISTICS dbo.DataMigrationEventObjectAttribute WITH ROWCOUNT = 56900, PAGECOUNT = 56900;

CREATE TABLE dbo.#attrs
(
    DataMigrationEventObjectID integer NOT NULL,
    AttributeType varchar(128) NOT NULL,
    AttributeValue varchar(255) NULL
);

CREATE CLUSTERED INDEX pk ON #attrs (DataMigrationEventObjectID, AttributeType);

DECLARE @MigrationEventID integer = 23;

INSERT INTO #attrs
SELECT 
    dmeo.DataMigrationEventObjectID,
    a.AttributeType,
    CAST(a.AttributeValue as varchar(255)) AttributeValue  
FROM DataMigrationEventObject dmeo
JOIN DataMigrationEventObjectAttribute a
    ON a.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
WHERE 
    dmeo.DataMigrationEventID = @MigrationEventID
    AND dmeo.DataMigrationObjectType IN ('BpClient','xxxxxxxClientMapping')
    AND dmeo.DataMigrationEventID = @MigrationEventID;

CREATE INDEX clus ON #attrs (DataMigrationEventObjectID, AttributeType);

UPDATE STATISTICS #attrs WITH ROWCOUNT = 2173, PAGECOUNT = 21;

    询问

    DECLARE @MigrationEventID integer = 23;

SELECT 
    dmeo.DataMigrationEventObjectID xxObjectID,
    a_clientid.AttributeValue ClientID,
    a_tnt.AttributeValue ClientExternalID,
    a_tnt.AttributeValue TenantID,
    int_att.AttributeValue ClientInternalID,
    obj_gvn.AttributeValue GivenName,
    obj_oth.AttributeValue OtherName,
    obj_fam.AttributeValue FamilyName,
    obj_pref.AttributeValue PreferredName,
    obj_title.AttributeValue Title,
    obj_dob.AttributeValue DateOfBirth,
    obj_email.AttributeValue Email,
    obj_hphone.AttributeValue HomePhone,
    obj_wphone.AttributeValue WorkPhone,
    obj_add1.AttributeValue Address1,
    obj_add2.AttributeValue Address2,
    obj_sub.AttributeValue Suburb,
    obj_state.AttributeValue StateID,
    obj_post.AttributeValue Postcode,
    obj_sex.AttributeValue SexID
    /*,
    obj_del.AttributeValue IsDeleted,
    obj_inact.AttributeValue IsInactive,
    obj_pref_corr.AttributeValue PreferredCorrespondenceMethodID,
    obj_eth.AttributeValue EthnicityID
    obj_em_title.AttributeValue EmergencyContactTitle,
    obj_em_surn.AttributeValue EmergencyContactSurname,
    obj_em_gvn.AttributeValue EmergencyContactGivenName,
    obj_em_add.AttributeValue EmergencyContactAddress,
    obj_em_sub.AttributeValue EmergencyContactSuburb,
    obj_em_post.AttributeValue EmergencyContactPostcode,
    obj_em_phone.AttributeValue EmergencyContactPhone,
    obj_em_phone2.AttributeValue EmergencyContactPhone2,
    obj_em_rel.AttributeValue EmergencyContactRelationship,
    obj_nok_title.AttributeValue NextOfKinContactTitle,
    obj_nok_surn.AttributeValue NextOfKinContactSurname,
    obj_nok_gvn.AttributeValue NextOfKinContactGivenName,
    obj_nok_add.AttributeValue NextOfKinContactAddress,
    obj_nok_sub.AttributeValue NextOfKinContactSuburb,
    obj_nok_post.AttributeValue NextOfKinContactPostcode,
    obj_nok_phone.AttributeValue NextOfKinContactPhone,
    obj_nok_phone2.AttributeValue NextOfKinContactPhone2,
    obj_nok_rel.AttributeValue NextOfKinContactRelationship*/
INTO #theClients
FROM DataMigrationEventObject dmeo 
  INNER JOIN #attrs a_clientid 
  ON a_clientid.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
  AND a_clientid.AttributeType = 'ClientID'
  INNER JOIN #attrs a_clientextid 
  ON a_clientextid.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
  AND a_clientextid.AttributeType = 'ClientExternalID'
  INNER JOIN #attrs a_tnt 
  ON a_tnt.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
  AND a_tnt.AttributeType = 'TenantID'
  INNER JOIN (
      #attrs int_att
      INNER JOIN DataMigrationEventObject obj 
      ON obj.DataMigrationEventObjectID = int_att.DataMigrationEventObjectID
      AND obj.DataMigrationObjectType = 'BpClient'
     ) 
  ON int_att.AttributeType = 'InternalID'
  AND obj.DataMigrationEventID = dmeo.DataMigrationEventID
  AND obj.TargetObjectKey1 = cast(a_clientextid.AttributeValue as int)  
      INNER JOIN #attrs obj_gvn 
      ON obj_gvn.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_gvn.AttributeType = 'GivenName'
      INNER JOIN #attrs obj_oth 
      ON obj_oth.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_oth.AttributeType = 'OtherName'
      INNER JOIN #attrs obj_fam 
      ON obj_fam.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_fam.AttributeType = 'FamilyName'
      INNER JOIN #attrs obj_pref 
      ON obj_pref.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_pref.AttributeType = 'PreferredName'
      INNER JOIN #attrs obj_title 
      ON obj_title.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_title.AttributeType = 'Title'
      INNER JOIN #attrs obj_dob 
      ON obj_dob.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_dob.AttributeType = 'DateOfBirth'
      INNER JOIN #attrs obj_email 
      ON obj_email.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_email.AttributeType = 'Email'
      INNER JOIN #attrs obj_hphone 
      ON obj_hphone.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_hphone.AttributeType = 'HomePhone'
      INNER JOIN #attrs obj_wphone 
      ON obj_wphone.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_wphone.AttributeType = 'WorkPhone'
      INNER JOIN #attrs obj_add1 
      ON obj_add1.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_add1.AttributeType = 'Address1'
      INNER JOIN #attrs obj_add2 
      ON obj_add2.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_add2.AttributeType = 'Address2'
      INNER JOIN #attrs obj_sub 
      ON obj_sub.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_sub.AttributeType = 'Suburb'
      INNER JOIN #attrs obj_state 
      ON obj_state.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_state.AttributeType = 'StateID'
      INNER JOIN #attrs obj_post 
      ON obj_post.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_post.AttributeType = 'Postcode'
      INNER JOIN #attrs obj_sex 
      ON obj_sex.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_sex.AttributeType = 'SexID'
      --INNER JOIN #attrs obj_del 
      --ON obj_del.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      --AND obj_del.AttributeType = 'IsDeleted'
      --INNER JOIN #attrs obj_inact
      --ON obj_inact.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      --AND obj_inact.AttributeType = 'IsInactive'
      --INNER JOIN #attrs obj_pref_corr
      --ON obj_pref_corr.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      --AND obj_pref_corr.AttributeType = 'PreferredCorrespondenceMethodID'
      --INNER JOIN #attrs obj_eth
      --ON obj_eth.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      --AND obj_eth.AttributeType = 'EthnicityID'
WHERE 
    dmeo.DataMigrationEventID = @MigrationEventID
    AND dmeo.IsDeleted = 0
    AND dmeo.DataMigrationObjectType = 'xxxxxxxClientMapping';

    该演示不会在我的兼容级别 130 下的 SQL Server 2019 实例上重现过多的编译时间，但考虑到缺乏准确的统计数据以及可能不同的硬件和实例配置（主要是内存）所有这些都会影响计划选择，这并不意外。

    使用原始基数模型可以重现更长的编译时间（和一个相当疯狂的计划）。将以下提示添加到最终查询：

    OPTION (MAXDOP 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

    我已经使用过MAXDOP 1，因为您的实例是这样配置的。

    • 6
  2. Best Answer

    我不确定这是否能解决您的问题，但您是否尝试过旋转 #attrs 字典然后加入它？我发现有时采取不同的策略至少可以让它发挥作用。像下面这样的东西。

    ;WITH CTE_PVT AS
    (
    SELECT DataMigrationEventObjectID
        , ClientID 
        , ClientExternalID
        , TenantID
        , GivenName
        , OtherName
        , FamilyName
        , PreferredName
        , Title
    FROM [#attrs] AS A
        PIVOT (MAX([AttributeValue]) FOR [AttributeType] IN (ClientID, ClientExternalID, TenantID, GivenName
                                                                , OtherName, FamilyName, PreferredName, Title)) AS pvt 
    )
SELECT dmeo.[DataMigrationEventObjectID]
    , P.ClientID 
    , P.ClientExternalID
    , P.TenantID
    , P.GivenName
    , P.OtherName
    , P.FamilyName
    , P.PreferredName
    , P.Title
FROM DataMigrationEventObject dmeo
    LEFT OUTER JOIN CTE_PVT AS P ON P.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
    • 2

相关问题