比较新的SQL Azure的层，以旧 [英] Comparing the new SQL Azure tiers to old ones

问题描述

现在，微软提供了新的SQL Azure的服务层（基本，标准，premium），我们试图找出他们是如何映射到现有的（Web和企业）。

Now that Microsoft made the new SQL Azure service tiers available (Basic, Standard, Premium) we are trying to figure out how they map to the existing ones (Web and Business).

基本上，有在新的层击穿提升绩效级别：基础，S1，S2，P1，P2和P3（细节这里：<一href=\"http://msdn.microsoft.com/library/dn741336.aspx\">http://msdn.microsoft.com/library/dn741336.aspx)

Essentially, there are six performance levels in the new tier breakdown: Basic, S1, S2, P1, P2 and P3 (details here: http://msdn.microsoft.com/library/dn741336.aspx)

有谁知道旧的数据库层是如何映射到这些六个级别？举例来说，企业是一个相当于S1的？ S2？

Does anyone know how the old database tiers map to those six levels? For instance, is Business equivalent of an S1? S2?

我们需要能够回答，为了这个问题要弄清楚什么服务层/水平，移植现有数据库。

We need to be able to answer this question in order to figure out what service tiers/levels to migrate our existing databases to.

推荐答案

我们刚刚完成了性能比较。

We just finished a performance comparison.

我不能发布我们的SQL查询，但我们使用符合我们正常的活动，3个不同的测试用例。在每个测试的情况下，我们对几千行执行多个查询与表连接和聚集计算（SUM，AVG等）。我们的测试数据库是温和的 - 大约5GB大小有几百万行。

I can't publish our SQL queries, but we used 3 different test cases that match our normal activity. In each test case, we performed several queries with table joins and aggregate calculations (SUM, AVG, etc) for a few thousand rows. Our test database is modest - about 5GB in size with a few million rows.

的几个注意事项：对于每一个，我们测试了我的本地机，这是一个5岁的iMac运行的是Windows / SQL虚拟机服务器（本地），SQL Azure的业务（业务），SQL Azure的premium P1，SQL Azure的标准S2和SQL Azure的标准S1。基本的层次显得那么慢，我们没有测试它。所有这些测试用在系统上没有其他活动完成。因此网络性能是希望不是一个因素查询未返回数据。

A few notes: For each, we tested the my local machine which is a 5 year old iMac running Windows/SQL Server in a virtual machine ("Local"), SQL Azure Business ("Business"), SQL Azure Premium P1, SQL Azure Standard S2, and SQL Azure Standard S1. The basic tier seemed so slow that we didn't test it. All of these tests were done with no other activity on the system. The queries did not return data so network performance was hopefully not a factor.

下面是我们的结果：

测试一个

Local: 1 second
Business: 2 seconds
P1: 2 seconds
S2: 4 seconds
S1: 14 seconds

测试两个

Local: 2 seconds
Business: 5 seconds
P1: 5 seconds
S2: 10 seconds
S1: 30 seconds

测试三

Local: 5 seconds
Business: 12 seconds
P1: 13 seconds
S2: 25 seconds
S1: 77 seconds

结论：

与几天不同层次的工作后，我们的团队总结出几件事情：

After working with the different tiers for a few days, our team concluded a few things:

• P1出现在同一级别的SQL Azure的商业演出。 （P1是10倍的价格）


• 基本与S1是什么，但一个启动数据库的方式过于缓慢。


• 业务层是一个共享服务，使性能取决于其他用户在服务器上的内容。我们的数据库显示，4.01％的CPU，0.77％数据IO，0.14％，日志IO的最大值，我们正在经历重大的性能问题和超时。 Microsoft支持证实了我们是只是一个非常繁忙的服务器上。


• 业务层提供跨服务器和地区不一致的服务。在我们的例子中，我们在不同的区域移动到不同的服务器，我们的服务恢复正常。 （我们查看作为临时解决方案）


• S1，S2，P1层似乎提供跨区域相同的性能。我们测试了西部和北部中央。


• 考虑到上述结果，我们一般是担心SQL Azure中的未来。业务层已经好几年了很大的帮助我们，但它计划走出去服务于12个月。新的层次似乎比相比，业务层的价格。

我敢肯定，有100种，这可能是比较科学的，但我希望这些统计数据帮助他人准备评估。

I'm sure there are 100 ways this could be more scientific, but I'm hoping those stats help others getting ready to evaluate.

更新：

Microsoft支持给我们送来一个非常有用的查询来评估你的数据库使用。

Microsoft Support sent us a very helpful query to assess your database usage.

SELECT
       avg(avg_cpu_percent) AS 'Average CPU Percentage Used',
       max(avg_cpu_percent) AS 'Maximum CPU Percentage Used',
       avg(avg_physical_data_read_percent) AS 'Average Physical IOPS Percentage',
       max(avg_physical_data_read_percent) AS 'Maximum Physical IOPS Percentage',
                   avg(avg_log_write_percent) AS 'Average Log Write Percentage',
       max(avg_log_write_percent) AS 'Maximum Log Write Percentage',
       --avg(avg_memory_percent) AS 'Average Memory Used Percentage',
       --max(avg_memory_percent) AS 'Maximum Memory Used Percentage',
       avg(active_worker_count) AS 'Average # of Workers',
       max(active_worker_count) AS 'Maximum # of Workers'
FROM sys.resource_stats
WHERE database_name = 'YOUR_DATABASE_NAME' AND
start_time > DATEADD(day, -7, GETDATE())

最有用的部分是百分比的重新S2实例的present％。根据微软的支持，如果你在100％，您使用的是S2的100％，200％，就相当于一个P1实例。

The most useful part is that the percentages represent % of an S2 instance. According to Microsoft Support, if you're at 100%, you're using 100% of an S2, 200% would be equivalent to a P1 instance.

我们遇到了非常好的运气P1实例现在，虽然价格差异一直是令人震惊的。

We're having very good luck with P1 instances now, although the price difference has been a shocker.

这篇关于比较新的SQL Azure的层，以旧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！