同一 SQL 查询在一个数据库中运行的时间比在同一服务器下的另一个数据库中运行的时间长 [英] The Same SQL Query takes longer to run in one DB than another DB under the same server

问题描述

我有一个 SQL 数据库服务器和 2 个具有相同结构和数据的数据库.我在 2 个数据库中运行相同的 sql 查询，其中一个需要更长的时间，而另一个在不到 50% 的时间内完成.他们都有不同的执行计划.

I have a SQL database server and 2 databases under it with the same structure and data. I run the same sql query in the 2 databases, one of them takes longer while the other completes in less than 50% of the time. They both have different execution plans.

视图的查询如下:

SELECT DISTINCT  i.SmtIssuer, i.SecID, ra.AssetNameCurrency AS AssetIdCurrency, i.IssuerCurrency, seg.ProxyCurrency, shifts.ScenarioDate, ten.TenorID, ten.Tenor, 
                      shifts.Shift, shifts.BusinessDate, shifts.ScenarioNum
FROM         dbo.tblRrmIssuer AS i INNER JOIN
                      dbo.tblRrmSegment AS seg ON i.Identifier = seg.Identifier AND i.SegmentID = seg.SegmentID INNER JOIN
                      dbo.tblRrmAsset AS ra ON seg.AssetID = ra.AssetID INNER JOIN
                      dbo.tblRrmHistSimShift AS shifts ON seg.Identifier = shifts.Identifier AND i.SegmentID = shifts.SegmentID INNER JOIN
                      dbo.tblRrmTenor AS ten ON shifts.TenorID = ten.TenorID INNER JOIN
                      dbo.tblAsset AS a ON i.SmtIssuer = a.SmtIssuer INNER JOIN
                      dbo.tblRrmSource AS sc ON seg.SourceID = sc.SourceID
WHERE     (a.AssetTypeID = 0) AND (sc.SourceName = 'CsVaR') AND (shifts.SourceID =
                          (SELECT     SourceID
                            FROM          dbo.tblRrmSource
                            WHERE      (SourceName = 'CsVaR')))

我已经尝试过的事情是 - 重建 &重新组织表上的索引(tblRRMHistSimShifts - 该表有超过 200 万条记录)，检查服务器上的锁或其他后台进程或错误，服务器的最大并行度为 0.

The things i have already tried are - rebuild & reorganize index on the table (tblRRMHistSimShifts - this table has over 2 million records), checked for locks or other background processes or errors on server, Max degree of parallelism for the server is 0.

您还有什么建议可以解决这个问题吗?

Is there anything more you can suggest to fix this issue?

推荐答案

事实上，您在同一台服务器上有两个数据库并且具有相同的数据集(如您所说)并不能确保相同的执行计划.

The fact that you have two databases on same server and with same data set (as you said) does not ensure same execution plan.

以下是查询计划可能不同的一些原因:

Here are some of the reasons why the query plan may be different:

• mdf 和 ldf 文件(针对每个数据库)位于不同的驱动器上.如果一个驱动器速度更快，该数据库也会更快地运行查询.
• 统计数据停滞.如果您有一个数据库的统计数据比另一方面，SQL 有更好的机会选择合适的(并且
  更快)执行计划.
• 索引:我知道你说它们是相同的，但我会检查如果您在两者上都有相同类型的索引.

专注于查看查询运行缓慢的原因或查看实际执行计划，而不是进行比较.检查慢查询的实际执行计划会给你一个运行缓慢的提示.

Focus on see why the query is running slow or see the actual execution plan, instead of comparing. Checking the actual execution plan for the slow query will give you a hint of why is running slower.

此外，我不会添加 NO LOCK 语句来解决该问题.根据我的经验，大多数慢查询都可以通过代码或索引进行调整，而不是添加 NO LOCK 提示，这可能会使您修改或旧结果集，具体取决于您的事务.

Also, I would not add a NO LOCK statement to fix the issue. In my experience, most slow queries can be tuned up via code or Index, instead of adding a NO LOCK hint that may get you modified or old result sets, depending of your transactions.

这篇关于同一 SQL 查询在一个数据库中运行的时间比在同一服务器下的另一个数据库中运行的时间长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！