SQL Server 中特定查询请求的 I/O 优先级
抱歉，介绍很长，但在我提出问题之前，我认为提供背景知识将有助于更好地理解我们的问题。
我们的 Web 服务使用 SQL Server 2008 作为后端，有时需要花费太多时间来响应本应运行得非常快的请求，例如查询表的选择请求需要花费超过 20 秒的时间只有 22 行。我们检查了许多可能导致问题的潜在区域，从索引到存储过程、触发器等，并尝试优化我们能做的一切，例如删除不读取但频繁写入的索引，或者为我们的选择查询添加 NOLOCK 以减少对索引的锁定。表（我们可以接受脏读）。
我们还让 DBA 检查了服务器并对组件进行了基准测试，以查看 CPU、内存或磁盘子系统中的任何瓶颈，并发现硬件方面我们也没有问题。由于尖峰偶尔发生，因此很难在生产或开发中重现错误，因为大多数时候，当我们重新运行相同的查询时，它会产生我们期望的响应时间，该响应时间很短，而不是之前的响应时间。较早经历过。
话虽如此，我几乎对 I/O 产生了怀疑，尽管它似乎不是瓶颈。但我认为在对服务器上的特定表运行索引碎片报告后，我能够重现该错误，这立即导致请求中的尖峰不仅针对该表运行，而且在查询其他表的其他请求中也出现。由于数据库和服务器与我们使用的其他应用程序共享，并且有时可以在服务器和数据库上运行查询，这对我们来说是常见的情况，因此我怀疑偶尔出现 I/O 瓶颈我相信，这正在成为事实。
因此，我想找到一种方法来优先处理来自 Web 服务的请求，即使正在运行其他资源敏感查询，这些请求也会得到处理。从解析过程一开始，我就一直在寻找上面描述的某种优先级，并发现 SQL Server 2008 有一个名为“资源调控器”的功能，可以对请求进行优先级排序。
但是，由于我不是资源调控器方面的专家，也不是 DBA，所以我想问问其他可能已经使用过或正在使用资源调控器的人的经验，以及我是否可以为特定登录或特定的 I/O 确定优先级存储过程（例如，如果我们收到 Web 服务请求时正在运行一个 I/O 密集型进程，SQL Server 是否可以停止或减慢该进程的 I/O 活动，并优先处理我们的请求）刚收到？）。
感谢任何花时间阅读或提前提供帮助的人。
一些硬件详细信息：
CPU：2 个四核 AMD Opteron 8354
内存：64GB
磁盘子系统：Compaq EVA8100 系列（我不确定，但它应该是跨 8 个 HP HSV210 SCSI 驱动器的 RAID 0+1）
PS：我几乎可以 100% 确定应用程序服务器不会导致错误，并且我们可以不存在瓶颈在那里识别。
更新1：
我将尽力回答gbn 下面提出的以下问题。如果您正在寻找其他东西，请告诉我。
1) 请问你们有什么样的索引和统计维护？
我们有一个每周运行的工作，每周五对索引进行碎片整理。除此之外，还启用了自动创建统计数据和自动更新统计数据。而且峰值也发生在碎片作业以外的其他时间。
2) 您有什么样的写入数据量？
很难回答。据我所知，除了我们的 Web 服务之外，还有一个前端应用程序访问同一数据库，并且需要定期运行资源密集型查询，但是，我不知道如何获取，比如说每周一次或每天将金额写入数据库。
3) 您是否分析过重新编译和统计更新事件？
抱歉无法弄清楚这一点。我不明白你这个问题想问什么。如果可能的话，您能为这个问题提供更多信息吗？
Sorry for the long introduction but before I can ask my question, I think giving the background would help understanding our problem much better.
We are using sql server 2008 for our web services as the backend and from time to time it takes too much time for responding back for the requests that supposed to run really fast, like taking more than 20 seconds for a select request that queries a table that has only 22 rows. We went through many potential areas that could cause the issue from indexes to stored procedures, triggers etc, and tried to optimize whatever we can like removing indexes that are not read but write frequently or adding NOLOCK for our select queries to reduce the locking of the tables (we are OK with dirty reads).
We also had our DBA's reviewed the server and benchmarked the components to see any bottlenecks in CPU, memory or disk subsystem, and found out that hardware-wise we are OK as well. And since the pikes are occurring occasionally, it is really hard to reproduce the error on production or development because most of the time when we rerun the same query it yields response times that we are expecting, which are short, not the one that has been experienced earlier.
Having said that, I almost have been suspicious about I/O although it is not seem to be a bottleneck. But I think I was just be able to reproduce the error after running an index fragmentation report for a specific table on the server, which immediately caused pikes in requests not only run against that table but also in other requests that query other tables. And since the DB, and the server, is shared with other applications we use and also from time to time queries can be run on the server and database that take long time is a common scenario for us, my suspicion regarding occasional I/O bottleneck is, I believe, becoming a fact.
Therefore I want to find out a way that would prioritize requests that are coming from web services which will be processed even if there are other resource sensitive queries being run. I have been looking for some kind of prioritization I described above since very beginning of the resolution process and found out that SQL Server 2008 has a feature called 'Resource Governor' that allows prioritization of the requests.
However, since I am not an expert on Resource Governor nor a DBA, I would like to ask other people's experience who may have used or is using Resource Governor, as well as whether I can prioritize I/O for a specific login or a specific stored procedure (For example, if one I/O intensive process is being run at the time we receive a web service request, can SQL server stops, or slows down, I/O activity for that process and give a priority to the request we just received?).
Thank you for anyone that spends time on reading or helping out in advance.
Some Hardware Details:
CPU: 2x Quad Core AMD Opteron 8354
Memory: 64GB
Disk Subsystem: Compaq EVA8100 series (I am not sure but it should be RAID 0+1 accross 8 HP HSV210 SCSI drives)
PS:And I can almost 100 percent sure that application servers are not causing the error and there is no bottleneck we can identify there.
Update 1:
I'll try to answer as much as I can for the following questions that gbn asked below. Please let me know if you are looking something else.
1) What kind of index and statistics maintenance do you have please?
We have a weekly running job that defrags indexes every Friday. In addition to that, Auto Create Statistics and Auto Update Statistics are enabled. And the spikes are occurring in other times than the fragmentation job as well.
2) What kind of write data volumes do you have?
Hard to answer.In addition to our web services, there is a front end application that accesses the same database and periodically resource intensive queries needs to be run to my knowledge, however, I don't know how to get, let's say weekly or daily, write amount to DB.
3) Have you profiled Recompilation and statistics update events?
Sorry for not be able to figure out this one. I didn't understand what you are asking about by this question. Can you provide more information for this question, if possible?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
第一个想法是统计由于达到数据更改阈值而导致执行计划被重建，因此正在更新。
first thought is that statistics are being updated because of the data change threshold is reached causing execution plans to be rebuilt.
为了回答您对原始问题的更新的问题 3)，请查看 SQL Server Pedia 上的以下参考。它解释了什么是查询重新编译，并继续解释如何监视这些事件。我相信 gbn 所问的问题（请随意纠正我，先生:-)）是您是否在缓慢执行麻烦的查询之前看到重新编译事件。您可以使用 SQL Server Profiler 来查找是否发生这种情况。
重新编译查询执行计划的原因
In response to question 3) of your Update to the original question, take a look at the following reference on SQL Server Pedia. It provides an explanation of what query recompiles are and also goes on to explain how you can monitor for these events. What I believe gbn is asking (feel free to correct me sir :-) ) is are you seeing recompile events prior to the slow execution of the troublesome query. You can look for this occurring by using the SQL Server Profiler.
Reasons for Recompiling a Query Execution Plan