我的基于云的服务器在从单核升级到双核后运行SQL Server查询减慢50％ [英] My cloud-based server runs a SQL Server Query 50% slower after an upgrade to dual core from single core

问题描述

我的应用程序在Windows Server 2008上使用Tomcat运行SQL Server 2008.

My application runs SQL Server 2008 on Windows Server 2008 using Tomcat.

我使用基于云的服务器

我的一个查询需要48秒从应用程序运行。它从数据库中提取425条记录。

One of my queries takes exactly 48 seconds to run from the application. It pulls 425 records from the database.

我并不惊讶于时间长度。

I was not surprised by the length of time.

虽然类似的查询在Oracle上运行6秒，Oracle安装程序具有2GB内存和四处理器，而我的SQL Server设置为1GB内存和单个2.66GHZ处理器。

Although a similar query on Oracle ran in 6 seconds, the Oracle setup had 2GB of memory and a quad-processor, whereas my SQL Server set up was 1GB of memory and a single 2.66GHZ processor.

因此，我增加了服务器规格为2GB。没有改变。

Therefore I increased my server spec to 2GB. There was no change.

然后，我将我的服务器规格增加到了单处理器的双处理器。
我惊讶地发现查询现在需要更长的时间 - 正好70秒！

I then increased my server spec to dual processor from single processor. I was surprised to find that the query now took longer - exactly 70 seconds!

我知道将查询从P / SQL转换为T / SQL可能以非常非高效的方式完成。 Oracle和SQL Server之间可能存在速度差异。
然而我的主要问题是，为什么同一个查询在双核系统上比在单核上运行得慢？

I am aware that the conversion of the query from P/SQL to T/SQL may have been done in a very non-efficient way. And there may be speed differences between Oracle and SQL Server. However my main question is really, why should the same query now run slower on a dual-core system than on a single-core?

谢谢！

推荐答案

根据所使用的虚拟机管理程序服务器上的总负载，双虚拟CPU可以导致获得（显着）更少的CPU时间，因为一些虚拟机管理程序的调度程序坚持找到可同时调度的2个CPU。如果你的查询是单线程的，第二个CPU的优势是零，但是获得更少的CPU时间会使它变得更慢。

Depending on the hypervisor used and the total load on the server, dual virtual CPUs can result in getting (significantly) less CPU time, as some hypervisor's scheduler insists of finding 2 CPUs that are schedulable at the same time. If your query is single-threaded, the advantage of a second CPU is zero, but getting less CPU time makes it slower.

恰恰相反的方向是另一种可能性：If您的查询是多线程的，并且您的存储缓慢，则2个线程可以创建随机IO风暴，这会降低您的存储吞吐量。

Exactly the opposite direction is another possibility: If your query is multithreaded and your storage is slow, then the 2 threads can create a random IO storm, that decreases your storage throughput.

这篇关于我的基于云的服务器在从单核升级到双核后运行SQL Server查询减慢50％的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！