Microsoft Azure上的SQL Server性能问题 [英] SQL Server Performance Issues on Microsoft Azure

问题描述

目前我们有一个软件（SW）应用程序，它与SQL Server数据库集成。  SW应用程序和SQL Server DB位于自己的服务器上。 
对SW应用程序执行性能检查时，应用程序检测到"插入"语句进入SQL Server时出现性能问题。 

 

屏幕截图（可根据要求提供）显示测试所需的时间，以及基准测试时间

 

插入语句的基准测试结果为15000ms。但是，测试显示系统正在采用47136ms。 
比参考值多30秒！

 

如您所见，结果为'插入声明'不好;将数据插入SQL所需的时间比预期的要长，但有明显的滞后。  当您意识到系统刚刚设置好时，这个
特别令人不安，因此没有任何用处;主要是闲着。 

软件供应商和我已对我们的数据库服务器进行了独立测试，该服务器提供了需要多长时间的信息：

•  
  创建
•  
  Insert
•  
  选择
•  
  Drop

 

附带说明的SQL脚本（可根据要求提供）

 

我创建了许多Azure服务器，其中有一个少数数据库服务器。 我已在所有数据库服务器上测试了测试脚本。 结果是
非常差！

以下是在数据库服务器上完成测试的结果

 

从上面的结果可以看出，insert语句不好。 一旦将其与另一台服务器的结果进行比较，这将变得很明显
示例

 

Live DB服务器测试：

云提供商：
Microsoft Azure

位置：
英国南部

VM大小：
标准DS11 v2（2个vcpus，14 GB内存）

操作系统： Windows Server 2016数据中心

SQL版本： Microsoft SQL Server 2016 SP2 Web SQL Server Web（64位） - 13.0.5264.1

其他软件： 无

存储帐户类型： Premium SSD。
  E ncryption：已启用

平均插入时间= 31438

结果的屏幕截图可根据要求提供

 

尽管此服务器与测试数据库服务器相比具有更高的规格，以及此服务器经过内存优化的事实，您可以看到插入速度没有真正的变化
！

 

我决定创建一个新服务器，并按照SQL指导（可根据要求提供在线链接）将数据和日志文件分开，以及其提及的其他建议的数量
。  我还决定进一步增加VM大小并选择更高的磁盘大小（1 TB）

 

 

测试1 - 数据库服务器：

云提供商：
Microsoft Azure

位置：
英国南部

VM大小：
标准D16s v3（16 vcpus，64 GB内存）

操作系统： Windows Server 2016数据中心

SQL版本： Microsoft SQL Server Web（64位）2016 - 13.0.4560.0

其他软件： 无

存储帐户类型： Premium SSD。 
加密：未启用

平均插入时间= 30302

结果的屏幕截图可根据要求提供

 

看看上面的结果，它们也不是很好。付出额外的费用来改善服务器的规格，没有任何区别！

 

Azure的最终测试，我做的是创建一个新的服务器，具有高VM规范，但安装SQL Express。这是为了确定问题是否与
的类型SQL版

 

测试2 - 数据库服务器1（SQL-Exp）：

云提供商：
Microsoft Azure

位置：
英国南部

VM大小：
标准DS13 v2（8个vcpus，56 GB内存）

操作系统： Windows Server 2016数据中心

SQL版本： Microsoft SQL Server Express（64位）2016 - 13.0.5026.0

其他软件： 无

存储帐户类型： Premium SSD。 
加密：未启用

平均插入时间= 31805

结果的屏幕截图可根据要求提供

 

当你将这些结果与其他3个数据库服务器上的结果进行比较时，你会想知道你要支付额外的费用！ 两个任何一个的
插入速度没有区别服务器，所有这些服务器都过长！ 

 

将这些结果与另一个云服务提供商或内部虚拟机的另一个服务器示例（下一页）进行比较后，您将会发现
这些结果有多糟糕。

软件供应商在其云服务提供商的平台上拥有自己的应用程序和数据库服务器
。他们为其中一个数据库服务器提供了测试结果（下图）。

 

云提供商：
Amito

VM大小：
6 vcpus，20 GB内存

操作系统： Windows Server 2016数据中心

磁盘大小和类型： 100GB HDD SAN。 
加密：无

SQL版本： Microsoft SQL Server Web（64位）2016 - 13.0.5026.0

描述： 一个有很多活动的实时生产SQL Server。

	Starttime	创建	insert	选择	drop
1	2019-05-10 12：36：30.417	28	4046	350	6

 

由于这是一个生产服务器，而不是空闲服务器（就像我们目前设置的Azure服务器一样），你会得到一些结果的波动。但是，
'插入'时间仍然比在Azure中测试的时间好10-12倍。

 

我还决定在前提下进行测试

平均插入时间= 6354

结果的屏幕截图可根据要求提供

从上面的结果可以看出，它们远远优于任何Azure服务器。

 

结论

根据发现的问题以及已经发生的测试，很明显在Azure平台上写入SQL数据库存在问题。

< p style ="margin：0px 0px 16px; line-height：normal">  

如果有人能帮我找到解决这个问题的方法，我将不胜感激

解决方案

感谢所有这些细节 - 这非常有用！我需要更多...但是看看INSERT语句以了解它是有用的它有什么用呢？

一旦我看到这个陈述，我就可以提供有关SQL级别事项的进一步指导。

但是让我们另外看看硬件方面，因为SQL问题可能与硬件有关，而你的时间差异意味着它很可能......所以我们可以获得更多信息。对于SQL硬件，我们会关注CPU，RAM和磁盘：

CPU：

虽然服务器具有大量内核，但SQL的版本将使用多个内核而不是vCPU（例如，STD SQL将只使用4个内核，因此它取决于如何将vcores呈现给操作系统。）我相信这些虚拟机将允许SQL
要全部使用它们，但是当你运行INSERT时，检查任务管理器，这样你就可以看到每个CPU的图形....它们中的任何一个最大化了吗？

你也可以看到SQL从ERRORLOG顶部使用多少CPU或运行以下命令：

  选择  cpu_count  来自  sys  。  dm_os_sys_info   

RAM：

那个版本的SQL可以使用64GB内存....但是检查是否存在针对SSMS中SQL实例的属性设置的上限。我怀疑这将被设置为默认值以外的任何其他内容（一个非常高的数字），但让我们排除它。

磁盘：

在Azure中，你获得的IOP越多，磁盘就越大。所以虽然它是Premium SSD，但你会有IOPS限制。这些磁盘有多大？再次，如果你运行查询，打开性能监视器并单击磁盘标签...查找磁盘
IO（这是否坚持某条扁平线？）以及磁盘队列 - 达到你的IOP限制将导致操作排队，因此这将清晰可见。

如果您能告诉我上述情况，那么我可以进一步了解这个。

谢谢，

马特

Currently we have a software (SW) application, which integrates with a SQL Server DB.  The SW application and SQL Server DB sit on their own servers.  When doing Performance checks on the SW application, the application has detected a performance issue with ‘Insert’ Statement into SQL Server. 

 

The screenshot (can be made available on request) shows the time it takes to do a test, but also what the benchmark times are

 

The benchmark results for 15000ms for an Insert Statement. However, the test shows the system is taking 47136ms.  Over 30 seconds more than the reference value!

 

As you can see, the results for the ‘Insert Statement’ are not good; Inserting data into SQL is taking longer than expected, with a visible lag.  This is particularly further disturbing, when you realise the system has just been setup, so therefore is not in any use; being mainly idle. 

The software vendor and I have done an independent test on our DB Servers, which provides information on how long it will take to:

•   Create
•   Insert
•   Select
•   Drop

 

SQL Script with instructions attached (can be made available upon request)

 

I have created a number of Azure servers, of which there are a few Database servers.  I have tested the test script on all of the DB servers.  The results have been very poor!

Below are results of the test done on the DB servers

 

As you can see from the results above, the insert statement are not good.  This will become apparent, once you compare it to results of another server example

 

Live DB Server Test:

Cloud Provider: Microsoft Azure

Location: UK South

VM Size: Standard DS11 v2 (2 vcpus, 14 GB memory)

OS: Windows Server 2016 Data Centre

SQL Version: Microsoft SQL Server 2016 SP2 Web SQL Server Web (64-bit) - 13.0.5264.1

Other Software: None

Storage Account Type: Premium SSD.  Encryption: Enabled

Average Insert time = 31438

A screenshot of the results can made available on request

 

Even though this server has a higher specification compared to the Test DB Server, along with the fact that this server is memory optimised, you can see no real change in the insert speeds!

 

I decided to create a new server, and follow SQL guidance (Online link available upon request) of having the data & log files separate, along with a number of other recommendations its mentions.  I also decided I would pay more increase the VM size further & choose a higher disk size (1 TB)

 

 

Test 1 - DB Server:

Cloud Provider: Microsoft Azure

Location: UK South

VM Size: Standard D16s v3 (16 vcpus, 64 GB memory)

OS: Windows Server 2016 Data Centre

SQL Version: Microsoft SQL Server Web (64-bit) 2016 - 13.0.4560.0

Other Software: None

Storage Account Type: Premium SSD.  Encryption: Not Enabled

Average Insert time = 30302

A screenshot of the results can made available on request

 

Looking at the results above, they too are not great.  Paying extra to improve the specification of the server, doesn’t make any bit of difference!

 

The final test on Azure, I did was to create a new server, with a high VM spec, but to install SQL Express. This was to determine if the issue was with the type of SQL version

 

Test 2 - DB Server 1 (SQL-Exp):

Cloud Provider: Microsoft Azure

Location: UK South

VM Size: Standard DS13 v2 (8 vcpus, 56 GB memory)

OS: Windows Server 2016 Data Centre

SQL Version: Microsoft SQL Server Express (64-bit) 2016 - 13.0.5026.0

Other Software: None

Storage Account Type: Premium SSD.  Encryption: Not Enabled

Average Insert time = 31805

A screenshot of the results can made available on request

 

When you compare these results, with the results on the other 3 DB servers, you wonder what you are paying extra for!  There is no difference in the insert speeds on either of the servers, with all of them being excessively long! 

 

Once you compare these results, to another server example (on the next page) from another Cloud provider or a VM on premise, you will realise how bad these results are.

The software vendor have their own Application & DB Servers on their cloud service providers’ platform.  They have provided test results for one of their DB Servers (below).

 

Cloud Provider: Amito

VM Size: 6 vcpus, 20 GB memory

OS: Windows Server 2016 Data Centre

Disk Size & Type: 100GB HDD SAN.  Encryption: None

SQL Version: Microsoft SQL Server Web (64-bit) 2016 - 13.0.5026.0

Description: A Live Production SQL Server with a lot of activity going on.

	Starttime	create	insert	select	drop
1	2019-05-10 12:36:30.417	28	4046	350	6

 

As this is a Production server compared to an idle server (like the Azure servers we currently have setup), you will get fluctuations on some of the results.  However, the ‘Insert’ times are still 10-12 times better than on the one tested in Azure.

 

I also decided to do a test on premise

Average Insert time = 6354

A screenshot of the results can made available on request

As you can see from the results above, they are a far superior then any of the Azure servers.

 

Conclusion

Based on the issue identified, and the tests that have taken place, it is clear there are issues writing into a SQL DB on an Azure platform.

 

I would appreciate if anyone can help me with finding a solution for this problem

解决方案

Hi,

Thanks for all of that detail - that is really useful!  I would need some more though...it would be useful to see the INSERT statement to get an idea of what it was doing if thats possible?

Once I see the statement, I can offer further guidance on SQL level things to check.

But lets also look at the hardware side as SQL issues can be hardware related and the difference in your times mean it may well be...so lets get more info on that.  For SQL hardware, we'd be looking at CPU, RAM and disk:

CPU:

Although the servers have a large number of cores, editions of SQL will use a number of cores rather than vCPU (STD SQL for example will only use 4 cores, so it depends on how the vcores are presented to the OS).  I believe those VMs will allow SQL to use them all, but when you run the INSERT, check task manager so you can see a graph for each CPU....are any of them maxing out?

You can also see how many CPUs SQL is using from the top of the ERRORLOG or by running the below:

select cpu_count from sys.dm_os_sys_info

RAM:

That edition of SQL can use 64GB RAM....but check if there is an upper limit set anyway against the properties of the SQL instance in SSMS.  I doubt this will be set as anything other than the default (a really high number) but lets rule it out.

Disk:

In Azure, you get more IOPs the bigger the disks are.  So although it is Premium SSD, you will have an IOPS limit.  How big are these disks?  Again, if you run the query, open Performance Monitor and click the disks tab...look for the disk IO (does this stick at a certain flat line?) and also the disk queue - hitting your IOPs limit will cause operations to queue and so this will be clearly visible.

If you can let me know the above then I can look further into this.

Thanks,

Matt

这篇关于Microsoft Azure上的SQL Server性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！