Posted by Jonathan Allen  From/InfoQ

Hosting server applications inside VM images is all the rage today. The ability to quickly move a virtual server from one machine to another as needs change is a big win for IT departments. But can this be applied to heavyweight systems like SQL Server? Conor Cunningham says no.

According to Conor, SQL Server makes several assumptions about its environment. These include:

• All CPUs are equally powerful
• All CPUs process instructions at about the same rate.
• A flush to disk should probably happen in a bounded amount of time.

The first issue comes into play with high-end SKUs that support parallel queries. When a query is executed, the work is evenly spilt among threads. But with both hyperthreading and virtualization, those threads are not running at a consistent speed.

So now I have some threads that finish earlier than others. So they block until the slowest threads finish. Even worse, I don't think that the query re-allocates those threads for other queries until the whole query finishes. So, now you have some background as to why hyperthreading was not recommended for at least some SQL Server deployments.

Later Conor discusses memory and I/O,

SQL Server assumes, at least in the main server SKUs, that it is the only significant memory consumer on the machine. It's a *server*. (SQLExpress has different assumptions, but it's no memory slouch either). Now, SQL Server will work in a memory constrained environment, but you often don't want to do that. You take that away from a lot of different things - the buffer pool, the compiled plan cache, memory to execute queries (for example, hash join grants). All of these things can add up if you aren't careful.

I/O is the area where I have the least experience in virtualization. This is one of the reasons I asked people about production SQL Servers. Usually they did get some storage array, and this makes sense - it ramps the I/O bandwidth and usually isolates it from any other operations on the machine (your OS, your application you are developing on top of SQL Server, etc). I'm going to spend some more time on this, but I think the core idea is sound - as you start sharing your I/O bandwidth over several VMs, you are going to hit limits earlier with big IO consumers like SQL Server. The same basic logic applies - isolate your database traffic onto different storage paths, especially when building a system to scale. In a VM world, this can let you avoid the sharing penalties vs. the default config of everyone sharing the same hard drive.

All this isn't to say that SQL Server cannot run in a virtual image, merely that if performance is critical than it probably isn't work the cost.

感谢原创者的辛勤劳动，希望对您有所帮助，转载请注明原出处。

您可能对 [SQL] 的这些文章也感兴趣： SQL 性能优化（SQL Performance Tuning） SQL语句的自动优化 SELECT @@IDENTITY中的@@含义及应用方法 一个横表变纵表的例子 转:[分享]SQL语句集锦【强力推荐！】 SQL查询语句精华使用简要 分页且带条件的存储过程 常见数据库系统之比较 - 数据库的发展和现状 优化.NET访问Active Directory的性能 NHibernate系列(6)：探索NHibernate中的事务

数据库虚拟化——这样做值吗？

译/赵劼

在虚拟机镜像中部署服务器应用程序的做法风靡一时。当需求改变时，虚拟服务器可以快速地从一台机器移到另一台机器上的这种能力让IT部门受益无穷。但是，这种做法适用于像SQL Server这样的重量级系统吗？对于这个问题，Conor Cunningham提出了否定的看法。

根据Conor的说法，SQL Server对它的运行环境有一些假设，这包括：

• 所有的CPU能力相同；
• 所有CPU执行指令的频率大致相同；
• 磁盘缓存的写入动作必须在一个确定的时间段内发生。

第一个问题会出现在支持并行查询的高端数据库版本中。当执行一个查询时，所有的工作会被平均地分配到不同的线程中，但在超线程（hypertheading）或是虚拟化的环境中，这些线程并不是按照一致的速度运行的。

在这种情况下，某些线程会比另外一些提前完成，因此较快的线程会被阻塞，直至最慢的线程执行完毕。更糟糕的是，除非是整个查询都完成了，否则这些线程都无法被分配给其他查询任务。现在，你应该了解到为什么说某些SQL Server不适合部署到超线程机器上的真正原因了。

Later Conor接着讨论了内存和I/O方面的问题，

SQL Server有假设条件，至少在被配置为主要服务模式的SQL Server中，它会假设自己是机器中唯一一个会使用大量内存的服务程序，因为这是一台“服务器”（SQL Express的假设不同，不过它并不会放松对内存的需求），而现在，SQL Server运行在一个内存受限的环境中，尽管你并不希望这样做，但这样做会让许多事情受到影响——缓存池、查询计划的缓存、以及用于查询的内存（例如进 行hash join的条件）等等。如果你不当心的话，这些问题都可能会变的愈发严重。

虚拟化中的I/O部分我的经验很少，这也是为什么我向其它人请教SQL Server产品相关问题的原因之一。他们通常使用会使用存储阵列（storage array），这的确是个有效的方法——它大大提高了I/O的带宽，并且把它和机器中的其他操作（如你的操作系统、你正在开发的SQL Server上层应用程序等）隔离开来。我打算花更多的时间去研究它，不过我认为这个想法的基础是有效的——因为当你开始让多个虚拟机共享I/O带宽时， 像SQL Server这样的IO带宽消耗大户会让你很快达到极限。所以，按照前面的逻辑，你应该将你的数据库通信隔离在独立的存储路径上，尤其是当你想构建一个扩展性良好的系统时更应该如此。在虚拟机环境中，这样做能让你避免因使用默认配置让所有人共享同一硬盘而造成的严重后果。

以上的说明并不是在说SQL Server无法在虚拟镜像中运行，只是为了说明当你对性能的要求很迫切时，使用虚拟机将会使你得不偿失。