sql - Azure SQL DB 导致存储过程连接超时

Question

我们已在 Azure 中托管数据库，并在此数据库上运行存储过程。直到上周，存储过程一直运行良好，但突然开始出现连接超时错误。

我们的数据库大小为 14 GB，存储过程通常返回 2k 到 20k 条记录，并且我们使用S3 定价层 (50 DTU)< Azure DB 的/b>。

我发现有趣的是第一次执行存储过程时，需要花费 2 - 3 分钟的大量时间，这导致了超时。后面的执行很快(也许它缓存了执行计划)。

此外，当我在配置为 8GB RAM 的 Win10 计算机上运行具有相同记录数的同一数据库时，它会在 15 秒内运行。

这是我的存储过程:

CREATE PROCEDURE [dbo].[PRSP]   
    @CompanyID INT, 
    @fromDate DATETIME, 
    @toDate DATETIME, 
    @ListMailboxId as MailboxIds Readonly, 
    @ListConversationType as ConversationTypes Readonly
AS
BEGIN       
    SET NOCOUNT ON;    

    SELECT 
        C.ID,
        C.MailboxID,
        C.Status,
        C.CustomerID,
        Cust.FName,
        Cust.LName,
        C.ArrivalDate as ConversationArrivalDate,
        C.[ClosureDate],
        C.[ConversationType],
        M.[From],
        M.ArrivalDate as MessageArrivalDate,
        M.ID as MessageID
    FROM  
        [Conversation] as C
    INNER JOIN
        [ConversationHistory] AS CHis ON (CHis.ConversationID  = C.ID)
    INNER JOIN
        [Message] AS M ON (M.ConversationID = C.ID)
    INNER JOIN
        [Mailbox] AS Mb ON (Mb.ID = C.MailboxID)
    INNER JOIN
        [Customer] AS Cust ON (Cust.ID = C.CustomerID)
    JOIN
        @ListConversationType AS convType ON convType.ID = C.[ConversationType]
    JOIN
        @ListMailboxId AS mailboxIds ON mailboxIds.ID = Mb.ID
    WHERE
        Mb.CompanyID = @CompanyID
        AND ((CHis.CreatedOn > @fromDate
             AND CHis.CreatedOn < @toDate
             AND CHis.Activity = 1
             AND CHis.TagData = '3')
         OR (M.ArrivalDate > @fromDate
             AND M.ArrivalDate < @toDate)) 
END

这是执行计划:

Execution Plan

请就需要哪些改进提出您的建议？我们还需要升级我的定价等级吗？

理想情况下，14GB 数据库的 Azure 定价层应该是多少？

Answer 1

在 Windows 10 8Gb RAM 计算机上，该查询应需要 1 到 3 秒才能完成。需要 15 秒，因为 SQL Server 选择了较差的执行计划。在这种情况下，糟糕的执行计划的根本原因是错误的估计，计划中的几个运算符显示估计行和实际行之间存在很大差异。例如，SQL Server 估计只需要对 pk_customer 聚集索引执行一次查找，但实际上执行了 16,522 次查找。 [ConversationHistory].[IX_ConversationID_CreatedOn_Activity_ByWhom] 和 [Message].[IX_ConversationID_ID_ArrivalDt_From_RStatus_Type] 也会发生同样的情况。

这里有一些提示，您可以遵循这些提示来提高查询的性能:

• 更新统计数据
• 在查询末尾尝试OPTION (HASH JOIN)。它可能会改善性能，否则可能会降低速度，甚至可能导致查询错误。
• 将表变量数据存储在临时表中并在查询中使用它们。 (从 @table_variable 中选择 * INTO #temp_table)。表变量没有导致错误估计的统计数据。
• 确定估计行与实际行之间的差异足够大的第一个运算符。拆分查询。查询1:SELECT * INTO #operator_result FROM(相当于运算符的查询)。查询2:使用#operator_result编写查询。由于#operator_result 是时态表，SQL Server 被迫重新评估估计值。在这种情况下，有问题的运算符是哈希匹配(内连接)

您还可以采取其他措施来提高此查询的性能:

• 避免关键查找。 Conversation.PK_dbo.Conversation 聚集索引中有 16,522 个关键查找。通过创建适当的覆盖索引可以避免这种情况。在这种情况下，覆盖索引如下:

DROP INDEX [IX_MailboxID] ON [dbo].[对话]
去
在 [dbo].[对话](MailboxID) 上创建索引 IX_MailboxID
INCLUDE(到达日期、状态、关闭日期、客户 ID、对话类型)

• 将 OR 谓词拆分为 UNION 或 UNION ALL。例如:

而不是:

SELECT *
FROM table
WHERE <predicate1> OR <predicate2>

用途:

SELECT *
FROM table
WHERE <predicate1>
UNION
SELECT *
FROM table
WHERE <predicate2>

有时它会提高性能。

单独应用每个提示并衡量性能。

编辑:您可以尝试以下操作，看看是否可以提高性能:

SELECT 
        C.ID,
        C.MailboxID,
        C.Status,
        C.CustomerID,
        Cust.FName,
        Cust.LName,
        C.ArrivalDate as ConversationArrivalDate,
        C.[ClosureDate],
        C.[ConversationType],
        M.[From],
        M.ArrivalDate as MessageArrivalDate,
        M.ID as MessageID
    FROM  
        @ListConversationType AS convType
        INNER JOIN (
            @ListMailboxId AS mailboxIds
            INNER JOIN
                [Mailbox] AS Mb ON (Mb.ID = mailboxIds.MailboxID)
            INNER JOIN
                [Conversation] as C
                ON C.ID = Mb.ID
        ) ON convType.ID = C.[ConversationType]
        INNER HASH JOIN
            [Customer] AS Cust ON (Cust.ID = C.CustomerID)
        INNER HASH JOIN
            [ConversationHistory] AS CHis ON (CHis.ConversationID  = C.ID)
        INNER HASH JOIN
            [Message] AS M ON (M.ConversationID = C.ID)

    WHERE
        Mb.CompanyID =  @CompanyID
        AND ((CHis.CreatedOn > @fromDate
             AND CHis.CreatedOn < @toDate
             AND CHis.Activity = 1
             AND CHis.TagData = '3')
         OR (M.ArrivalDate > @fromDate
             AND M.ArrivalDate < @toDate)) 

还有这个:

SELECT 
    C.ID,
    C.MailboxID,
    C.Status,
    C.CustomerID,
    Cust.FName,
    Cust.LName,
    C.ArrivalDate as ConversationArrivalDate,
    C.[ClosureDate],
    C.[ConversationType],
    M.[From],
    M.ArrivalDate as MessageArrivalDate,
    M.ID as MessageID
FROM  
    @ListConversationType AS convType
    INNER JOIN (
        @ListMailboxId AS mailboxIds
        INNER JOIN
            [Mailbox] AS Mb ON (Mb.ID = mailboxIds.MailboxID)
        INNER JOIN
            [Conversation] as C
            ON C.ID = Mb.ID
    ) ON convType.ID = C.[ConversationType]
    INNER MERGE JOIN
        [Customer] AS Cust ON (Cust.ID = C.CustomerID)
    INNER MERGE JOIN
        [ConversationHistory] AS CHis ON (CHis.ConversationID  = C.ID)
    INNER MERGE JOIN
        [Message] AS M ON (M.ConversationID = C.ID)

WHERE
    Mb.CompanyID =  @CompanyID
    AND ((CHis.CreatedOn > @fromDate
         AND CHis.CreatedOn < @toDate
         AND CHis.Activity = 1
         AND CHis.TagData = '3')
     OR (M.ArrivalDate > @fromDate
         AND M.ArrivalDate < @toDate))