logo标签列表

Azure SQL数据库导致存储过程连接超时。

sqlsql-serverazurestored-proceduresazure-sql-database
3
我们已将数据库托管在Azure上，并在此数据库上运行存储过程。存储过程一直运行良好，但突然从上周开始出现连接超时错误。
我们的数据库大小为14 GB，通常返回2k到20k条记录的存储过程使用Azure DB的S3定价层（50 DTU）。
有趣的是，第一次执行存储过程需要很长时间，大约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

这是执行计划：

执行计划

请提出您认为需要改进的建议吗？我们是否需要升级定价层？

对于一个14GB的数据库，理想的Azure定价层是什么？

3个回答
1
该查询在您的Windows 10 8Gb RAM机器上应该只需要1到3秒钟完成。但是由于SQL Server选择了一个糟糕的执行计划，所以需要15秒钟才能完成。在这种情况下，糟糕的执行计划的根本原因是错误的估算，计划中的几个操作符显示出估计行数和实际行数之间的巨大差异。例如，SQL Server估计只需要对pk_customer聚集索引执行一次查找，但它实际上执行了16,522次查找。相同的情况也发生在[ConversationHistory] .[IX_ConversationID_CreatedOn_Activity_ByWhom]和[Message].[IX_ConversationID_ID_ArrivalDt_From_RStatus_Type]上。
以下是一些提示，可供您遵循以提高查询性能：
  • 更新统计信息
  • 在查询末尾尝试使用OPTION (HASH JOIN)。它可能会提高性能，也可能会降低性能，甚至会导致查询出错。
  • 将表变量数据存储在临时表中，并在查询中使用它们。（SELECT * INTO #temp_table FROM @table_variable）。表变量没有统计信息，会导致估算不准确。
  • 确定估计行数和实际行数之间差异很大的第一个运算符。拆分查询。查询1: SELECT * INTO #operator_result FROM (query equivalent to operator)。查询2：使用#operator_result编写查询。因为#operator_result是一个临时表，SQL Server被强制重新评估估算。在这种情况下，有问题的运算符是哈希匹配（内部连接）。

还有其他方法可以改善此查询的性能：

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

DROP INDEX [IX_MailboxID] ON [dbo].[Conversation] GO CREATE INDEX IX_MailboxID ON [dbo].[Conversation](MailboxID) INCLUDE (ArrivalDate, Status, ClosureDate, CustomerID, ConversationType)

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

改为:

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

use:

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))

And this:

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))
@Saurabh，我编辑了我的答案，加上了覆盖索引。 - Jesús López
嗨，感谢您的快速回复。我创建了这个索引。CREATE NONCLUSTERED INDEX [Conv_MailboID_ConvType_CustID] ON [dbo].[Conversation] ( [MailboxID] ASC, [ConversationType] ASC, [CustomerID] ASC ) INCLUDE ( [Status], [ArrivalDate], [ClosureDate])现在在本地运行只需要1秒钟。我已经在索引中包含了所有那些我在'where'中使用的字段以及我在'select'中使用的字段都被包含在'include'部分作为覆盖。 - Saurabh
客户表中有29227条记录。消息表中有168120条记录，对话历史表中有51981条记录。有哪些改进措施可以采取？ - Saurabh
@Saurabh，我在考虑强制使用哈希匹配内连接，但我担心这些数字太大了。 - Jesús López
谢谢回复 :) 玩得开心，抱歉打扰了。有时间的话，请查看此链接：https://dba.stackexchange.com/questions/213259/need-help-improving-the-stored-procedure-performance/213261?noredirect=1#comment418254_213261 - Saurabh
显示剩余12条评论
0
哦，谢谢。 :) 这太低了。我会探索 DTU 计算器并尽快回复。 - Saurabh
此外，“标准”层和“高级”层都是按DTU评分的，但高级DTU具有固态存储。 - ta.speot.is
0

这个星期我也遇到了同样的问题，最终用户声称使用连接到 Azure 中托管的 VM 的应用程序速度很慢。此外，我的 VM 几乎相同（4 个 CPU、14GB RAM 和 S3，但 DTU 为 100）。

在我的情况下，我有很多索引的 avg_fragmentation_in_percent 大于 30，这导致执行存储过程时性能不佳。

在 SSMS 中运行此命令，如果您正在运行存储过程的表的索引存在，则可能需要处理：

SELECT dbschemas.[name] as 'Schema',
              dbtables.[name] as 'Table',
              dbindexes.[name] as 'Index',
              indexstats.avg_fragmentation_in_percent,
              indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
WHERE indexstats.database_id = DB_ID()
AND indexstats.index_id = dbindexes.index_id
AND indexstats.avg_fragmentation_in_percent >30
--AND dbindexes.[name] like '%CLUSTER%'
ORDER BY indexstats.avg_fragmentation_in_percent DESC

更多信息请点击这里。

编辑：

还要检查统计数据的年龄：

SELECT 
        sys.objects.name AS table_name,
        sys.indexes.name as index_name, 
        sys.indexes.type_desc as index_type, 
        stats_date(sys.indexes.object_id,sys.indexes.index_id) 
        as last_update_stats_date,
        DATEDIFF(d,stats_date(sys.indexes.object_id,sys.indexes.index_id),getdate()) 
        as stats_age_in_days
FROM  
        sys.indexes
        INNER JOIN sys.objects on sys.indexes.object_id=sys.objects.object_id
WHERE 
        sys.objects.type = 'U' 
        AND
        sys.indexes.index_id > 0 
        --AND sys.indexes.name  Like  '%CLUSTER%'
ORDER BY 
        stats_age_in_days DESC;
GO
谢谢您的回复，但我已经重建了我的索引。 - Saurabh
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接