主页 / dba / 问题 / 18799
Accepted
Jon of All Trades
Asked: 2012-06-05 11:59:11 +0800 CST

返回固定行数后查询暂停

  • 772

我有一个视图可以快速（几秒钟）运行多达 41 条记录（例如，TOP 41），但需要几分钟才能运行 44 条或更多记录，如果使用TOP 42or运行，则会产生中间结果TOP 43。具体来说，它将在几秒钟内返回前 39 条记录，然后在返回剩余记录之前停止近三分钟。这种模式在查询TOP 44or时是一样的TOP 100。

这个视图最初是从基础视图派生的，在基础视图中添加了一个过滤器，即下面代码中的最后一个过滤器。如果我将子视图从基础链接起来，或者我用基础内联的代码编写子视图，似乎没有区别。基本视图在几秒钟内返回 100 条记录。我想我可以让子视图像基地一样快地运行，而不是慢 50 倍。有没有人见过这种行为？关于原因或解决方案的任何猜测？

在我测试所涉及的查询时，这种行为在过去几个小时内一直保持一致，尽管在事情开始变慢之前返回的行数略有上下波动。这并不新鲜。我现在正在查看它，因为总运行时间是可以接受的（<2 分钟），但我已经看到相关日志文件中的这种暂停至少有几个月了。

阻塞

我从未见过查询被阻止，即使数据库上没有其他活动（由 sp_WhoIsActive 验证），问题仍然存在。基本视图包括NOLOCK所有内容，这是值得的。

查询

这是子视图的简化版本，为简单起见，内嵌了基本视图。它仍然在大约 40 条记录处显示运行时间的跳跃。

SELECT TOP 100 PERCENT
    Map.SalesforceAccountID AS Id,
    CAST(C.CustomerID AS NVARCHAR(255)) AS Name,
    CASE WHEN C.StreetAddress = 'Unknown' THEN '' ELSE C.StreetAddress                 END AS BillingStreet,
    CASE WHEN C.City          = 'Unknown' THEN '' ELSE SUBSTRING(C.City,        1, 40) END AS BillingCity,
                                                       SUBSTRING(C.Region,      1, 20)     AS BillingState,
    CASE WHEN C.PostalCode    = 'Unknown' THEN '' ELSE SUBSTRING(C.PostalCode,  1, 20) END AS BillingPostalCode,
    CASE WHEN C.Country       = 'Unknown' THEN '' ELSE SUBSTRING(C.Country,     1, 40) END AS BillingCountry,
    CASE WHEN C.PhoneNumber   = 'Unknown' THEN '' ELSE C.PhoneNumber                   END AS Phone,
    CASE WHEN C.FaxNumber     = 'Unknown' THEN '' ELSE C.FaxNumber                     END AS Fax,
    TransC.WebsiteAddress AS Website,
    C.AccessKey AS AccessKey__c,
    CASE WHEN dbo.ValidateEMail(C.EMailAddress) = 1 THEN C.EMailAddress END,  -- Removing this UDF does not speed things
    TransC.EmailSubscriber
    -- A couple dozen additional TransC fields
FROM
    WarehouseCustomers AS C WITH (NOLOCK)
    INNER JOIN TransactionalCustomers AS TransC WITH (NOLOCK) ON C.CustomerID = TransC.CustomerID
    LEFT JOIN  Salesforce.AccountsMap AS Map WITH (NOLOCK) ON C.CustomerID = Map.CustomerID
WHERE
        C.DateMadeObsolete IS NULL
    AND C.EmailAddress NOT LIKE '%@volusion.%'
    AND C.AccessKey IN ('C', 'R')
    AND C.CustomerID NOT IN (243566)  -- Exclude specific test records
    AND EXISTS (SELECT * FROM Orders AS O WHERE C.CustomerID = O.CustomerID AND O.OrderDate >= '2010-06-28')  -- Only count customers who've placed a recent order
    AND Map.SalesforceAccountID IS NULL  -- Only count customers not already uploaded to Salesforce
-- Removing the ORDER BY clause does not speed things up
ORDER BY
    C.CustomerID DESC

该过滤器会丢弃由;Id IS NULL返回的大部分记录。BaseView如果没有TOP子句，它们分别返回 1,100 条记录和 267K。

统计数据

运行时TOP 40：

SQL Server parse and compile time:    CPU time = 234 ms, elapsed time = 247 ms.
SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.

(40 row(s) affected)
Table 'CustomersHistory'. Scan count 2, logical reads 39112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AccountsMap'. Scan count 1, logical reads 458, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:   CPU time = 2199 ms,  elapsed time = 7644 ms.

运行时TOP 45：

(45 row(s) affected)
Table 'CustomersHistory'. Scan count 2, logical reads 98268, physical reads 1, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 1788, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AccountsMap'. Scan count 1, logical reads 2152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 41980 ms,  elapsed time = 177231 ms.

我很惊讶地看到实际输出的这种适度差异导致读取次数增加了约 3 倍。

比较执行计划，除了返回的行数之外，它们是相同的。与上面的统计数据一样，TOP 45查询中早期步骤的实际行数要高得多，而不仅仅是高出 12.5%。

概括地说，它是从 Orders 中扫描一个覆盖索引，从 WarehouseCustomers 中寻找相应的记录；将此循环连接到 TransactionalCustomers（远程查询，确切计划未知）；并将其与 AccountsMap 的表扫描合并。远程查询是估计成本的 94%。

杂项说明

早些时候，当我将视图的扩展内容作为独立查询执行时，它运行得非常快：100 条记录需要 13 秒。我现在正在测试一个没有子查询的查询的精简版本，这个更简单的查询需要三分钟才能返回超过 40 行，即使作为独立查询运行也是如此。

子视图包含大量读取（每个 sp_WhoIsActive 约 1M），但在这台机器上（8 个内核，32 GB RAM，95% 专用 SQL 框）通常不是问题。

我已经多次删除并重新创建了这两个视图，没有任何变化。

数据不包括任何 TEXT 或 BLOB 字段。一个领域涉及UDF；删除它不会阻止暂停。

无论是在服务器本身上查询，还是在 1,400 英里外的工作站上查询，时间都是相似的，因此延迟似乎是查询本身固有的，而不是向客户端发送结果。

sql-server-2008

4 个回答

  1. Best Answer

    一些事情要尝试：

    1. 检查您的索引

      • 是否所有JOIN关键字段都已编入索引？如果您经常使用此视图，我什至会为视图中的条件添加过滤索引。例如...

      • CREATE INDEX ix_CustomerId ON WarehouseCustomers(CustomerId, EmailAddress) WHERE DateMadeObsolete IS NULL AND AccessKey IN ('C', 'R') AND CustomerID NOT IN (243566)

    2. 更新统计

      • 过时的统计数据可能存在问题。如果你可以摆动它，我会做一个FULLSCAN。如果有大量行，则数据可能发生了显着变化而没有触发自动重新计算。

    3. 清理查询

      • 制作Map JOINa NOT EXISTS- 您不需要该表中的任何数据，因为您只需要不匹配的记录

      • 删除ORDER BY. 我知道评论说没关系，但我觉得这很难相信。由于数据页已被缓存，因此对于较小的结果集可能无关紧要。

    • 4

  2. 问题是远程查询。如果您将该表带到本地，您将永远不会看到问题。为了解释更多，如果您的数量少于一定数量，远程查询引擎将远程推送连接值。如果超过该限制，它将在本地提取整个表（这是慢速位），然后执行连接。通过更改为 EXISTS 子句，您将在所有其他连接之后加入远程查询，因此连接到远程表所需的值更少，并且可以远程推送它们。

    但是，如果您从本地表返回更多行，您仍然会遇到同样的问题。

    我过去解决该问题的一种方法是将查询的本地部分的结果返回到临时表中。然后从临时表中选择远程连接值到 XML 变量中，然后作为 NVARCHAR 参数传递给我编写的远程存储过程。远程存储过程接受参数并转换回 XML，然后是临时表。它将它与远程表连接起来以取回行。然后将它们加入到本地临时表的另一个查询中以获得最终结果。稍微复杂一点，但是当您只需要几百行数千万行的表时，避免每次都进行大量下载是非常值得的。查询从 20 分钟缩短到 13 秒。

    • 2

  3. 改进 1 删除 Orders 的 SubQuery 并将其转换为 join

    FROM
WarehouseCustomers AS C WITH (NOLOCK)
INNER JOIN TransactionalCustomers AS TransC WITH (NOLOCK) 
                                                        ON C.CustomerID = TransC.CustomerID
LEFT JOIN  Salesforce.AccountsMap AS Map WITH (NOLOCK) 
                                                        ON C.CustomerID = Map.CustomerID
INNER Join Orders AS O 
                                                        ON C.CustomerID = O.CustomerID

 WHERE
    C.DateMadeObsolete IS NULL
    AND C.EmailAddress NOT LIKE '%@volusion.%'
    AND C.AccessKey IN ('C', 'R')
    AND C.CustomerID NOT IN (243566)
    AND O.OrderDate >= '2010-06-28'
    AND Map.SalesforceAccountID IS NULL

    改进 2 - 将 TransactionalCustomers 过滤记录保留在本地临时表中

    Select 
    CAST(C.CustomerID AS NVARCHAR(255)) AS Name,
    CASE WHEN C.StreetAddress = 'Unknown' THEN '' ELSE C.StreetAddress                 END AS BillingStreet,
    CASE WHEN C.City          = 'Unknown' THEN '' ELSE SUBSTRING(C.City,        1, 40) END AS BillingCity,
                                                       SUBSTRING(C.Region,      1, 20)     AS BillingState,
    CASE WHEN C.PostalCode    = 'Unknown' THEN '' ELSE SUBSTRING(C.PostalCode,  1, 20) END AS BillingPostalCode,
    CASE WHEN C.Country       = 'Unknown' THEN '' ELSE SUBSTRING(C.Country,     1, 40) END AS BillingCountry,
    CASE WHEN C.PhoneNumber   = 'Unknown' THEN '' ELSE C.PhoneNumber                   END AS Phone,
    CASE WHEN C.FaxNumber     = 'Unknown' THEN '' ELSE C.FaxNumber                     END AS Fax,
    C.AccessKey AS AccessKey__c
Into #Temp
From  WarehouseCustomers C
Where C.DateMadeObsolete IS NULL
        AND C.EmailAddress NOT LIKE '%@volusion.%'
        AND C.AccessKey IN ('C', 'R')
        AND C.CustomerID NOT IN (243566)

    最终查询

    FROM
#Temp AS C WITH (NOLOCK)
INNER JOIN TransactionalCustomers AS TransC WITH (NOLOCK) 
                                                            ON C.CustomerID = TransC.CustomerID
LEFT JOIN Salesforce.AccountsMap AS Map WITH (NOLOCK) 
                                                            ON C.CustomerID = Map.CustomerID
INNER Join Orders AS O 
                                                            ON C.CustomerID = O.CustomerID

WHERE
C.DateMadeObsolete IS NULL
AND C.EmailAddress NOT LIKE '%@volusion.%'
AND C.AccessKey IN ('C', 'R')
AND C.CustomerID NOT IN (243566)
AND O.OrderDate >= '2010-06-28'
AND Map.SalesforceAccountID IS NULL

    第 3 点 - 我假设您在 CustomerID、EmailAddress、OrderDate 上有索引

    • 1

  4. 关于作者最初留在问题正文中的解决方案的注释：

    解决方法很简单：用一个子句替换LEFT JOINto Map 。NOT EXISTS这只会导致查询计划中的一个微小差异，即在加入 Map 表之后而不是之前加入 TransactionCustomers 表（远程查询）。这可能意味着它只从远程服务器请求所需的记录，这将减少大约 100 倍的传输量。

    通常我是第一个欢呼的人NOT EXISTS；它通常比LEFT JOIN...WHERE ID IS NULL构造更快，并且更紧凑。在这种情况下，这很尴尬，因为问题查询是建立在现有视图上的，并且虽然反连接所需的字段由基本视图公开，但它首先从整数转换为文本。因此，为了获得良好的性能，我必须放弃两层模式，而是拥有两个几乎相同的视图，第二个视图包括NOT EXISTS子句。

    感谢大家帮助解决此问题！它可能对我的情况太具体了，无法对其他人有所帮助，但希望不会。如果不出意外，这是一个NOT EXISTS比LEFT JOIN...WHERE ID IS NULL. 但真正的教训可能是确保尽可能高效地连接远程查询；查询计划声称它代表 2% 的成本，但它并不总是准确估计。

    • 1

相关问题