Learn how to read SQL Server execution plans

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)

4.

⑴ 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