SQL query performance debugging. Use set statistics Io and set statistics time-to explain it in more detail.

The more CPU and IO resources a query requires, the slower the query speed. Therefore, another way to describe the query performance tuning task is, the query command should be rewritten in a way that uses less CPU and IO resources. If the query can be completed in this way, the query performance will be improved. If the purpose of tuning query performance is to make it use as few server resources as possible, rather than the minimum query Run Time, therefore, it is easier to test whether the query performance is improved or reduced. This is especially true for servers with constantly changing resource utilization. First, we need to figure out how to test the resource usage of our servers when adjusting queries.
Before starting our example, run the following two commands (do not execute them on the server in use). These two Commands will clear the data and process buffer of SQL Server, this allows us to execute a query at the same starting point each time. Otherwise, the results of each query execution will not be comparable:
DBCC dropcleanbuffersAnd DBCC freeproccache
Enter and run the following Transact-SQL command:
Set statistics Io on
Set statistics time on
Once the preceding preparation is complete, run the following query:
Select * from [Order Details]

Display result:

SQL Server parse and compile time: (SQL Server Parsing and Compilation Time :) CPU time = 10 MS, elapsed time = 61 Ms ....... (1) SQL Server parse and compile time: (SQL Server Parsing and Compilation Time :) CPU time = 0 MS, elapsed time = 0 Ms ....... (2) (The number of affected rows is 2155 )...... (3) Table 'order details'. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 9. (Table: Order details, scanning count 1, logic read 10 times, physical read 1 time, pre-read 9 times )...... (4) SQL Server execution times: (SQL Server execution time :) CPU time = 30 MS, elapsed time = 387 Ms ....... (5)

Logo (1) it indicates the CPU running time and total time required by SQL Server to parse the "elect * from [Order Details]" command and put the parsed result in the SQL server process buffer.
Sign (2) indicates the time when SQL Server extracts the parsing result from the process buffer for execution. In most cases, the two values are 0, because the process is executed quite fast.
Sign (5) indicates the CPU running time used for executing this query and the running query time. The CPU running time is a relatively stable Measurement Method for the CPU resources required to run the query. It has nothing to do with the idle CPU usage. However, this number varies each time you run a query, but the change range does not have a large change in the total time. The total time is the time required for query execution (no blocking or data reading time is calculated). Because the server load is constantly changing, therefore, the scope of this data change is sometimes quite large. (Because the CPU usage time is relatively stable, you can use this data as a way to measure whether your adjustment measures increase the query performance or reduce the query performance .)
Mark (4) is the effect of set statistics Io

Scan count: Number of times the table involved in the query is accessed. In our example, the table is only accessed once. This information is not very useful because the query does not include the connection command, however, this information is useful if the query contains one or more connections. (The scan Count value of a table outside a cycle is 1, but for a table in a cycle, its value is the number of cycles. As you can imagine, the smaller the scancount value of a table in a loop, the fewer resources it uses, and the higher the query performance. Therefore, when adjusting the performance of a query with a connection, you need to pay attention to the value of scan count. When adjusting the performance, observe whether it increases or decreases .)

Logical reads:This is the most useful data provided by the set statistics Io or set statistics time command. We know that before SQL server can operate on any data, it must first read the data to its data buffer. In addition, we also know when SQL server will read data from the data buffer and read the data to a page of 8 K bytes. So what does logicalreads mean? Logical reads refersNumber of pages that SQL server must read from the data buffer for query results. When a query is executed, SQL server does not read more or less data than the actual requirement. Therefore, when the same query is executed on the same dataset, the resulting logicalreads numbers are always the same. (The value of logical reads does not change every time the SQL server executes the query. Therefore, when tuning the query performance, this is a good standard for measuring whether your adjustment measures are successful. If the logical reads value drops, it indicates that the server resources used for query are reduced, and the query performance is improved. If the logical reads value is increased, the adjustment measures reduce the query performance. When other conditions remain unchanged, the less logical reads a query uses, the higher the efficiency and the faster the query speed .)

Physical reads: Physical read. Before performing a real query operation, SQL Server must read the required data from the disk to the data buffer.. Before SQL server starts executing a query, the first thing it needs to do is to check whether the data it needs is in the data buffer. If it is in, it will read the data from it. If it is not, sqlserver must first read the required data from the disk to the data buffer. As we can imagine, SQL server requires more server resources when performing physical reads than executing logical reads. Therefore, we should avoid physical read operations as much as possible. The following part sounds confusing. When tuning the query performance, you can ignore physical reads and focus only on logical reads. You will be bored. Didn't you say that physical reads require more server resources than logical reads? This is indeed the case. The number of physical reads required by sqlserver during query execution cannot be reduced through performance adjustment. Reducing the number of physical reads is an important task of DBA, but it involves adjusting the performance of the entire server, not just the query performance. When adjusting the query performance, we cannot control the size of the data buffer, the server's busyness, and whether the data required to complete the query is in the data buffer or on the disk, the only data we can control is the number of logical reads required to obtain the query results. Therefore, in the query performance adjustment, we can safely ignore the physicalread value provided by the set statistics Io command. (One way to reduce the number of physical reads and speed up the running of SQL Server is to ensure that the physical memory of the server is sufficient .)

Read-ahead reads:Like physical reads, this value is useless in query performance tuning.Read-ahead reads indicates the physical page that the SQL Server reads when executing the pre-read mechanism.To optimize its performance, SQL Server reads a part of data in advance before considering that it needs data. Based on the Accuracy predicted by SQL Server for data requirements, pre-read data pages may be useful or useless. In this example, the value of read-ahead reads is 9, the value of physical read is 1, and the value of logicalreads is 10. There is a simple addition relationship between them. When I run a query on the serverProcessWhat is it like? First, SQL Server checks whether the data required to complete the query is in the data buffer. It will soon find that the data is not in the data buffer, the pre-read mechanism is enabled to read the first nine of the 10 Data Pages required by the system to the data buffer zone. When SQL Server checks whether all the required data is already in the data buffer, it will find that nine data pages are already in the data buffer, and one is not, it will immediately read the disk and read the required pages to the data buffer zone. Once all the data is in the data buffer, sqlserver can process the query.

 

Conclusion: It is very important to use scientific standards to measure the effectiveness of your adjustment measures when adjusting the query performance. The problem is that the load of SQL servers is dynamically changing. It is not a reasonable way to use the total running time of the query to measure whether the query performance you are tuning is improved or not. A better way is to compare multiple data items, such as the number of logical reads or the CPU time used for query. Therefore, to adjust the query performance, you must first use the set statistics Io and set statistics time commands to provide you with the necessary data, in order to determine whether your measures to adjust the query performance are actually achieved.

======================================
1. Run the following two commands to clear SQL server data and process buffers before the test to ensure that the test conditions are the same:
DBCC dropcleanbuffers and DBCC freeproccache
2. Set statistics time:View CPU time
3. Set statistics IO:Follow scan count(Count) ------ query the number of tables read;Number of logical reads
======================================

 

PS: The test confirms that the table connection conditions in sqlserver 2005 will be automatically optimized. However, to develop good habits and consider the performance of other databases, we need to continue to maintainThe habit of placing large table join fields on the left.