Learn how to read SQL Server execution plans

Source: Internet
Author: User

1. There are two ways to see the execution plan, press CTRL+L on the SQL statement, or press Ctrl+m to open the Show Execution Plan window each execution of SQL will show the corresponding execution plan

2. The chart of the execution plan is viewed from right to left

3. SQL Server has several ways to find data records

[Table Scan] table scan (slowest), check table records line by row

[Clustered Index Scan] clustered index Scan (slower), record-by-row checks on a clustered index

[Index Scan] Index filter scan (normal), according to the index filter out part of the data in the progressive check

[Index seek] index lookup (faster), retrieve records based on index location

[Clustered Index seek] clustered index lookup (fastest) to fetch records directly from a clustered index

(If some SQL execution is slow, you can see if the execution plan contains too many "scan" operations, if you have an index that you can consider for these fields, make sure that you do not always have the update operation on the field established, each update will cause the operation to rebuild the index, will also affect performance, 0 or 1 This identifies the status of the field because most of the data is the same index does not make any difference

(Two indexes, 1 is a clustered index, 2 nonclustered indexes, a clustered index can have only one per table, a nonclustered index each table may have more than one, the primary key ID is a typical clustered index, the clustered index is similar to the dictionary lookup pinyin A, B, c ...) and the dictionary text content order is the same, the nonclustered index and the content is non-sequential arrangement, similar to the dictionary component search, the same radical ' horse ' kanji may one on the 10th page one on the 100th page)


⑴ table query with no primary key

⑵ table query with primary key

⑶ Table Query to establish a nonclustered index

⑷ creating a nonclustered index and adding other display columns to the index

⑸ Create a nonclustered index and add other display columns to the index and treat the clustered index column as a condition

Finally make sure that SQL is executed as much as possible under Index Scan search!

Learn how to read SQL Server execution plans

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.