We have an 8 CPU 2.5GHz machine with 8 GB of RAM than executes SQL queries in slower fashion than a dual core 2.19 GHz with 4GB of RAM.
我们有一个8 CPU 2.5GHz的机器,8 GB的RAM比执行SQL查询的速度慢于双核2.19 GHz和4GB的RAM。
Why is this the case, given that Microsoft SQL Server 2000 is installed on both machines?
为什么会出现这种情况,因为两台机器上都安装了Microsoft SQL Server 2000?
5 个解决方案
#1
Our solution for multicore servers (our app executes many very complex queries, which tend to create many threads and these start to interlock and even deadlock sometimes):
我们的多核服务器解决方案(我们的应用程序执行许多非常复杂的查询,这些查询往往会创建许多线程,并且有时会开始互锁甚至死锁):
sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'max degree of parallelism', 1
reconfigure
This is not ideal solution, but we haven't noticed any performance loss for other actions.
这不是理想的解决方案,但我们没有注意到其他操作的任何性能损失。
Of course you should optimize disk layout too and sometimes limit SQL server memory for 64bit server.
当然,您也应该优化磁盘布局,有时限制64位服务器的SQL服务器内存。
#2
Just check these links to indicate where the bottleneck is situated
只需检查这些链接以指出瓶颈所在的位置
I think the disk layout and the location where which SQL server database files are causing the trouble.
我认为磁盘布局和SQL Server数据库文件导致问题的位置。
#3
Also, you may have different settings of SQL Server (memory assignments and AWE memory, threads, maximum query memory, processor affinity, priority boost).
此外,您可能有不同的SQL Server设置(内存分配和AWE内存,线程,最大查询内存,处理器关联,优先级提升)。
#4
Check the execution plans for the same query on both machines and, if possible, post it here.
检查两台计算机上相同查询的执行计划,如果可能,请在此处发布。
Most probably it will be the case.
最有可能是这种情况。
#5
Keep in mind that just be cause one machine has more CPUs running at a higher clock speed and memory than another, it's not necessarily going to solve a given problem faster than another.
请记住,只是因为一台机器有更多的CPU以比另一台更高的时钟速度和内存运行,它不一定能比另一台更快地解决给定的问题。
Though you don't provide details, it's possible that the 8-CPU machine has 8 sockets, each with a single-core CPU (say, a P4-era Xeon) and 1 GB of local (say RDRAM) RAM. The second machine is a modern Core 2 Duo with 4GB of DDR2 RAM.
虽然您没有提供详细信息,但8-CPU机器可能有8个插槽,每个插槽都有一个单核CPU(比如P4时代的Xeon)和1 GB的本地(比如RDRAM)RAM。第二台机器是现代Core 2 Duo,配备4GB DDR2 RAM。
While each CPU in machine #1 has a higher individual frequency, the netburst architecture is much slower clock-for-clock than the Core 2 architecture. Additionally, if you have a light CPU load, but memory-intensive load that doesn't fit in the 1GB local to the CPU on the first machine, your memory accesses may be much more expensive on the first machine (as they have to happen via the other CPUs). Additionally, the DDR2 on the Core 2 machine is much quicker than the RDRAM in the Xeon.
虽然机器#1中的每个CPU具有更高的单独频率,但是netburst架构比Core 2架构的时钟慢得多。此外,如果您的CPU负载很轻,但内存密集型负载不适合第一台机器上CPU本地的1GB,那么第一台机器上的内存访问可能要贵得多(因为它们必须发生)通过其他CPU)。此外,Core 2机器上的DDR2比Xeon中的RDRAM快得多。
CPU frequency and total memory aren't everything -- the CPU architecture, Memory types, and CPU and memory hierarchy also matter.
CPU频率和总内存并非一切 - CPU架构,内存类型以及CPU和内存层次结构也很重要。
Of course, it may be a much simpler answer as the other answers suggest -- SQL Server tripping over itself trying to parallelize the query.
当然,它可能是一个更简单的答案,因为其他答案建议 - SQL Server试图并行化查询绊倒自己。
#1
Our solution for multicore servers (our app executes many very complex queries, which tend to create many threads and these start to interlock and even deadlock sometimes):
我们的多核服务器解决方案(我们的应用程序执行许多非常复杂的查询,这些查询往往会创建许多线程,并且有时会开始互锁甚至死锁):
sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'max degree of parallelism', 1
reconfigure
This is not ideal solution, but we haven't noticed any performance loss for other actions.
这不是理想的解决方案,但我们没有注意到其他操作的任何性能损失。
Of course you should optimize disk layout too and sometimes limit SQL server memory for 64bit server.
当然,您也应该优化磁盘布局,有时限制64位服务器的SQL服务器内存。
#2
Just check these links to indicate where the bottleneck is situated
只需检查这些链接以指出瓶颈所在的位置
I think the disk layout and the location where which SQL server database files are causing the trouble.
我认为磁盘布局和SQL Server数据库文件导致问题的位置。
#3
Also, you may have different settings of SQL Server (memory assignments and AWE memory, threads, maximum query memory, processor affinity, priority boost).
此外,您可能有不同的SQL Server设置(内存分配和AWE内存,线程,最大查询内存,处理器关联,优先级提升)。
#4
Check the execution plans for the same query on both machines and, if possible, post it here.
检查两台计算机上相同查询的执行计划,如果可能,请在此处发布。
Most probably it will be the case.
最有可能是这种情况。
#5
Keep in mind that just be cause one machine has more CPUs running at a higher clock speed and memory than another, it's not necessarily going to solve a given problem faster than another.
请记住,只是因为一台机器有更多的CPU以比另一台更高的时钟速度和内存运行,它不一定能比另一台更快地解决给定的问题。
Though you don't provide details, it's possible that the 8-CPU machine has 8 sockets, each with a single-core CPU (say, a P4-era Xeon) and 1 GB of local (say RDRAM) RAM. The second machine is a modern Core 2 Duo with 4GB of DDR2 RAM.
虽然您没有提供详细信息,但8-CPU机器可能有8个插槽,每个插槽都有一个单核CPU(比如P4时代的Xeon)和1 GB的本地(比如RDRAM)RAM。第二台机器是现代Core 2 Duo,配备4GB DDR2 RAM。
While each CPU in machine #1 has a higher individual frequency, the netburst architecture is much slower clock-for-clock than the Core 2 architecture. Additionally, if you have a light CPU load, but memory-intensive load that doesn't fit in the 1GB local to the CPU on the first machine, your memory accesses may be much more expensive on the first machine (as they have to happen via the other CPUs). Additionally, the DDR2 on the Core 2 machine is much quicker than the RDRAM in the Xeon.
虽然机器#1中的每个CPU具有更高的单独频率,但是netburst架构比Core 2架构的时钟慢得多。此外,如果您的CPU负载很轻,但内存密集型负载不适合第一台机器上CPU本地的1GB,那么第一台机器上的内存访问可能要贵得多(因为它们必须发生)通过其他CPU)。此外,Core 2机器上的DDR2比Xeon中的RDRAM快得多。
CPU frequency and total memory aren't everything -- the CPU architecture, Memory types, and CPU and memory hierarchy also matter.
CPU频率和总内存并非一切 - CPU架构,内存类型以及CPU和内存层次结构也很重要。
Of course, it may be a much simpler answer as the other answers suggest -- SQL Server tripping over itself trying to parallelize the query.
当然,它可能是一个更简单的答案,因为其他答案建议 - SQL Server试图并行化查询绊倒自己。