SQL查询，如何知道是否是资源密集型？I/O

Question

我不是SQL专家，但我正在解决我们的营销平台的一个问题，因为它变得浮躁和缓慢，我已经要求在我们的sql服务器上执行的前50项重/资源密集型查询与我们的营销工作流相关联。

下面是一个例子或一些顶级查询，基于平均I/O，您会说这些查询消耗了太多的资源吗？用于查询的正常可接受IO是什么？

如果采用第一个查询执行计划，则如下所示。

计划链接https://www.brentozar.com/PasteThePlan/?id=HJtGXHxUc

Answer 1

对于查询，没有一个好的或坏的值。这完全取决于查询在做什么。从没有WHERE子句的表中从具有数十亿行的表中选择*将具有非常高的I/O级别，而从ID列上带有WHERE子句的表中选择ID和ID列上的聚集索引将具有尽可能低的I/O级别。两者都不是对的或错的，而且这里没有>42是一种不好的衡量标准。

相反，您必须查看查询以及它正在做什么。然后，查看执行计划，查看查询是如何解析的。

在本例中，您有一个表扫描，这意味着它是一个没有聚集索引的堆，即使有明确的子句值可以使用索引查找数据。因此，堆没有任何非聚集索引，或者它拥有的非聚集索引不支持查询。这是否意味着存在过多的I/O？是的，可能吧。

此外，您还使用了一个非聚集索引，skypipeline_eventid，但它不是覆盖索引，因为您有一个RID查找(另一个堆表)。这是过度的I/O吗？是的，可能吧。

一般来说，绝大多数表都应该有聚集索引。迹象是，这个数据库没有，或很少(样本大小2不是完全不确定的)。如果列上的聚集索引(S)定义了最常用的数据路径，那么您只会在整个地方进行表扫描。所以，是的，你可能正经历着过度的I/O。

Answer 2

理论:要确切地知道一个批处理(不是一个查询，而是整个查询集)是否过多，您必须首先确定它可能会更好。最完美的方法是打开批处理并以不同的方式编写它--根据扩展事件或Profiler逻辑读、写、CPU和SQL:BatchCompleted事件的持续时间--更好地处理所有可能的输入参数情况。

如果查询可以更好，那么任何更坏的东西都是过度的。

实际上，它越模糊，您的数据库和代码就越好。一组可怕的关于可怕模式的查询(请参阅Grant提到的聚集索引，等等)。很容易发现，也很容易在每一种可能的方式下变得更好。对一个相当好的模式进行相当好的查询往往会导致权衡--一种不同的编写方式在60%的情况下更好，在40%的情况下更糟，但是如果在最繁忙的时候，您的工作量大多是40%，而在其他较少繁忙的时间，则主要是60%的工作量？

对于一个简单的实用答案:为SQL:BatchCompleted打开扩展事件一分钟，没有将过滤器保存到文件中，允许多个语句丢失，请在打开时观察所有用户的性能，然后关闭它。看看逻辑读，写，CPU，也许持续时间，如果你关心。见鬼，在这个阶段也加入物理阅读。

• 如果一分钟测试是可以的，你可以试几分钟，慢慢地往上爬，不要把它留在生产机器上。

现在您有了实际的结果；此外，如果您查看语句或Sql_text或batch_text字段，就会有一个实际的runnable语句，其中包括发送到SQL的参数。

• 首先，按语句/Sql_text/批处理_text进行排序，并查看应用程序是否重复发送相同参数的相同语句。
  • 如果这些批次一次又一次地返回相同的结果，那么现在就开始让营销应用程序人员使用它的过程.因为通常需要很长时间才能改变应用程序。提高SQL性能的最简单方法是减少工作量。

• 然后你做了一次，很可能-这是非常罕见的步骤，可以根据物理读取；如果你经常或通常做一个高百分比的物理读取比逻辑.
  • 如果在调优过程中很容易给您带来更多的性能，那么添加RAM就是要抛出它的硬件。
  • 添加正确的索引也可以完全解决这个问题，特别是当您缺少适当的集群和非聚集索引时--如果SQL必须读取100 IO的数据，而它应该只读取12 IO，那么您将非常快地浏览缓冲区内存，从而导致过多的无意义磁盘IO。
  • 调优也可能对此有所帮助，原因与上述相同。

• 现在，按逻辑读取和CPU一次排序；您需要知道实际可能的瓶颈是什么。大多数系统都是合乎逻辑的。有些系统--通常设计得很糟糕，或者资源严重不足--是CPU。
  • 查看最大的查询；其中，查找经常调用的查询。大而频繁是第一件要看的事情。
    • 实际的关键是峰值时间内的累计负载，但是对于您的第一次查看，只需选择看起来很大的内容。

- Open them up. 
    - First, fix obvious problems.  If it's doing a JOIN on something non-unique and then a DISTINCT on the results when a better join gives perfectly accurate data without the DISTINCT, fix it.  Again, make SQL do LESS work - less columns used, less rows used by the total batch.  Only what is actually necessary.  Quit putting columns in #temp or derived tables that aren't necessary to end results.  Etc.
    - Then, write them differently - at least two or three different ways.  With #temp tables, without, different joins, whatever other techniques you think might work.  Use Extended Events or Profiler on a test system to compare them. 
        - Remember parameter sniffing - ALTER PROCEDURE or sp\_recompile or use DBCC FREEPROCCACHE (On your test system, NOT on production) to reset the query plan cache each time, "prime" it with a different inital query.

    - Now, determine which indexes - and which column order for multi-column indexes - might help.  Add one, test the variations, drop the index and add another. 
        - Every index means more writes, more space, slower maintenance, bigger backups, etc.
        - Study how SQL uses multi-column indexing.  Watch it in query plans.

在对数据库的同一个通用域中的几个查询执行上述操作之后，您将开始看到常见的问题；您将开始感觉到什么是过度的。您可以查看一个查询，然后说：“嘿，要获得X，我认为读取100,000次太过了，太过了。”

这是你的系统的经验引导你，当你看到一个新的系统，你必须重新开始，但你有一个更好的基础工作。