在进行性能测试时如何避免SQL Server的“重建统计”？

I'm doing some SQL tuning these days and find one weird sql during the test:

这些天我正在做一些SQL调优，并在测试期间找到一个奇怪的SQL：

SELECT StatMan([SC0],[SC1], [SB0000]) 
FROM (SELECT TOP 100 PERCENT [SC0],[SC1], step_direction([SC0]) over (order by NULL) AS [SB0000]  
      FROM (SELECT [tableA] AS [SC0],[tableB] AS [SC1] 
            FROM [dbo].[url] WITH (READUNCOMMITTED,SAMPLE 3.408654e+000 PERCENT) 
           ) AS _MS_UPDSTATS_TBL_HELPER 
     ORDER BY [SC0],[SC1], [SB0000] 
    ) AS _MS_UPDSTATS_TBL  
OPTION (MAXDOP 1)

Looks this is doing some "reindex" or "rebuild" some db index according to SQL Server. But my question is how can we avoid this during the long load test besides "reindex" for each tables before the testing.

看起来这是根据SQL Server做一些“reindex”或“重建”一些db索引。但我的问题是，除了测试之前每个表的“reindex”之外，我们如何在长负载测试期间避免这种情况。

And this SQL will consume 16862ms because of my table contains enough rows. And there are many insert action in my test.

由于我的表包含足够的行，因此该SQL将消耗16862ms。在我的测试中有很多插入动作。

2 个解决方案

#1

This seems to be from updating statistics.

这似乎来自更新统计数据。

Will updating statistics happen in a normal production environment? If so, shouldn't a load test, to reflect a production environment, update statistics as well?

更新统计信息是否会在正常生产环境中发生？如果是这样，不应该进行负载测试，以反映生产环境，更新统计数据？

To turn off the AUTO_UPDATE_STATISTICS option, use sp_autostats on the desired table(s) (see http://msdn.microsoft.com/en-us/library/ms188775.aspx ).

要关闭AUTO_UPDATE_STATISTICS选项，请在所需的表上使用sp_autostats（请参阅http://msdn.microsoft.com/en-us/library/ms188775.aspx）。

2 个解决方案

#1

更多相关文章

随机推荐