The role of Set STATISTICS IO and set STATISTICS time in SQL Server query performance optimization

Original: Set STATISTICS io and set STATISTICS time in SQL Server query performance optimization

In recent times, has been exploring the SQL Server query performance issues, of course, also aimless search for a lot of information, but also from the online great God's articles learned a lot, here, to the great God salute. It is by the selfless dedication of the great God, so the younger brother also in return, share about the set STATISTICS io and set STATISTICS time these two t_sql commands, in the query optimization performance of the role.

First I would like to explain that this article is not about how to optimize SQL Server query performance, because there is too much content, too complex. In addition to looking at a lot of data on performance optimization (in this article, which refers to query performance), it is found that almost all of them use execution time as an optimization indicator, but it is not appropriate to use execution time as an indicator of performance. Of course, our ultimate goal of optimizing query statements is to reduce query time.

cause The reason why the query time is not accurate, there are the following two aspects:

1.SQL server adjusts itself as the server resources change.

Because we usually test the environment of the server and the actual server is not exactly the same. For example, we test repeatedly on a server that is heavily loaded. You will find that the time of execution is not the same, of course, the gap is not big, but this gap is enough to make our performance adjustment much more difficult. Of course you can do it over and over again, but on a heavily loaded server, what kind of scientific criteria do you need to determine the average execution time?

2.SQL server to read the data, there is no in the buffer.

Because SQL Server must read from the data buffer every time it reads data, this is also called a logical read. If the data to be read is not in the data buffer, it will be read from the physical disk (physical read).

All two of these reasons affect the time it takes to execute a query statement.

Having said that much, what should we use as a standard for performance optimization?

1.CPU occupancy time.

When a database executes a query statement, it uses many of the server's resources. One of the resources is the CPU time, if the database has not changed any changes, repeatedly run the same query, CPU time is very close.

The number of 2.IO operations.

There are many indicators of IO operations, which are described in more detail below.

As described above, we know that the less CPU and IO Resources A query requires, the better the performance will be. If we refine the query according to this criterion, it is easy to tell if your optimization is reducing performance or improving performance. Think of this, then how can we see my server resource usage? This is when we think of set STATISTICS io and set STATISTICS time (previous content even if I suspense ah, hehe)

Set statistic IO and set statistic time are like many t_sql statements that belong to the switch command (their own name, which is on and off with on and off). The default state is off. Now we're going to start using the two commands and look forward to it.

In this example, we used the previously built test database, using the person table

(a) First we use Set STATISTICS time

1. First, in order for each execution to be at the same starting point, we use the following two commands to clear the SQL Server data and process buffers, otherwise the results of the query executed will not be comparable.

DBCC dropcleanbuffers

DBCC Freeproccache

2. Execute set statistic time on to open the Cup statistics report

After these preparations are completed, we can execute the following query:

SELECT * FROM person where id=50000

After executing the above command, you can get the following information in the Messages tab

SQL Server parse and compile time: CPU time = 0 milliseconds, elapsed time = 20 milliseconds.

SQL Server parse and compile time: CPU time = 0 milliseconds, elapsed time = 0 milliseconds.

(1 rows affected)

SQL Server Execution Time: CPU time = 235 milliseconds, elapsed time = 1508 milliseconds.

Let's take a detailed analysis of the meaning of these time messages.

The first SQL Server parse and compile time refers to the query statement that parses the "select * from the person where id=50000" and puts the parsed results into the procedure buffer, which is used by SQL Server for the CPU run time and the total time.

The second SQL Server parse and compile time refers to the time that the parse result is taken out of the procedure buffer and executed. This time will be very fast.

If you run "select * from the person where id=50000" without emptying the buffer next, you will find that SQL Server parse and compile time is 0 because SQL Server will use the parse result in the buffer directly. Therefore, compile time is not required.

The third "SQL Server execution Time" will be our most interesting time, which is how much CPU run time is used to execute the query and how much time is 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 is small. 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.

Because CPU time is relatively stable, you can use this data as a measure of whether your optimization measures improve query performance or reduce query performance.

(b) Next we use Set STATISTICS IO

As with the preparatory work above, when we finish "select * from person where id=50000", we see the following information in the Message tab:

(1 rows affected)
Table ' Person '. Scan Count 5, logical read 10,418 times, physical read 105 times, read 10,418 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

Some of this information is very important, and the other part is not to be considered.

Number of scans: the number of times the tables involved in the query were accessed. In our case, the person table is accessed only 5 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.

Logical reads: This is the most useful data. We know that SQL Server must first read the data into the 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. Logical read acquisition means the number of pages that SQL Server must read from the data buffer to obtain the results of the query.

When SQL Server performs a logical read, it does not read more or less data than the actual result, so the same query is executed in the same dataset, and the resulting logical reads are always the same. Therefore, the value of logical reading in query optimization is a good criterion to measure whether your optimization measures are feasible. (The less logical reading at query time, the higher the efficiency, the faster the query, and the slower it is)

Physical read: Before performing a real query operation, SQL Server must read the data he needs from disk to the data buffer. Before SQL Server starts executing a query, when it discovers that the data to be read is no longer in the data buffer, it first reads the data it needs into the data buffer. Physical reading means the number of pages of data read from a physical disk when SQL Server reads the required data into the data buffer.

Unfortunately, when we do query optimization, we do not need to consider physical reading. Although physical reads are likely to require more server resources than logical reads. Because SQL Server does not perform a query, it is not possible to reduce the number of physical reads by performance tuning. Reducing physical reads is a complex and important task that involves tuning the performance of the entire server, not just the performance of the query. In Query performance tuning, we cannot control the size of the data buffer or the busy server, and the data required to complete the query is in the data buffer or on disk, the only thing we can control is to get the query results more than the number of logical reads to be performed. So when it comes to query optimization, we don't have to worry about physical reading data.

Read-ahead: refers to SQL Server predicting which data pages to read before querying optimization, which may or may not be useful depending on the accuracy of the read-ahead. As with physical reading, we do not need to consider the query optimization.

The remaining "lob logic reads 0 times, the LOB is physically read 0 times, the LOB pre-read 0 times" means the same as before, only in the increase, delete, change the operation of IO resources.

So, when we do query optimization, using set STATISTICS time and set STATISTICS IO is a good choice.

Set STATISTICS io and set STATISTICS the role of time in SQL Server query performance tuning