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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.