什么是 sql server 中的逻辑读取？如何减少逻辑数？

what is logical reads in sql server? how to reduce no of logical?

提问by Nandha

From all my search to speed up the queries in sql server,sources said to reduce the logical reads by using the proper where clause. Actually, what I need to know is the work flow in sql server, when a stored procedure is being called by a request from an endpoint user or external system, and some tips on do's and don'ts'.

从我所有的搜索来加速 sql server 中的查询，消息来源说通过使用正确的 where 子句来减少逻辑读取。实际上，我需要知道的是 sql server 中的工作流程，当一个存储过程被来自端点用户或外部系统的请求调用时，以及一些关于该做什么和不该做什么的提示。

采纳答案by Thorsten Kettner

Logical reads means records you are reading from the database. Let's take a small, stupid example:

逻辑读取是指您正在从数据库中读取的记录。让我们举一个小而愚蠢的例子：

select *
from
(
  select *
  from orders
  where client = 1234
)
where item = 9876;

Here you select all orders from client 1234. Then later you only take those for item 9876. So (provided the optimizer doesn't see through this and optimizes your query internally) you select many more records in the first step than needed. Reduce logical reads (and the according large intermediate result) by applying both criteria in one step:

在这里，您选择来自客户端 1234 的所有订单。然后，您只选择项目 9876 的订单。因此（前提是优化器不会看穿这一点并在内部优化您的查询）您在第一步中选择的记录比需要的多得多。通过在一个步骤中应用这两个标准来减少逻辑读取（以及相应的大中间结果）：

select *
from orders
where client = 1234
and item = 9876;

(This may also effect physical reads, but doesn't necessarily have to. For instance the first query may access 100 records and then reduce that to 10, whereas the second only reads those 10. But all 100 records may be in one disk block, so both statements read one disk block, i.e. make one physical read. It can even be zero physical reads, by the way, in case the data happens to be already in the dbms cache, i.e. in memory. This also tells us that physical reads can vary for a query, while logical reads remain the same as long as the query and the data are not altered.)

（这也可能影响物理读取，但不一定必须如此。例如，第一个查询可能访问 100 条记录，然后将其减少到 10 条，而第二个仅读取这 10 条记录。但所有 100 条记录可能都在一个磁盘块中, 所以这两个语句都读取一个磁盘块, 即进行一次物理读取. 顺便说一下, 甚至可以是零次物理读取, 以防数据恰好已经在 dbms 缓存中, 即在内存中. 这也告诉我们物理一个查询的读取可能会有所不同，而只要查询和数据没有改变，逻辑读取就会保持不变。）

回答by Bacon Bits

A Microsoft white paper that's a part of the Retired SQL Server 2000 technical documentation(p387, originally from SQL Server Architecture (SQL Server 2000)) has a good definition:

作为Retired SQL Server 2000 技术文档（p387，最初来自 SQL Server Architecture (SQL Server 2000)）的一部分的 Microsoft 白皮书有一个很好的定义：

The I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache.If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory.

SQL Server 实例的 I/O 分为逻辑 I/O 和物理 I/O。每次数据库引擎从缓冲区缓存请求页面时，都会发生逻辑读取。如果页面当前不在缓冲区缓存中，则执行物理读取以将该页面读入缓冲区缓存。如果页面当前在缓存中，则不会生成物理读取；缓冲区缓存只是使用内存中已有的页面。

So, a logical read is when the query engine needs to read data. First, it looks in memory. If the page is already in SQL Server's memory, then it uses that. If it can't find it in memory, then that triggers a physical read and the data page is read from disk. A logical read without a subsequent physical read is a "cache hit," basically.

因此，逻辑读取是查询引擎需要读取数据的时候。首先，它在内存中查找。如果页面已经在 SQL Server 的内存中，那么它就会使用它。如果在内存中找不到它，则会触发物理读取并从磁盘读取数据页。没有后续物理读取的逻辑读取基本上是“缓存命中”。

The buffer cache (also known as the buffer pool) is SQL Server's primary working memory for solving queries. When you set the amount of memory that SQL Server will use, you're controlling the size of the available buffer cache.

缓冲区缓存（也称为缓冲池）是 SQL Server 用于解决查询的主要工作内存。当您设置 SQL Server 将使用的内存量时，您正在控制可用缓冲区缓存的大小。

However, telling you what you need to do without seeing the query or knowing what the table contains and what the data look like and how the data are indexed and organized is basically impossible.

但是，在不查看查询或不知道表包含的内容、数据的外观以及数据的索引和组织方式的情况下告诉您需要做什么基本上是不可能的。

Large numbers of logical reads may not necessarily be bad -- or, rather, not necessarily preventable. What's bad is an inordinatenumber of logical reads. If you're returning 3 rows of data, but the query engine had to scan 200 million rows of the table to do it, that's going to be very slow and you can probably improve that by rewriting the query or adding an index.

大量的逻辑读取不一定是坏的——或者说，不一定是可以预防的。不好的是过多的逻辑读取。如果您要返回 3 行数据，但查询引擎必须扫描表的 2 亿行才能执行此操作，这将非常缓慢，您可以通过重写查询或添加索引来改进它。

I would start by looking at how complex the queries in your stored procedure are. Notably, I'd look for missing indexes. If you're running SELECT * FROM BigTable WHERE ProductDate >= '01/01/2014', then I'd look to see that there was an index on ProductDate. If you're running SELECT * FROM BigTable ORDER BY ProductDate DESC, however, then, yes, an index will still help, but you'll still need to return the entire data set so you have to read the whole table anyways. Additionally, note that logical reads refer to pagereads, so if the ProductDatein question is evenly distributed around the disk, you might need to read every page or nearly every page anyways.

我将首先查看您的存储过程中的查询有多复杂。值得注意的是，我会寻找缺失的索引。如果您正在运行SELECT * FROM BigTable WHERE ProductDate >= '01/01/2014'，那么我会查看 上是否有索引ProductDate。SELECT * FROM BigTable ORDER BY ProductDate DESC但是，如果您正在运行，那么，是的，索引仍然会有所帮助，但是您仍然需要返回整个数据集，因此无论如何您都必须读取整个表。此外，请注意逻辑读取是指页面读取，因此如果有ProductDate问题的内容均匀分布在磁盘上，则您可能需要读取每一页或几乎每一页。

Beyond that, it could be that the statistics on the table are out-of-date. If you've added 20,000 rows to a table and SQL Server still thinks there's only 2000 there, it's going to completely throw of the query planning.

除此之外，表上的统计数据可能已经过时。如果您向表中添加了 20,000 行，而 SQL Server 仍然认为只有 2000 行，那么它将完全放弃查询计划。