How query optimization uses SQL statements to view performance metrics for queries

One, SET STATISTICS IO (about the amount of disk activity generated by the TSQL statement query)

--Displays information about the amount of disk activity generated by Transact-SQL statements

SET STATISTICS IO on

--close information about the amount of disk activity generated by Transact-SQL statements

SET STATISTICS IO OFF

The information displayed is as follows:

(The SQL statement is: SELECT * from note500)

which

Scan count: The number of times a table is accessed in a query;

Logical reads: The number of pages of data read from the data buffer;

Physical reads: The number of pages of data read from the physical disk to buffer;

Read-ahead: The number of pages of data read from the physical disk to buffer according to the execution plan;

for the first query in general, there will be a relationship: logical read = physical read + pre-read (the specific link, as already mentioned in the previous blog post, it is no longer described in detail (the article is called "query optimization" MSSQL query execution Process ))

Similarly, the subsequent LOB logical reads, physical reads, and read-ahead concepts are understood to be similar, but only when the corresponding table is updated or inserted.

For scan count, the query with slices is not connected, so it makes little sense. However, if a connection query, especially a circular query, such as self-connection, if the number of cycles, the more the number of scans, it will make the query less efficient. This is the scan count is a more important performance embodiment parameter.

In the case of logical reads, data is read into the buffer due to any manipulation of the data in SQL Server, and the lower the performance of the query if the more pages are read logically. For this reason, logical reads are generally an important parameter of query performance.

Second, SET STATISTICS time (SQL Server parsing and compilation times)

The information displayed on the polygon shows how much CPU run time was used to execute the query and how much time was used to run the query. CPU uptime is a relatively stable measure of the CPU resources needed to run queries, and it has nothing to do with the CPU's level of free time. However, this number will be different each time the query is run, except that the range of changes does not vary greatly over time. The total time is the amount of time that is required to execute the query (no time to block or read data), and because the load on the server is constantly changing, the range of this data can sometimes be quite large.

In general, the performance of a query statement can be compared in several parameters in a quantitative way:

1, CPU time. Compare the CPU resource time to be consumed by the query;

2, I/O. The number of circular scans and the amount of data logically read can be compared.

How query optimization uses SQL statements to view performance metrics for queries