帮助解决不合理的性能问题 [英] Help Troubleshooting Unreasonably Poor Performance

问题描述

Hello All，

Hello All,

我试图理解为什么我的Azure SQL数据库性能看起来如此难以想象。我刚刚开始评估该平台，当我运行一些存储过程时，我首先注意到它是为了在两个表中创建历史记录。 I
然后将所有内容删除到一些简单的SQL查询，并试图在几个不同的服务中比较苹果和苹果。

I'm trying to understand why my Azure SQL Database performance seems so unimaginably poor. I'm just starting to evaluate the platform, and I noticed it first when I was running some stored procedures I wrote to create history records across two tables. I then stripped everything down to some simple SQL queries and tried to compare apples to apples across a few different services.

测试很简单 - 我复制了一张大桌子（ 2.3M行，25个相对较短的列）。执行'alter table'以添加单个字符字段，然后执行更新查询以将该字段的所有实例设置为相同的字符。这模仿了我们今天使用的流程，将记录标记为"当前"以准备变更捕获流程。 

The test is simple - I copy up a large table (2.3M rows, 25 relatively short columns). Execute an 'alter table' to add a single character field, then execute a update query to set all instances of that field to the same character. This mimics a process we use today to flag records as 'current' in preparation for a change capture process. 

我们的流程正在其他地方生产，我们是希望迁移到Azure。我们今天使用PostgreSQL进行开发和测试，但似乎转向Azure SQL将是一个合理的选择。 

The process we have is in production elsewhere and we are looking to migrate to Azure. We use PostgreSQL today for development and test, but it seemed like moving to Azure SQL would be a logical choice. 

当我针对PostgreSQL实例的Azure数据库执行此操作时（通用） gen 5 2核心），查询一直需要20-25秒才能完成。我测试了几次并验证了结果;这也大致与我在当前的PostgreSQL开发环境中获得
的时间相匹配。

When I do this against an Azure Database for PostgreSQL instance (general purpose gen 5 2 core), the query consistently takes between 20-25 seconds to complete. I tested several times and validated the results; this also roughly matches the timings I get in my current PostgreSQL development environment.

当我在Azure SQL（标准S2,50DTU）中进行完全相同的测试时，最快的时间还是超过18分钟。怎么可能呢？我将它升级为S4：200 DTU进行测试，使实例现在的价格是Azure PostgreSQL实例的两倍，而
的性能仍然比微弱的PostgreSQL实例慢得多。我尝试重建实例并开始完全清理，我可以保证实例上没有来自任何其他进程的争用。 

When I do the exact same test in Azure SQL (Standard S2, 50DTUs), the fastest time was still over 18 minutes. How can that be possible? I upped it to a S4:200 DTUs to test, making the instance now twice the price of that the Azure PostgreSQL instance, and the performance is still substantially slower than the meager PostgreSQL instance. I tried rebuilding the instance and starting totally clean, and I can guarantee there is no contention on the instance from any other process. 

我尝试了一系列其他简单查询，复制表，性能一直很差，并且比Azure PostgreSQL实例慢很多因素。我还尝试使用其他表的集合来尝试排除这个特定表的
结构的奇怪之处。 Azure SQL确实表现得非常好的一点是表的批量加载，并且选择查询的速度可以接受，但是任何需要数据更新的东西都太慢而无法使其无法使用。

I tried a series of other simple queries, copying tables, and performance was consistently poor and many factors slower than the Azure PostgreSQL instance. I also tried with a collection of other tables to try and rule out it being something odd about the structure of this one specific table. One thing where Azure SQL does seems to perform quite well is in the bulk load of the table, and select queries are acceptably fast, but anything requiring data updates is so slow as to render it unusable.

因为我无法弄清楚如何将DTU与PostgreSQL大小进行比较，我确信这不完全是苹果，我不得不相信定价这两个实例在同一个资源的同一个地方。透视。 

Since I can't figure out how to compare DTUs to the PostgreSQL sizing, I'm sure this is not totally apples to apples, I have to believe based on pricing that the two instances are somewhere in the same ballpark from a resource perspective. 

感谢您阅读所有这些内容，如果您对我可能想要考虑的内容有任何指导，我将不胜感激。现在，我没有必要继续评估Azure SQL，如果它要么执行得不好和/或我需要支付
，比我现在做的要多得多，以使性能达到可接受的水平。

Thanks for reading all this, and if you have any guidance for what I might want to consider, I'd appreciate it. As it stands there's no point in me continuing to evaluate Azure SQL if it's either going to perform this poorly and/or I'm going to need to pay many times more what I do now to get performance to an acceptable level.

推荐答案

测试很简单 - 我复制了一个大表（2.3M行，25个相对较短的列）。执行'alter table'以添加单个字符字段，然后执行更新查询以将该字段的所有实例设置为相同的字符。这模仿了我们今天用b $ b用于将记录标记为"当前"以准备变更捕获过程的过程。 

The test is simple - I copy up a large table (2.3M rows, 25 relatively short columns). Execute an 'alter table' to add a single character field, then execute a update query to set all instances of that field to the same character. This mimics a process we use today to flag records as 'current' in preparation for a change capture process. 

考虑添加以下内容，而不是添加列并进行更新具有默认约束的列。这是使用Azure SQL数据库的仅限元数据的操作，并且将避免更新每一行的成本。

Rather than add the column and update afterwards, consider adding the column with a default constraint. This is a meta-data only operation with Azure SQL Database and will avoid the cost of updating every row.

--meta-data only operation
ALTER TABLE dbo.Test
	ADD NewColumn char(1) NOT NULL 
	CONSTRAINT DF_Test_NewColumn DEFAULT('A');

--if you don't need the default constrant
ALTER TABLE dbo.Test
	DROP CONSTRAINT CONSTRAINT DF_Test_NewColumn;

这篇关于帮助解决不合理的性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！