"SQL Advanced" 03. Plan of Execution Tour 1-preliminary exploration

It is always a fear of the lack of knowledge that Daniel says the plan is executed, so it is necessary to learn to implement the plan to reduce the fear of this piece of knowledge, and here is the first lecture on the implementation plan-Understanding the implementation plan.

This series of "T-SQL" is mainly for T-SQL summary.

SQL Basics

"T-SQL Basics" 01. Single-Table query-several SQL query questions

"T-SQL Basics" 02. Join Query

"T-SQL Basics" 03. Subqueries

"T-SQL Basics" 04. Table Expression-Previous

"T-SQL Basics" 04. Table Expression-Next

"T-SQL Basics" 05. Set operation

"T-SQL Basics" 06. Perspective, inverse perspective, grouping set

"T-SQL Basics" 07. Data modification

"T-SQL Foundation" 08.30-minute Full parse-sql transaction + Isolation level + blocking + deadlock (recommended +165)

"T-SQL Basics" 09. Programmable objects

SQL Advanced

"SQL Advanced" 01. Easy-to-use SQL tvp~~ exclusive [add-delete-change-check] Example

"SQL Advanced" 02. Understanding the underlying principles of SQL queries (recommendation +5)

"SQL Advanced" 03. Execution Plan Tour 1-initial exploration (latest)

SQL Performance Tuning

"SQL Performance Tuning" 01.TempDB usage and performance issues

Keep updating ... Welcome to follow me!

First, Why do I need to execute a plan? (1) Help analysis

When we want to analyze the slow existence of SQL statements, we need an analysis tool to help us analyze where performance issues exist in the SQL statement, and this analysis tool is the execution plan, and the execution plan will know where the performance problem is. Then combine your existing SQL knowledge to analyze why these areas have performance problems, and then try to propose solutions and test whether your solution improves performance and whether the scenario is reasonable.

(2) For additional information

A. Which indexes are used in the query

B. How the data is linked

C. How the data is retrieved

D. Why SQL Server does not use these indexes

Order of execution of E.SQL statements

second, what is the implementation plan?

Before the SQL statement executes, there is a scenario that needs to be executed, which is generated by the query optimizer (Query Analyzer) and is an efficient, least expensive scenario, which is the execution plan. I do not know the query optimizer can read before I write a blog:

the T-SQL Advanced ". Understanding SQL the underlying principle of the query

third, how to display the implementation plan?

The execution plan has three formats: graphical execution plan, textual execution plan, and XML format execution plan.

(1) Graphical execution plan

Pros: good visibility.

A. Estimated implementation plan

The estimated execution plan can be displayed by clicking the icon on the mouse or by using the shortcut key ctrl+l. It is estimated that the execution plan will not actually be executed, only the estimated execution plan.

B. Actual implementation plan

Click the actual Execution Plan icon, the icon is selected, and then execute the SQL statement, which displays the execution plan that was actually executed.

(2) Textual execution plan

Represents each iterator with a separate line. Use the vertical bar (symbol | ) to represent the parent-child relationship between iterators in the query tree. Data is flowing from the child iterator to the parent iterator.

Pros: compared to graphical plans, text execution plans are easier to save, process, search, and compare.

--Displays the complete estimated execution plan information set SHOWPLAN_TEXT Ongo

--Displays limited information about the estimated execution plan and can analyze set SHOWPLAN_ALL with tools such as Osql.exe Ongo

--Displays the complete actual execution plan information set STATISTICS profile Ongo

Summarize:

(3) XML Execution plan

Pros: Three of the most detailed execution plans. The graphics execution plan can be saved as a plan file in an XML format with the. sqlplan extension, and opening this file will be presented in a graphical execution plan.

--Show estimated execution plan set Showplan_xml Ongo

--Displays the actual planned XML format data set STATISTICS XML Ongo

Summarize

Iv. How to analyze the implementation plan?

The following is an analysis of the execution plans for three scenarios:

1. Heap Table

2. Clustered index

3. Nonclustered indexes

Structure	Scanning	Find	Bookmark Lookup
Heap Table	Table Scan	That's not the case.	RID Lookup
Clustered index	Clustered Index Scan	Clustered index Lookup	That's not the case.
Nonclustered indexes	If the index is used, it is an index scan	Index Lookup	Key Lookup

Those things about table scans:

• A table without an index is called a heap table, and a table scan is used to find matching rows.
• If a table scan operation occurs, there must be no clustered index on the table.

Those things about index lookups:

Suppose that [column 1] has a single-column index, you can use this index to find these predicates:

1.[column 1] = 1.23

2.[column 1] > 1.23

3.[Column 1] between 1 and 100

4.[column 1] like ' abc% '

5.[column 1] in (1,3,7,10)

You cannot use this index to find the following predicates:

1.abs[column 1] = 1

2.[column 1] + > 12

3.[column 1] like '%abc '

Those things about nonclustered indexes:

1. If the nonclustered index does not contain a query column when there is only a nonclustered index, the SQL query optimizer chooses a nonclustered index scan.
2. A table scan is selected when a nonclustered index does not contain a filter condition column when there is only a nonclustered index.
3. Nonclustered indexes have a structure that is independent of the data rows. Nonclustered indexes contain nonclustered index key values, and each key-value entry has a pointer to the data row that contains the key value.
4. A pointer to a data row from an index row in a nonclustered index is called a row locator. The structure of a row locator depends on whether the data page is stored in a heap or in a clustered table. For heaps, a row locator is a pointer to a row. For clustered tables, the row locator is the clustered index key.
5. You can add nonkey columns (including columns) to the leaf level of a nonclustered index to skip existing index key restrictions (900-byte and 16-key columns) and perform full-scope index queries.

Things about Clustered indexes:

1. If there is a clustered index on the table, the scan is called a clustered index scan, and the lookup is called a clustered index lookup;

2. The performance of clustered Index scan and table scan is not much different;

3. The clustered index sorts and stores the data rows in a table or view based on the key values of the data rows.

4. The index definition contains a clustered index column.

5. Each table can have only one clustered index, because the data rows themselves can only be sorted in one order.

6. Data rows in a table are stored in sorted order only when the table contains a clustered index. If the table has a clustered index, the table is called a clustered table. If a table does not have a clustered index, its data rows are stored in an unordered structure called a heap.

7. Adding a clustered index does not necessarily improve performance, in some cases performance may be less than the table scan;

8. The clustered index is the table itself. How many rows and columns the table has, and how many rows and columns the clustered index has.

9. In a single-table query, the filter condition has a clustered index column, and can use this index to find the predicate in the filter condition, it is a clustered index lookup, the filter condition does not have a clustered index column is a clustered index scan.

(1) Non-indexed condition

Create a Myorder table

Use [Test]go CREATE TABLE [dbo]. [MyOrder] ([ID] [int] not null,[customer] [nvarchar] (+) not NULL) on [PRIMARY] GO

Myorder has only two columns of Id,customer, both of which have no indexes.

SELECT [id] from  [test].[ DBO]. [MyOrder]  WHERE [Customer] = ' DDD '

Here is the execution plan:

There is no index above the customer column, SQL Server needs to read each row of the Myorder table to determine customer= ' DDD ', and returns this line if the result is true.

The sample graph for the query is as follows, customer=ddd there are three records.

Attention:

1. Scans and lookups are iterators used by SQL Server to read data from tables and indexes;

2. Scan all branches that are used to process the entire table or index;

3. A lookup is a valid return of a row in one or more ranges in an index, based on a predicate.

(2) The case of a nonclustered index

Create a nonclustered index on an ID

Use [Test]go---delete Dbo.myOrder.ID_NON_INDEXDROP index Dbo.myOrder.ID_NON_INDEX CREATE nonclustered index [id_non_index ] on [dbo]. [MyOrder] ([id] ASC) with (pad_index  = off, statistics_norecompute  = off, sort_in_tempdb = off, Ignore_dup_key = off, drop_ex Isting = off, ONLINE = off, allow_row_locks  = on, allow_page_locks  = on) on [Primary]go

1. There are no nonclustered indexes on the columns of the query criteria, no nonclustered indexes on the query columns, and table scans

There is an index on the--id column, there is no index on the customer column, and the query condition is filtered by customer= ' DDD '. --because there is no index on the customer column, a table scan is required to find rows that conform to customer= ' DDD '. SELECT [id] from  [test].[ DBO]. [MyOrder]  WHERE [Customer] = ' DDD ' SELECT [customer] from  [test].[ DBO]. [MyOrder]  WHERE [Customer] = ' DDD '  SELECT [Id],[customer] from  [test].[ DBO]. [MyOrder]  WHERE [Customer] = ' DDD ' SELECT [id] from  [test].[ DBO]. [MyOrder]  WHERE [id] = 2 and [customer] = ' DDD ' SELECT [Id],[customer] from  [test].[ DBO]. [MyOrder]  WHERE [id] = 2 and [customer] = ' DDD '

2. There is a nonclustered index on the column of the query condition, and a table scan with no nonclustered index on the query column

There is an index on the--id column, there is no index on the customer column, and the query condition is filtered by id=2. The--select query needs to return the customer column, because there is no index on the customer column, and the index [Id_non_index] does not contain the customer column--that is, using a nonclustered index scan to find an index branch that matches the filter id=2. However, you can only get the value of the ID column on the index branch, because the index branch contains only the ID column, and the values of the other columns are not available. --so you still need to do a table scan to find rows that match the criteria, and then get the value of the customer column for that row. --Here's a question: Why can't I find the corresponding row after I find the index branch and get the customer column of this line??

3. There is a nonclustered index on the column of the query criteria, and the index lookup on the query column has a nonclustered index

There is an index on the--id column, there is no index on the customer column, and the query condition is filtered by id=2. The--select query needs to return the ID column, use a nonclustered index scan to find the index branch that matches the filter id=2, and get the value of the ID column on the found Index branch. SELECT [id] from  [test].[ DBO]. [MyOrder]  WHERE [id] = 2

( 3 ) with a clustered index

1. Clustered index scan on column without clustered index on query criteria

There is a clustered index on the----ID column, there is no index on the customer column, and the query condition is filtered by customer= ' DDD '. --because there is no index on the customer column, a scan is required to find rows that conform to customer= ' DDD '. --As long as there is a clustered index, the scan is a clustered index scan. The performance of clustered indexes and table scans is essentially the same. SELECT [id] from  [test].[ DBO]. [MyOrder]  WHERE [Customer] = ' DDD '  SELECT [customer] from  [test].[ DBO]. [MyOrder]  WHERE [Customer] = ' DDD '  SELECT [Id],[customer] from  [test].[ DBO]. [MyOrder]  WHERE [Customer] = ' DDD '

　　

2. Clustered index lookup on columns with query criteria

There is an index on the--id column, there is no index on the customer column, and the query condition is filtered by id=2. --The id=2 row was found with a clustered index, and because the rows in the table were sorted by ID column, the row was located--and all the columns of the row could be found, so we could get the customer column. So it's a clustered index scan. SELECT [Customer] from  [test].[ DBO]. [MyOrder]  WHERE [id] = 2  SELECT [id] from  [test].[ DBO]. [MyOrder]  WHERE [id] = 2   SELECT [Id],[customer] from  [test].[ DBO]. [MyOrder]  WHERE [id] = 2

　　

3. In the query condition, one column has a clustered index, and the other column does not have a clustered index lookup

There is an index on the--id column, there is no index on the customer column, and the query condition is filtered with [id] = 2,[customer] = ' ddd '. --when one of the filter conditions can be found based on a clustered index, the clustered index is used to find the matching row ([id] = 2), and then the rows that conform to [customer] = ' ddd ' are filtered in the filtered rows. --so it's a clustered index lookup. --Question: Why is the second-step filtering operation not reflected in the execution plan diagram?? This place I think is to get id=2 matching line, directly discard the non-conforming [customer] = ' DDD ' line, this discard action is not directly reflected.  SELECT [id]  from [Test]. [dbo]. [MyOrder]  WHERE [id] = 2 and [customer] = ' DDD '    SELECT [Id],[customer] from  [test].[ DBO]. [MyOrder]  WHERE [id] = 2 and [customer] = ' DDD '    SELECT [Id],[customer] from  [test].[ DBO]. [MyOrder]  WHERE [Customer] = ' 3333 ' and [id] = 2

"SQL Advanced" 03. Plan of Execution Tour 1-preliminary exploration