ASP.NET/SQL 2008 性能问题

Question

我们开发了一个带有搜索屏幕的系统，看起来像这样：

_{(来源: nsourceservices.com)}

。正如您所见，它具有相当严格的搜索功能。您可以使用任意组合的状态、渠道、语言、活动类型，并通过名称等缩小范围等方式进行搜索。然后，在搜索完成并出现线索时，您可以对标题进行排序。查询使用ROWNUM进行分页方案，因此我们一次只返回大约70行内容。但即使我们只返回70行，也会发生大量IO和排序。这是有道理的。这一直导致磁盘队列出现一些小的峰值。当我们达到300万个线索时，它开始变慢，现在我们接近500万个线索时，磁盘队列有时会连续卡住一两秒钟。

That would actually still be workable, but this system has another area with a time-sensitive process, lets say for simplicity that it's a web service, that needs to serve up responses very quickly or it will cause a timeout on the other end. The Disk Queue spikes are causing that part to bog down, which is causing timeouts downstream. The end result is actually dropped phone calls in our automated VoiceXML-based IVR, and that's very bad for us. 我们尝试过的方法 我们已经尝试过：

• 维护任务，将系统中的潜在客户数量降至最低。
• 添加明显的索引以帮助优化。
• 在分析器中运行索引调整向导并应用其大部分建议。其中一个建议是在索引中重复整个表格，因此我手动调整了一下以减少工作量。
• 为服务器增加了更多的RAM。原本有点不足，但现在它始终保持着大约8GB的空闲内存，而SQL服务器的配置使用不超过8GB，但它从未使用过2或3个GB以上。我发现这很奇怪。为什么它不把整个表格放入内存中呢？只有500万条记录，还有很多空间。
• 详细查看查询执行计划。我可以看到，此时索引似乎大多数情况下都在发挥作用——大约90%的工作是在排序阶段完成的。
• 考虑将Leads表格分区到另一个物理驱动器中，但我们没有足够的资源，而且似乎也不需要。

最后...

一部分我的感觉是服务器应该能够处理这个问题。考虑到服务器的性能，它是一个不错的四核心服务器，带有16G内存，500万条记录并不算太多。然而，我可以看到排序部分会导致数百万行被访问以返回少量数据。

那么在这种情况下，你们通常会怎么做呢？我的直觉告诉我我们或许应该减少一些功能，但如果有办法保持原样，这将避免我与业务部门的争执。

提前致谢！

- Brian MacKay

---

@Matthew PK：没有GUID。聚集索引只是主键--LeadID（int）。至于固态硬盘...嗯，把钱扔进去只能是我最后的选择。但这确实在我考虑范围内。 :) - Brian MacKay

查询是动态 SQL 构建的还是固定查询？可能需要对查询本身进行调整而不是索引。 - HLGEM

@HLGEM：这可能也涉及到ORM并不是最佳选择的领域。实际上，我可能正在失去大量IO来提取字段数量。 - Brian MacKay

如果你认为ORM可以优化查询，那么它可能会带来风险。对于一个棘手、性能要求高的查询，你可能需要考虑使用存储过程（甚至是根据搜索条件决定具体存储过程的模式）。我认为你对于ASP和视图状态的观点是正确的……在那里倾泻一百万行数据可能不是最好的选择。 - Matthew

如果您启动另一个线程来显示结果集的前N行，同时查询继续搜索所需信息，这样怎么样？ - FrankRuperto

Answer 1

数据库瓶颈经常可以通过改进SQL查询来改善。如果不知道这些查询是什么样子的，考虑创建一个按计划填充的运营数据存储库或数据仓库。

有时候展开复杂的关系数据库是正确的方法。它可以使查询运行速度显著提高，并且使优化查询变得更加容易，因为模型非常平坦。这也可能使确定是否需要将数据库服务器扩展上下文变得更容易。容量和增长分析可能有助于做出决策。

事务/高度规范化的数据库通常不如ODS或数据仓库可扩展。

编辑：您的ORM也可能支持优化，值得一试，而不仅仅是寻找如何优化发送到数据库的查询。完全绕过ORM以进行报告可能是一种获得更好性能的方式，以便完全控制查询。

- Shan Plourde

---

另一个 ORM 失败的地方。 - 3Dave

是的，ORM在某些情况下非常好，但也许不适用于这种情况。也许如果解决方案需要保持ORM导向，可以采用扁平化数据库模型，并使用简单的对象层来构建查询。我怀疑最简单的方法可能是探索优化现有ORM查询的方法。 - Shan Plourde

Answer 2

1. 确定哪些特定的即席查询最有可能被运行或通过存储过程限制搜索条件。你能总结数据吗？将此应用程序视为数据仓库。
2. 在涉及搜索的每个列上创建索引以避免表扫描。
3. 在表达式上创建片段。
4. 随着更多的线索被加载，定期重新组织数据并更新统计信息。
5. 将查询（结果集）创建的临时文件放置在ramdisk中。
6. 考虑迁移到高性能的RDBMS引擎，如Informix OnLine。
7. 启动另一个线程以开始显示结果集中的N行，同时查询继续执行。

- FrankRuperto

---

好东西...此外，即使您没有遇到这个错误，这些步骤仍然适用：http://stackoverflow.com/questions/4719841/system-data-sqlclient-sqlexception-timeout-expired#4719892 - Chris B. Behrens

除非您仔细控制查询（我猜ORM不会这样做），否则过多的索引可能会导致数据库尝试“万能”的计划，而不是为特定类型的搜索选择“最佳”索引。 - Matthew

这真的很有趣，你有用于构建ramdisk的特定工具吗？谷歌找到了RamDisk和RamDisk Plus。 - Brian MacKay

@Brian：RamDisk Plus 11..你还需要考虑有多少个同时用户将执行查询。容量规划将决定你的应用程序需要多少G的RAM。 - FrankRuperto

@BrianMacKay：即使查询尚未完成结果集的构建，是否有可能启动另一个线程来开始显示搜索结果？ - FrankRuperto

Answer 3

考虑一下你的ORM是如何创建查询的。 如果你的搜索性能较差，可以尝试使用存储过程来返回结果，并根据使用的搜索条件，必要时使用多个特定的存储过程。

- Matthew