SQL Server Performance Optimization auxiliary indicator set STATISTICS time on and set STATISTICS IO on

1. Preface

For the optimization of SQL statements or stored procedures, the former mainly use the following statements to determine the specific execution time, but the SQL environment is complex and changeable, the following statement does not accurately determine whether the performance is improved, if you need to know the CPU, IO and other information, there is nothing to do.

PRINT CONVERT (varchar (+), GETDATE (), 121) SELECT * from Sales.SalesOrderDetail where SalesOrderID > 64185PRINT convert (varchar (), GETDATE (), 121)

At this point, if you use the set STATISTICS time on and set STATISTICS IO on command, you will know that you need to execute the following 2 commands before testing

　　DBCC dropcleanbuffers Clear Buffer

　　DBCC freeproccache Deleting elements in the plan cache

2. Testing

2.1 First executes the following script

--Turn on statistics set STATISTICS time ONSET STATISTICS IO ongoselect * from Sales.SalesOrderDetail where SalesOrderID > 64185GO

The results are as follows

--1.sql Server Analysis and compile time:    CPU time = 0 milliseconds, elapsed time = 53 milliseconds. --2.sql Server Analysis and compile time:    CPU time = 0 milliseconds, elapsed time = 0 milliseconds. (35292 rows affected)--3. Table ' SalesOrderDetail '. Scan count 1, logical read 337 times, physical read 4 times, read 333 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. --4. SQL Server Execution time:   CPU time = 47 milliseconds, elapsed time = 893 milliseconds.

　　

Description

Tag 1: Indicates the CPU time and total time required to put the result of the statement into the SQL buffer

Tag 2: Identifies the time required to remove the parse result from the buffer

Tag 4: Identify how much CPU time and total time the query uses, where CPU time is a relatively stable measure of the CPU resources required for the query; The total time is related to SQL Server, so it is more unstable, so performance judgment can be done by CPU time.

Tag 3: Resource time, where logical reading refers to the data that SQL reads from the buffer, and physical reading refers to reading from the disk into the buffer from the data;

2.2 Run the query again results as follows, because the first time the data has been read from the disk to the buffer, so the time to mark 1 is 0, the tag 3 physical read is 0.

--1.sql Server Analysis and compile time:    CPU time = 0 milliseconds, elapsed time = 0 milliseconds. --2.sql Server Analysis and compile time:    CPU time = 0 milliseconds, elapsed time = 0 milliseconds. (35292 rows affected)--3. Table ' SalesOrderDetail '. Scan count 1, logical read 337 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. --4. SQL Server Execution time:   CPU time = 32 milliseconds, elapsed time = 848 milliseconds.

2.3 in the optimization of SQL statements can be from the CPU time, the number of logical reads to determine whether performance is improved, and these 2 indicators are more realistic reflection of SQL execution. Here's a brief introduction to some of the basic information about these 2 commands, which requires a deeper understanding of SQL underlying knowledge.

SQL Server Performance Optimization auxiliary indicator set STATISTICS time on and set STATISTICS IO on