CPU time, elapsed time

When we use SET STATISTICS TIME on to view the execution of the SQL statement, the SQL Sever returns us two time, one is CPU times and the other is elapsed time. So what's the difference between the two times?

CPU time is when the CPU is busy executing the current task, and it does not consider the wait time, such as IO wait, network wait, and elapsed time is the total amount spent in performing the current task, that is, the

Ask the shutdown system can be expressed as: Elapsed time = CPU time + waiting time but in the case of multi-core processors, because more than one CPU simultaneously processing the task, it is possible to have more than Elapsed

The first thing to note is that the content of this article is not how to adjust SQL Server query performance (to write a book about this), but how to use set STATISTICS IO and set STATISTICS in the tuning of SQL Server query performance Time these two Transact-SQL commands that are often ignored.

On the surface, the adjustment of query performance is a very simple matter. Essentially, we want the query to run as fast as possible, either by reducing the query's running time from 10 minutes to 1 minutes or by shortening the run time from 2 seconds to 1 seconds, and our ultimate goal is to reduce the running time.

Although there are many reasons for the difficulty of query performance tuning, this article will only cover one of these areas, the most important reason is that whenever the use of the environment changes, you need to adjust performance, so it is difficult to figure out how to adjust the performance of the query.

If you perform a performance survey on a test server like most users, the effect is often not very satisfying, because the environment of the test server is not exactly the same as the server environment in which it is actually applied. As the resource requirements evolve, SQL Server automatically adjusts itself.

If you have questions about this, you can run the same query repeatedly on a heavily loaded server, and in most cases, the time used to execute the query is different. Of course, the gap is not big, but the change is enough to make performance adjustment more difficult than it should be.

What the hell is going on here? Is your idea wrong or is the server overloaded when you run the query. Is this the cause of the increase in running time? Although it can take an average of time to run a query repeatedly, it's a lot of work. We need to compare the performance of each test with a very scientific standard.

Measuring server resource is the key to solve query performance regulation problem

There are a number of server resources that are used when executing queries on the server. One of these resources is CPU time, assuming that the database has not changed any, repeatedly running the same query its CPU time will be very close. Here, I'm not talking about a query from run to finish, but rather the amount of CPU resources needed to run the query, and the time it takes to run a query is related to how busy the server is.

Another resource that SQL Server needs is IO. Whenever a query is run, SQL Server must read the data (logical reads) from the data buffer, and if the required data is not in the buffer, it needs to be read on disk (physical reading).

As you can see from the discussion, the more CPUs and IO Resources A query requires, the slower the query runs, so another way to describe the query performance tuning task is to rewrite the query command in a way that uses fewer CPUs, IO resources, and if you can complete the query in such a way, The performance of the query can be improved.

If the purpose of tuning query performance is to make it use as few server resources as possible, rather than the shortest time the query runs, it is easier to test whether you have improved the performance of the query or decreased the performance of the query. This is especially true for servers with ever-changing resource utilization. First, you need to figure out how to test the resource usage of our servers when tuning the query.

And I think of the set STATISTICS IO and set STATISTICS time

SQL Server has long supported the two Transact-SQL commands for set STATISTICS IO and set STATISTICS time, but for some other reason, many DBAs (data for system administrators) ignore them when tuning query performance. Maybe they are not attractive. But whatever the reason, we'll find that they're useful for tuning query performance.

There are three ways to use these two commands: using the Transact-SQL command-line, using Query Analyzer, setting the appropriate connection properties for the current connection in Query Analyzer. In this article, we will use the Transact-SQL command line to demonstrate their usage.

The set STATISTICS io and set STATISTICS Time act as switches to turn on or off the various reporting information for our queries using resources. By default, these settings are turned off. Let's first look at an example of how these commands are opened and see what information they will report.

Before starting our example, start query Analyzer and connect to a SQL Server. In this case, we'll use the Northwind database and make it the default database for this connection.

Then, run the following query:

SELECT * FROM [Order Details]

If you haven't changed the Order Details table, this query will return 2,155 records. This is a typical result, and I'm sure you've seen it several times in Query Analyzer.

Now we're going to run the same query, but we'll run the set STATISTICS IO and set STATISTICS time command first before we run the query. Remember that these two commands are open only to the current connection, and once you open one or two of them, and then close the current connection and open a new connection, you need to execute the appropriate command again. 　　If you want to turn off the two commands in the current connection, you can do this once by switching on the on in the original command. Before starting our example, run the following two commands (do not execute on the server you are using), and these two commands will clear the data and process buffers of SQL Server so that we can be on the same starting point each time we execute the query, otherwise, The results from each execution of the query are not comparable:
DBCC dropcleanbuffers

DBCC Freeproccache

Enter and run the following Transact-SQL command:

SET STATISTICS IO on

SET STATISTICS time on

Once the above preparation is complete, run the following query:

SELECT * FROM [Order Details]

If you run all of the above commands at the same time, the output you get will be different from mine, and it's hard to figure out what's going on.
After you run the above command, you will see new information in the results window that you haven't seen before, and at the top of the window, you'll have the following information:

SQL Server parse and compile time: (SQL Server parsing and compilation times:)
CPU time = Ten ms, elapsed time = Ms.
SQL Server parse and compile time: (SQL Server parsing and compilation times:)
CPU time = 0 ms, Elapsed time = 0 Ms.

When the above data is displayed, the records from the query are displayed. After the 2,155 records are displayed, the following information is displayed:

Table ' Order Details '. Scan count 1, logical reads, physical reads 1, Read-ahead reads 9.
(Table: Order Details, scanning times 1, logical reading 10, physical reading 1, read in advance 9)
SQL Server Execution times:
(SQL Server Execution time:)
CPU time = m ms, Elapsed time = 387 Ms.

(each result may be different, as we discuss the information shown below.) ）

So, what is the specific meaning of this information? Let's go through the analysis in detail below.

Result of SET STATISTICS time

The SET STATISTICS time command is used to test the run-time of various operations, some of which may not be useful for tuning query performance. Run this command to get the following display information on the screen:

At the beginning of the output:

SQL Server parse and compile time:
CPU time = Ten ms, elapsed time = Ms.
SQL Server parse and compile time:
CPU time = 0 ms, Elapsed time = 0 Ms.


At the end of the output:

SQL Server Execution times:

CPU time = m ms, Elapsed time = 387 Ms.

At the very beginning of the output we can see two test times, but the first line takes the CPU time and total time required to perform an operation, but the second line does not seem to be.

SQL Server parse and compile time indicates that SQL Server resolves the elect * from [Order Details] command and puts the resolved results into the procedure buffer of SQL Server for SQL Server to use the required The CPU run time and the total time to be needed.

In this case, the CPU runs for 10 milliseconds, and the total time is 61 milliseconds. Because the server is configured and loaded differently, the two values you get for CPU uptime and total time may differ from the test results in this example.

The second row of "SQL Server parse and compile time" means that SQL Server takes out the resolution results from the process buffer for execution, and in most cases the two values will be 0, because the process executes fairly quickly.

If you run the SELECT * from the Order Details command again without clearing the buffer, the CPU run time and compile time will be 0, because SQL Server will reuse the parsed results in the buffer, so there is no time to compile again.
Does this information help you a lot in the tuning of query performance? Maybe not, but I'll explain what this information really means, and you'll be surprised that most DBAs don't really understand the implications of this information:

What we are most interested in is displaying the last time information in the output:

SQL Server Execution times:

CPU time = m ms, Elapsed time = 387 Ms.

The information shown above shows how much CPU uptime was used to execute the query and how much time was used to run the query. CPU uptime is a relatively stable measurement of the CPU resources needed to run the query, and is not related to the CPU's free and busy level. However, this number will vary each time the query is run, except that the range of changes is not as large as the total time change. The total time is the time required to execute the query (without calculating the amount of time to block or read data), and because the load on the server is constantly changing, the range of data is sometimes quite large.

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

The effect of SET STATISTICS IO

The output information for SET STATISTICS io is displayed at the end of the output, and here is an example of what it shows:

Table ' Order Details '. Scan count 1, logical reads, physical reads 1, Read-ahead reads 9.

Part of this information is very useful, and the other part is not, let's look at each section and understand its implications:

Scan Count: The number of times the table that is involved in the query is accessed. In our example, the table is only accessed 1 times, and since the query does not include a connection command, this information is not very useful, but this information is useful if the query contains one or more connections.

The scan count value of a table outside a loop is 1, but the number of loops is the value of a table within a loop. As you can imagine, the smaller the scan count value for a table within a loop, the less resources it uses, and the higher the performance of the query. Therefore, when adjusting the performance of a query with a connection, you need to pay attention to the value of scan count, and when adjusting, notice whether it is increasing or decreasing.

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 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 to a page of size 8K.

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. SQL Server does not read more or less data than actual requirements when executing queries, so the number of logical reads is always the same when executing the same query on the same dataset.

Why is it important to know the logical reads value of SQL Server execution queries in tuning query performance? Because this value does not change each time the same query is executed. As a result, this is a good standard to measure the success of your regulatory measures when you are tuning your query performance.

When the performance of the query is adjusted, if the logical reads value drops, it indicates that the server resources used by the query have been reduced and the query performance has been improved. If the logical reads value increases, the adjustment measures reduce the performance of the query. With other conditions unchanged, a query uses less logical reads and is more efficient, and the query is faster.

Physical reads: What I'm going to say here may sound paradoxical at first, but as long as you think about it, you'll understand the real meaning.

Physical reading means that SQL Server must read the data it needs from disk to the data buffer before performing a true query operation. Before SQL Server starts executing a query, the first thing it does is check that the data it needs is in the data buffer, read from it if it is, and if not, SQL Server must first read the data it needs from disk to the data buffer.

As we can imagine, SQL Server requires more server resources to perform physical reading than to perform logical reads. Therefore, in an ideal situation, we should try to avoid physical read operations.

This part of the following sounds easy to be confused. When you adjust the performance of a query, you can ignore physical reading and focus only on logical reading. You're going to wonder, didn't you just say that physical reading requires more server resources than logical reading.

As it is, the number of physical reads that SQL Server needs to perform the query cannot be reduced through 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 tuning of query performance. When querying performance tuning, we cannot control the size of the data buffer or the server's busyness and whether the data needed to complete the query is in the data buffer or on disk, the only data we can control is the number of logical reads to be performed on the query results.

Therefore, in the tuning of query performance, we can comfortably disregard the physical read value provided by the set STATISTICS IO command. (One way to reduce the number of physical reads and speed up SQL Server is to ensure that the server has enough physical memory.) ）

Read-ahead reads: Like physical reads, this value does not have any users in the query performance tuning. Read-ahead reads represents the physical page that SQL Server reads when it performs a read-back mechanism. To optimize its performance, a read-ahead data page may or may not be useful, depending on the accuracy of SQL Server's forecast of data requirements, before SQL Server can read a subset of the data before it thinks it needs data.

In this case, the value of Read-ahead reads is 9,physical Read with a value of 1, and the logical reads value is 10, and there is a simple additive relationship between them. So what's the process when I execute a query on the server? First, SQL Server begins to examine whether the data needed to complete the query is in the data buffer, it quickly discovers that the data is not in the data buffer, and initiates the read mechanism to read the first 9 of the 10 data pages it needs to the data buffer. When SQL Server checks that all of the data needed is already in the data buffer, it finds that there are 9 data pages in the data buffer and one is not, and it reads the disk again and reads the required pages into the data buffer. Once all the data is in the data buffer, SQL Server can process the query.

What should we do.

I have mentioned at the beginning of this article that it is important to use some scientific criteria to measure the effectiveness of your regulation when tuning the performance of a query. The problem is that the load on the SQL servers is dynamic, and it is not a reasonable way to measure whether the query's performance is improved or not, using the total elapsed time of the query.

A better approach is to compare multiple data, such as the number of logical reads or the CPU time used by the query. Therefore, when tuning query performance, you need to first use the set STATISTICS IO and set STATISTICS time commands to provide you with the necessary data to determine whether the measures you have to adjust the performance of the query really get the purpose

--This article is from: CPU time@dlapsed time difference