SQL Statistics Explained

[SQL Basics] Interpretation of statistical information

In the usual optimization of SQL, the longest use is: SET STATISTICS on, it can be used to see how we write query statements exactly how performance, but what is the performance of the indicators? The first thing to understand is the meaning of the data.

The following explanations are from MSDN (click to view)

Output Items	meaning
Table	The 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 read from disk.
Read-ahead Reads	The number of pages to put in the cache for querying.
LOB logical Reads	text,ntext,image , or large value types read from the data cache (varchar (max),nvarchar (max), The number of varbinary (max)pages.
LOB physical Reads	The number of text,ntext,image , or large-value type pages read from disk.
LOB Read-ahead Reads	The number of text,ntext,image , or large-value type pages placed in the cache for querying.

The following explanation comes from a master in the garden, hehe (click to view the original)

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.

SQL Statistics Explained