"MS SQL" analyzes SQL performance by execution plan

Original: "MS SQL" analyzes SQL performance by execution plan

How to know the execution efficiency of a SQL statement, only know the following 3 kinds:

1. Analysis of disk activity volume (IO) information through SQL statement execution:set STATISTICS io on (ON)/ set STATISTICS io off (off)

2. The time consumed by parsing, compiling, and executing the SQL statement when it is executed:set STATISTICS times on (open)/ set STATISTICS -off (off)

3. View by Execution plan: Ctrl + L

--------------------------------------------------------------------------------------------------------------- -------------------------------------

First there is a table, the table data is not many, so the execution results may not be too obvious ...

1, first execute set STATISTICS IO on & set STATISTICStime on;

2. Execute the query statement, select the Message Options page, and for easy viewing, use the aggregate function, GROUP by, and ORDERby:

"We did not query the table: worktable, why there is a worktable record, the most most in the back of the note." 】

3. Press CTRL + L key combination to view execution plan:

==================================================================================

How to view the design of a table or whether the SQL statement is optimized, this way to execute the same SQL statement after indexing, to execute the result.

==================================================================================

1. Build the index. (built for test performance, not necessarily for real-world scenarios)

2. The time spent executing the statement after the index is established:

"There is no record of worktable on this side, most most of them are noted." 】

3. Press CTRL + L key combination to view execution plan:

Do you feel that there is no change? is a comparison of the execution effectiveness of the same SQL statement without indexing and indexing =====

The results are not obvious due to the low test data.

However, you can tune SQL performance by mastering parameter changes in IO read, time overhead, and execution plan.

==============================================================

"Worktable" on the MSDN Note:

the relational engine may need to generate a worksheet to perform The logical operations specified in the SQL statement. A worksheet is an internal table used to hold intermediate results.

some GROUPby,ORDER by, or UNION A worksheet is generated in the query.

For example, if the order by clause references a column that is not covered by any index, the relational engine may need to generate a worksheet to sort the result set in the order requested.

worksheets are sometimes also used as a spool to temporarily save results from a partial query plan. The worksheet is generated in tempdb and is automatically deleted when it is no longer needed.

"MS SQL" analyzes SQL performance by execution plan