The first thing to say is that the content of this article is not how to adjust SQL Server query performance (about how to write a book), but how to use Set Statistics Io and Set Statistics in SQL Server Query Performance Time These two TRANSACT-SQL commands are often ignored. From the surface, the adjustment of query performance is a very simple matter. In essence, we hope that the running speed of the query can be as fast as possible, whether it is to reduce the time of the query from 10 minutes to 1 minute, or it will be shortened from 2 seconds from 2 seconds to 1 second, our ultimate The goal is to reduce the time of operation. Although there are many reasons for query performance adjustment, this article will only involve one of them, the most important reason is that whenever the environment changes, the performance needs to be adjusted, so it is difficult to figure out How to adjust the performance of the query. If you perform performance surveys on a test-used server as most users, the effect is not very satisfactory, because the environment of the test server is not exactly the same as the actual application server environment. As SQL Server automatically adjusts themselves with continuous changes in resource requirements. If you have any questions about this, you can repeatedly run the same query on a larger server. In most cases, the time to perform the query is not the same. Of course, the gap is not large, but its change is enough to make the performance of the performance more difficult than it should have. What is going on? Is your idea wrong or when running a query? Is this caused by an increase in run time? Although the query can be run multiple times to get an average time, the workload of this is large. We need to compare the performance of each test with a very scientific standard. Measuring Server Resources is to solve the relationship between Query performance adjustment issues to perform a query on the server. One of the resources is the occupancy time of the CPU, assuming that the database does not have any changes, repeatedly runs the same query the occupancy time of its CPU will be very close. Here, I refer to a query from running to the end of the run, but refers to the number of CPU resources required to run this query, and the time required to run a query is related to the busyness of the server. Another resource that SQL Server needs is IO. SQL Server must read data (logically read) from the data buffer in the data buffer, if the required data is not in the buffer, you need to read (physical read) on the disk. From the discussion, the CPU, the more IO resources needed, the slower the speed of the query run, so another way to describe the performance adjustment task is that there should be less CPU, IO The way the resource rewrites the query command. If you can complete the query in this way, the performance of the query will increase. If the purpose of adjusting the query performance is to use as fewer server resources, not the query is the shortest time, so it is easier to test what you take is to improve the performance of the query or the performance of the query. This is especially true for resource utilization of changing servers. First, you need to figure out how to test the resource usage of our server when adjusting the query. Also think of the STATISTICS IO and SET STATISTICS TIME SQL Server, I support the set statistics Io and Set Statistics Time, but because of the other reasons, many DBA (data is System administrators) Ignore them, maybe they are not attractive.
But no matter what reason, we will find that they are still useful in adjusting the performance of query. There are three ways to use these two commands: Use the Transact-SQL command line to use Query Analyzer, set the appropriate connection properties of the current connection in Query Analyzer. In this article, we will use the transact-sql command line to demonstrate their usage. Sit Statistics Io and SET STATISTICS TIME functions like a switch, you can turn off or close our query using resources. These settings are turned off in the default state. Let's take a look at how these orders open an example and see what kind of information they will report. Start Query Analyzer before starting our example and connect to a SQL Server. In this example, we will use the Northwind database and use it as the default database of this connection. Then, run the following query: select * from [Order Details] If you have not changed the ORDER DETAILS, this query returns 2155 records. This is a typical result, I believe that you have seen many times in Query Analyzer. Now let's run the same query, but this time we run the SET Statistics Io and Set Statistics Time command before running the query. What needs to be remembered is that the opening of this two commands is only valid for the current connection. When one or two commands are opened, turn off the current connection and open a new connection, you will need to perform the corresponding command again. If you want to close the two commands in the current connection, just change the ON in the original command to OFF, then execute it once. Before starting our example, first run the following two commands (do not execute on the server being used), this two commands will clear the SQL Server data and process buffers, which allows us to be in each execution query At the same start, otherwise, each time the result is not comparable: DBCC DROPCLEANBUFFERS DBCC FreeProccache Enter and run the following transact-sql command: set statistics Io On Set Statistics Time Once after the preparation work is completed , Run the following query: select * from [ORDER DETAILS] If you run the above command, the output you get will be different from me, it is difficult to figure out what happened. After running the above command, you will see the new information you have previously seen in the result window. There will be the following information on the top of the window:
SQL Server Parse and Compile Time: (SQL Server Analysis and Compilation:) CPU Time = 10 MS, ELAPSED TIME = 61 MS. SQL Server Parse and Compile Time: (SQL Server Analysis and Compiling Time:) CPU Time = 0 ms, ELAPSED TIME = 0 ms.
After the data is displayed, the record obtained by the query will be displayed. After the 2155 record is displayed, the following information is displayed:
Table 'Order Details'. Scan Count 1, Logical Reads 10, Physical Reads 1, Read-ahead Reads 9. (Table: ORDER DETAILS, Scan Saix 1, Logic Read 10, Physics Read 1, Early Read 9) SQL Server Execution Times: (SQL Server Execution Time:) CPU Time = 30 MS, ELAPSED TIME = 387 MS. (Every time the result may vary, we will mention this when we discuss the displayed information.) So, these What is the specific meaning of information? Let's take a detailed analysis. SET Statistics Time's Results Set Statistics Time Command Used to test the running time of various operations, some of which may be no useful for the adjustment of query performance. Run this command can get the following display information on the screen: The start of the output:
SQL Server Parse and Compile Time: CPU Time = 10 MS, ELAPSED TIME = 61 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 = 30 ms, ELAPSED TIME = 387 MS. At the beginning of the output we can see the second test time, but the first line executes the CPU required for a certain operation Time and total time, but the second line seems to be. "SQL Server Parse and Compile Time" Indicates the SQL Server parsing "Elect * from [Order Details] command and put the result of the resolved result into the CPU run time and total time required for SQL Server using the result of SQL Server. . In this example, the CPU's operating time is 10 milliseconds, with a total time of 61 milliseconds. Due to the different server configuration and load, you get the CPU runtime, the total time this two values may differ from the test results in this example. The "SQL Server Parse and Compile Time" of the second line indicates that the SQL Server takes out the parsing result in the process buffer, and the two values will be 0 in most cases, because this process is implemented quite fast. If you do not clear the buffer and run the select * from [order Details] command, the CPU running time and compile time will be 0, because SQL Server will repeat the resolution results in the buffer, so you don't need to compile again. . This information is really big in the adjustment of query performance? Maybe not this, but I will explain the true meaning of this information, you will be very surprised, most of the DBA actually understands the meaning of this information: we are most interested in displaying the last time information: SQL Server Execution Times: CPU TIME = 30 ms, ELAPSED TIME = 387 MS. The information displayed above indicates how much CPU runtime and running query are used to perform this query. The CPU runtime is a relatively stable measurement method for the CPU resource required to run the query, and has no relationship with the CPU's busy level. However, this number will vary when running inquiry, but there is no total time to change. Total time is the time required to perform the query (not calculated to block or reading data). Since the load on the server is constantly changing, the variation range of this data is sometimes quite large. Since the CPU occupancy time is relatively stable, it is a way to use this data as a measure of your adjustment to increase the performance of query performance or the performance of the query.
The output information of the SET STATISTICS IO is displayed at the end of the output. Here is an example of it displayed: table 'Order Details'. Scan Count 1, Logical Reads 10, Physical Reads 1, Read-ahead Reads 9. Part of this information is very useful, other parts, let's take a look at each part and understand its meaning: scan count: The number of tables involved in the query is accessed. In our example, the table is only visited once. Because this information is not very useful because there is no connection command in the query, this information is very useful if there is one or more connections in the query. useful. A SCAN COUNT value of a loop outside is 1, but for the table in a loop, its value is the number of cycles. It can be imagined that the smaller the scan count value is, the smaller the resources it use, 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, when adjusting, pay attention to observation whether it is an increase or decrease. 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 performing any data. In addition, we also know how SQL Server reads data from the data buffer and reads the data to a page with a size of 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 the query is executed, SQL Server does not read data more than or less than actual needs, so when the same query is performed on the same data set, the number of Logical Reads is always the same. Why is it important to know that the Logical READS value when you know the SQL Server execution query? Because the value does not change when each time the same query is executed. Therefore, when the regulation of query performance is performed, this is a good standard that can be used to measure whether your adjustment is successful. When adjusting the performance of the query, if the Logical READS value decreases, it indicates that the server resources used by the query are reduced, and the performance of the query is improved. If the Logical Reads value is increased, the adjustment measures reduce the performance of the query. In the case where other conditions are constant, the less logical read, the higher the efficiency, the faster the query speed. Physical Reads: The thing I want to say here may be a little contradictory, but as long as you think repeatedly, you will understand the true meaning. Physically read, SQL Server must read the data it needs to the data buffer from the disk before performing the true query operation. Before SQL Server starts executing the query, the first thing it wants is to check if the data it needs is in the data buffer, if, it is read, if not, SQL Server must first put the data it needs it first. Read from the disk to the data buffer. We can imagine that SQL Server is performing more server resources than performing logical reading than executing physical read. Therefore, in the ideal case, we should try to avoid physical read operations. The part of the following sounds make people feel confused.
