Logical read, physical read (SET STATISTICS IO)

In Query performance optimization, Logical read is very important, and its count is generally proportional to the number of result sets queried, and is proportional to the speed of data read.

1,set STATISTICS IO Explicit disk IO information

Syntax

SET STATISTICS  on | OFF }

Output Item	meaning
Table	Name of the table.
Scan Count	Number of scans performed.
Logical Reads	Number of pages read from the data cache.
Physical reads	Number of pages in read from disk.
Read-ahead reads	Number of pages placed into the cache for the query.
LOB Logical Reads	Number of text, ntext, image, or large value type (varchar (max), nvarchar (max) /c10>, varbinary (max)) pages read from the data cache.
LOB Physical reads	Number of text, ntext, image , or large value type pages read from disk.
LOB Read-ahead reads	Number of text, ntext, image , or large value type pages placed into the cache for the query.

Example:

 use   AdventureWorks;        go  set  statistics  IO on  ;  go  select  *  from   Production.productcosthistory  where  StandardCost <  500.00  ;  go  set  statistics  IO off  ;  go  

Here is the result set:

 table   "  . Scan count  1 , Logical reads 5   physical Reads  0 , read  - ahead reads 0 , LOB logical reads 0 , lob physical reads 0   read  - ahead reads 0 . 

2, referencing SQL performance tuning SET STATISTICS IO

Scan Count: The number of times the table that is involved in the query is accessed. In our case, the table is accessed only 1 times, and this information is not very useful because the connection command is not included in the query, but this information is useful if the query contains one or more connections. (The scan count value for a table outside a loop is 1, but for a table within a loop, its value is the number of cycles.) As you can imagine, for a table within a loop, the smaller the scan count value, the less resources it uses, and the higher the performance of the query. Therefore, when adjusting the performance of a connected query, you need to pay attention to the value of scan count, and when adjusting, observe whether it is increased or decreased. ）

logical Read (Logical Reads): This is the most useful data provided by the set STATISTICS IO or set STATISTICS time command. We know that SQL Server must first read the data into its data buffer before it can manipulate any data. In addition, we know when SQL Server reads data from the data buffer and reads the data into a page of size 8K bytes. So what is the meaning of logical reads? Logical reads refers to the number of pages that SQL Server must read from the data buffer to get the results in the query. When executing a query, SQL Server does not read more or less data than is actually required, so when executing the same query on the same dataset, the resulting logical reads numbers are always the same. (logical reads value when SQL Server executes a query this value does not change every time.) So, when it comes to tuning query performance, this is a good standard to measure the success of your adjustment measures. If the Logical reads value drops, it indicates that the query uses less server resources and the query has improved performance. If the logical reads value is increased, the throttling action reduces the performance of the query. In the case of other conditions, the less logical reading a query uses, the more efficient it is, and the faster the query will become. ）

Physical Read (physical Reads): Physical read, before performing a real query operation, SQL Server must read the data it needs from disk to the data buffer. Before SQL Server starts executing a query, the first thing it does is to check if the data it needs is in the data buffer, if it reads from it, and if not, SQL Server must first read the data it needs from the disk into the data buffer. As we can imagine, SQL Server requires more server resources to perform a physical read than to perform a logical read. Therefore, in an ideal situation, we should try to avoid physical reading operations. This part of the following sounds easy to confuse. When tuning the performance of a query, you can ignore physical reads and focus only on logical reads. You're going to wonder, didn't you just say that physical reading requires more server resources than logical reading? Indeed, the physical reads that SQL Server needs to perform queries are not likely to be reduced by performance tuning. Reducing the number of physical reads is an important task for DBAs, but it involves tuning the performance of the entire server, not just the performance of the query. When performing query performance tuning, we cannot control the size of the data buffer or how busy the server is, whether the data required to complete the query is in the data buffer or on disk, and the only data that we can control is the number of logical reads that need to be performed to get the results of the query. Therefore, in the tuning of query performance, we can safely disregard the value of the physical read provided by the set STATISTICS IO command. (One way to reduce the number of physical reads and speed up SQL Server is to make sure that the server has enough physical memory.) ）

expected (Read-ahead Reads): As with physical Reads, this value is not used in query performance tuning. Read-ahead reads represents the physical page that SQL Server reads when it executes a read-ahead mechanism. To optimize its performance, a pre-read data page may or may not be useful, depending on how accurately SQL Server predicts the data needs before SQL Server reads a subset of the data before it needs data.

Reference Documentation:

https://technet.microsoft.com/en-us/library/ms184361 (sql.110). aspx

Http://www.cnblogs.com/jacksonwj/archive/2010/09/20/1831813.html

Http://www.cnblogs.com/laoyumi/archive/2009/09/15/1567323.html

Logical read, physical read (SET STATISTICS IO)