优化 SQL Server 2008 存储过程

Question

更新 25/03/2011： 我已将这个问题标记为已回答，虽然我还没有具体的答案来帮助改进系统，但我认为等到我做到那一步才公开是不公平的。这个问题很广泛，而答案给了我很多指导，帮助我走上正确的道路。当我有更具体的问题需要问时，我会再回来的。不幸的是，我只能选择一个答案作为最佳答案，但我想感谢每个人的贡献，谢谢你们的时间。

原问题：

提前道歉，因为这个问题可能会相当广泛！

我刚刚要着手进行一个项目，在这个项目中，我们的另一个开发人员已经基于SQL Server 2008企业版、报告服务和存储过程构建了一个大型系统。在VM系统上（即SQL Server运行在由VMWare1 ESXi托管的Windows Server 2008 R2系统上），在性能测试中我们发现它似乎不能利用额外的内存或核心。

当我开始这个项目时，我正在寻求一些初步概述问题的指导：

什么是优化利用更多内存和处理器核心的SQL Server 2008的机会？

是否有关于调用、结构、开发程序的规则等方面的规定，这些规定将影响SQL Server如何并行化存储过程？

此外，我想问下一个显而易见的点是 - 我需要注意哪些代码方面，这些代码可能为他人提供更多关于它结构的见解，让我提供更具体的例子？系统中有很多行代码，但是显然理想的情况是尝试找到一个简明的示例或模式，演示用法。

我相信存储过程是由报表服务调用的。

在我们的测试中，几乎似乎是线性执行 - 即如果我们使用2个内核、2GHz CPU分配和4GB内存配置VM运行1个报告需要2分钟，那么2个报告将需要近4分钟（即两个报告时间加倍），虽然不完全是线性的，但足够接近了 - 一切都变得更慢，就好像它们根本没有并行运行。

难道SQL Server Reporting Services只是单独运行报告或与此有关吗？

所有存储过程都是用Transact-SQL开发的。

再次道歉，问得太广泛了。如果有人能帮助我教育在这个领域更深层次、更具知识的话，那将不胜感激！

- Matt Peddlesden

---

SQL Server的哪个版本（年份：2005或2008）和版本（企业版，快速版等）。 - RacerX

@RacerX：“SQL Server 2008 Enterprise Edition”（第二段） - Martin Smith

报告尝试处理多少数据？数据大小是多少？报告运行的存储过程具体在做什么？ - DForck42

Answer 1

需要注意以下几点：

1. 内存是你的朋友，你拥有的内存越多，SQL Server 需要访问磁盘的次数就越少，而访问磁盘的速度是内存的 1000 倍。
2. 你是否有正确的索引，并且是否在使用这些索引？
3. 你的 where 子句和 order by 是否被索引覆盖了？
4. where 子句是否可搜索，或者是否以一种 SQL Server 不会使用索引的方式编写的？
5. 存储过程是否使用基于 SET 的操作，还是使用游标和循环数据集（后者速度更慢）？
6. 存储过程是否在只需要两列数据时使用 SELECT *？
7. 将日志、tempdb 和数据文件分别放在它们自己的磁盘上，如果你有非聚集的索引，也将它们放在自己的磁盘上。
8. 在运行存储过程时使用性能监视器，甚至任务管理器，观察发生了什么... CPU 是否达到最大值... 内存是否达到最大值。
9. 一个报表是否阻塞了另一个？运行 EXEC sp_who2 并查看 BlkBy 列。
10. 获取其中一个存储过程，在 SSMS 中运行它并查看执行计划... 如果你看到很多扫描和总执行项的高百分比... 首先关注这些内容，还要查找计划中的转换等问题。

你能展示一些代码吗？我们可以快速查看并告诉你是否存在红旗问题。

- SQLMenace

---

@Matt - 在并发活动发生时，您可以查看sys.dm_os_schedulers以了解负载如何在核心之间分布。 - Martin Smith

当运行两个报告查询时，您是否会遇到锁定的情况？ - HLGEM

如果您没有索引或者没有充分利用它们，那么CPU使用率会很高。这将不可避免地导致多个选择时性能表现差。 - Dumitrescu Bogdan

@SQLMenace - 我们运行所有的测试三次，忽略第一次，因为第一次通常比接下来的两次慢得多，而这两次总是在相同的时间左右。不过，退化的一般模式总是大致相同的。 - Matt Peddlesden

大家好 - 感谢您的反馈，我认为我需要根据这些反馈去进行一些测试，并且现在需要找到一些答案！ - Matt Peddlesden

Answer 2

如果从同一个连接中进行两个过程调用，它们将顺序执行。也就是说，一个将运行，当它完成后，下一个将运行。

如果从一个连接中运行一个过程，并且同时从另一个连接中运行一个过程，则它们将同时运行，具体取决于锁定条件。

- RacerX

---

鉴于存储过程很可能是由报表服务触发的，那么这个过程很可能使用单个连接还是多个连接呢？或者这可以在某种程度上受到影响吗？ - Matt Peddlesden

你是在启动一个调用两个过程的单一报告，然后它们会依次运行。如果同时运行两个报告，则它们将同时运行，只要它们不互相锁定。 - RacerX

是的，这基本上将是 ASP.NET Web 应用程序的两个用户各自触发报告，因此我的期望是在理论上同时生成两个完全独立的报告。我想要测试的一件事（除了上面建议的锁定检查之外）是通过两个显式分开的连接手动调用存储过程，并查看它们是否更有效地工作（即指向报告服务或存储过程）。谢谢！ - Matt Peddlesden

Answer 3

当涉及到虚拟化时，Brent Ozar 是你的好朋友。虚拟化最佳实践

另外，报告提取的数据是否为报告进行了优化？如果没有，那么在 SQL 侧和报告侧处理报告会占用大量的处理时间。如果 OLTP 系统使用率很高，则可能需要开发某种 OLAP 过程。根据报告的要求，你还可以考虑缓存报告。

另外一件事是检查 SQL Server 的最大内存设置，如果设置为最大 2GB，则增加另外 4GB 并没有多大用处。

- DForck42