SQL Server 2014:具有相同 VM 的不同性能(巨大的“执行次数") [英] SQL server 2014: different performances (huge "number of executions") with same VMs

问题描述

我有 2 个相同的 VM(16 个 vCPU，RAM:64GB)，具有相同的数据库、相同的表和视图以及相同的行数.View1 有 470 万行.

在 VM1 (UAT) 上，SELECT TOP 1000 .. FROM View1 在不到 1 秒内回答.

在 VM2 (PROD) 上，相同的查询在 4 分钟内得到答复.

我检查了数据库、表、视图 1 的属性，但没有发现 VM1 和 VM2 之间有任何区别.我查过统计数据，但它显示没有什么需要刷新的.

VM2 上的执行计划显示:

• 估计执行次数:1000
• 执行次数:420 万

在 VM1 上:

• 估计执行次数:1000
• 执行次数:900

应该与 VM1 (UAT) 完全相同的 VM2 (PROD) 会出现什么问题?

以下是两个 VM 上SELECT TOP 1000 .. FROM View1"的执行计划:

什么可以解释两种环境之间逻辑读取(和预读)数量的巨大差异???

提前，感谢帮助我理解这个谜团".

解决方案

尽管是两个配置相同的不同虚拟机，但也有很多不同之处:

1. 您是否检查了两台服务器的 IOPS 数量?

<块引用>

在选择前 1000 名期间 10 MB/s ... 来自 View1

2. 即使两个 RAM 相同，它也分配给 SQL Server 多少?

<块引用>

为两个 VM 上的SQL Server 2014"分配 60GB/64GB

3. 数据加载模式:数据加载如何发生以及索引如何重建/重新组织?时间表是什么?

<块引用>

没有 DBA在手"，我希望他下周回来

4. 统计信息更新:即使您重建了索引，两个服务器中的统计信息是否都是最新的?

<块引用>

请问，如何强制更新统计信息?在两个虚拟机上，最后一次更新是1.5 个月前！但它说统计数据是最新的...

5. 网络测试的工作原理是什么?如果您从远程机器查询，是否存在任何连接问题?

<块引用>

测试是通过终端服务器"和SQL"在 VM1 和 VM2 上完成的Server Management Studio".在此期间网络活动非常低在VM2上测试

6. 在您不知情的情况下，在同一张表上有任何额外的触发器、约束吗?

<块引用>

理论上不是，但……也许吧.我会和 DBA 一起看看他什么时候回来了.

第 3 点和第 4 点对于估计和实际执行次数及其差异至关重要.

<块引用>

关于 View1 的附加信息:它使用 2 个表 - view0:4.7M 行，没有性能问题(选择前 1000 <1 秒.)- table2:3 行

在 VM2 上测试 View1 时，100% 使用了 1 个 CPU.

有没有比较2个数据库的quickly参数的工具?组件(表、视图、索引...)?

感谢您的帮助！:)

对于第 3 点:您可以执行以下脚本:

SELECT a.index_id, name, avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(N'YourDatabase'), OBJECT_ID(N'YourTable'), NULL, NULL, NULL) AS aJOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

<块引用>

关于 UAT 的结果 &生产

结果 UAT &生产

按照以下百分比重建或重组索引:

avg_fragmentation_in_percent 值纠正声明

<块引用>

5% 和 <= 30% 改变指数重组30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

https://msdn.microsoft.com/en-us/library/ms189858.aspx

对于第 4 点:

您可以运行以下脚本:

更新统计信息 yourtablename

这将更新您的统计数据.之后，您可以使用

检查统计直方图

dbcc show_statistics(yourtablename, yourindexname)

它在两台服务器上均匀分布，使查询优化器选择最佳计划.

考虑到两个执行计划是相似的.如果您发布两个执行计划，我们可以找到确切的区别.

I have 2 identical VM (16 vCPU,RAM:64GB) with the same database, same tables and views and same number of lines. View1 has 4.7M lines.

On VM1 (UAT) a SELECT TOP 1000 .. FROM View1 answers in less than 1 s.

On VM2 (PROD) the same query answers in 4 minutes.

I've checked properties of database, tables, view1 and didn't find any difference between VM1 and VM2. I've checked statistics but it shows that nothing has to be refeshed.

The plan of execution on VM2 shows:

• estimated number of executions : 1000
• number of executions : 4.2M

On VM1:

• estimated number of executions : 1000
• number of executions : 900

What could be the problem with VM2 (PROD) that is supposed to be exactly the same than VM1 (UAT)?

Here are the execution plans of "SELECT TOP 1000 .. FROM View1" on both VM :

Execution plans on both VM: UAT and PROD

They seem to be very similar.

Here are UAT vs PROD statistics :

What can explain the huge difference of numbers of logical reads (and read-ahead reads) between the 2 environments ???

By advance, thanks to help me to understand this "mystery".

解决方案

Eventhough it is two different VM's with same configuration it has lots of differences:

1. Did you checked the number of IOPS in both the server?

10 MB/s during select top 1000 ... from View1

2. Eventhough both the RAM are same how much it is allocated to SQL Server?

60GB/64GB allocated for "SQL Server 2014" on both VM

3. Data load patterns : How data load happens and how Indexes are rebuilt/re-organized? What is the schedule for that?

No DBA "on hand", I hope he'll be back next week

4. Statistics updates : Eventhough you have indexes rebuilt, does the statistics upto date in both the server?

Please, how to force statistics update ? On both VM, last update was 1.5 months ago ! But it says statistics are up to date...

5. How network test works? Is there any connectivity issues if you are querying from remote machine?

The tests are done on VM1 and VM2 via "Terminal server" with "SQL Server Management Studio". Network activity is very low during the test on VM2

6. Any extra triggers, constraints on the same table without your knowledge?

Theoretically not but ... perhaps. I'll see with the DBA when he'll be back.

Points 3 and 4 are vital for estimated and actual number of executions and their differences.

Additional info about View1: it uses 2 tables - view0 : 4.7M rows, no problem of performance (select top 1000 < 1 s.) - table2 : 3 rows

During the test of View1 on VM2, 100% of 1 CPU is used.

Is there a tool to compare quiclky parameters of 2 databases and its components (tables, views, indexes...) ?

Thanks for your help ! :)

For point 3: You can execute below script:

SELECT a.index_id, name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDatabase'), OBJECT_ID(N'YourTable'), NULL, NULL, NULL) AS a  
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;  

Results on UAT & PROD

Results UAT & PROD

Do Index rebuild or Reorganize as per below percentages:

avg_fragmentation_in_percent value Corrective statement

5% and < = 30% ALTER INDEX REORGANIZE 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

https://msdn.microsoft.com/en-us/library/ms189858.aspx

For point 4:

You can run below script:

update statistics yourtablename

Which will update your statistics. After that you can check the statistics histogram by using

dbcc show_statistics(yourtablename, yourindexname)

It has even distribution in both servers which make Query optimizer to select optimal plan.

Considering that both the execution plans are similar. If you post both the execution plans we can find the exact difference.

这篇关于SQL Server 2014:具有相同 VM 的不同性能(巨大的“执行次数")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！