SQL Server execution plan and SQL query optimization

Today, we will discuss the execution plan of MSSQL to let everyone know how to view the optimization mechanism of MSSQL to optimize SQL queries, instead of simply using Program The execution result is optimized.

-- Drop table t_userinfo -------------------------------------- create a test table
Create Table t_userinfo
(
Userid varchar (20), username varchar (20 ),
Regtime datetime, tel varchar (20 ),
)
-- Insert Test Data
Declare @ I int
Declare @ endid int
Select @ I = 1
Select @ endid = 100 -- modify the data to be inserted here and delete all data before re-insertion.
While @ I <= @ endid
Begin
Insert into t_userinfo
Select 'abcde' + Cast (@ I as varchar (20) + 'ef ', 'lil' + Cast (@ I as varchar (20), getdate (), '200' + Cast (@ I as varchar (20 ))
Select @ I = @ I + 1
End

-- Explanation of related SQL statements
--------------------------------------------------------------- Create a clustered Index
Create clustered index index_userid on t_userinfo (userid)
-- Create a non-clustered Index
Create nonclustered index index_userid on t_userinfo (userid)
-- Delete an index
Drop index t_userinfo.index_userid
-----------------------------------------------------------
-------------------------------------------------------------
Displays the disk activity volume generated by the transact-SQL statement.
Set statistics Io on
-- Disable the disk activity volume generated by the transact-SQL statement.
Set statistics Io off
-- Display [return detailed information about statement execution and estimate the resource requirements of statements]
Set showplan_all on
-- Disable [return detailed information about statement execution and estimate the resource requirements of statements]
Set showplan_all off
-----------------------------------------------------------
Remember: Set statistics Io and set showplan_all are mutually exclusive.

OK , Start now: First, we insert 100 Data entries
Then I wrote a query statement:
Select * From t_userinfo where userid = 'abcde6ef 'select the preceding statement and press Ctrl + L, as shown in figure This is MSSQL Execution Plan: Table scan: scan the rows in the table
Then let's take a look at Io Read/write:
Run: Set statistics Io on
Then run the SQL statement: Select * From t_userinfo where userid = 'abcde6ef'
The following figure shows how to switch to the Message bar:
Table 't_ userinfo '. Scan count 1, logical read 1, physical read 0, pre-read 0.
Explain the meaning:
The four values are:
Number of scans performed;
Number of pages read from the data cache ;
The number of pages read from the disk;
Number of pages cached for query
Important: If SQL There are multiple ways to write a query, then the four values of the logical read ( Logical reads) It determines which one is optimal.

next, create a clustered index for it
execute create clustered index index_userid on t_userinfo (userid)
then execute select * From t_userinfo where userid = 'abcde6ef '
switch to the following message bar:
table 't_ userinfo '. Scan count 1, logical read 2, physical read 0, pre-read 0.
in this case, the logic read is changed from 1 to 2, indicating that an index page is added, logical reading is to read two pages (one index page + one data page). At this time, the efficiency is not as good as not creating an index.
select the query statement and press Ctrl + L, for example,
clustered index search: scan the rows in a specific range in the clustered index . The index is used.
OK, here you should have known MSSQL query plans and view pairs Io is consumed!

Next we will continue:
Now I will change the test data to 1000 records.
Then execute set statistics Io on,
Then run select * From t_userinfo where userid = 'abcde6ef'
Without a clustered index:
Table 't_ userinfo '. Scan count 1, logical read 7, physical read 0, pre-read 0.
When a clustered index is added: Create clustered index index_userid on t_userinfo (userid)
Table 't_ userinfo '. Scan count 1, logical read 2, physical read 0, pre-read 0.
(In fact, we read an index page and a data page at this time). When the data volume is a little large, the index query advantage is displayed.

Summary:When you buildSQLStatement, pressCTRL + LWe can see how the statement is executed, whether to use index scan or table scan? PassSetStatistics Io onTo view logical reads and complete different functions of the same function.SQLStatement, logical read The smaller the query speed, the faster(Of course, don't look for an example with only a few hundred records to counter me.).

Let's go further:
OK. Now let's take a look at it again. Let's change the SQL statement to see how MSSQL executes this SQL statement?
Now remove the index: drop index t_userinfo.index_userid
Now open [show statement execution details]: Set showplan_all on
Then run: Select * From t_userinfo where userid like 'abcde8%'
See the result bar: some specific parameters in the result, such as IO consumption and CPU consumption.
Here we only look at stmttext:
Select * From t_userinfo where userid like 'abcde8%'
| -- Table scan (Object :( [Student]. [DBO]. [t_userinfo]), where :( like ([t_userinfo]. [userid], 'abcde8% ', null) Ctrl + L to view the graph row execution plan at this time:

I add an index:
Disable: Set showplan_all off
Run create clustered index index_userid on t_userinfo (userid)
Enable: Set showplan_all on
Run: Select * From t_userinfo where userid like 'abcde8%'
View stmttext:
Select * From t_userinfo where userid like 'abcde8%'
| -- Clustered index seek (Object :( [Student]. [DBO]. [t_userinfo]. [index_userid]), seek :( [t_userinfo]. [userid]> = 'abcde8' and [t_userinfo]. [userid] <'abcde9'), where :( like ([t_userinfo]. [userid], 'abcde8% ', null) ordered forward) Ctrl + L view the graph row execution plan at this time:
When there is an index, we can write another SQL statement:
Set showplan_all on select * From t_userinfo where left (userid, 4) = 'abcde8%'
View stmttext:
Select * From t_userinfo where left (userid, 4) = 'abcde8%'
| -- Clustered index scan (Object :( [Student]. [DBO]. [t_userinfo]. [index_userid]), where :( substring ([t_userinfo]. [userid], 1, 4) = 'abcde8% ') Ctrl + L to view the graph row execution plan at this time:
Let's take a look at the IO operations in three cases respectively as follows:
First case: Table 't_ userinfo '. Scan count 1, logical read 7, physical read 0, pre-read 0.
Case 2: Table 't_ userinfo '. Scan count 1, logical read 3, physical read 0, pre-read 0.
Case 3: Table 't_ userinfo '. 1 scan count, 8 logical reads, 0 physical reads, and 0 pre-reads.

Note:
The first scan was performed on a table.7Page, that is, full table Scan
The second scan is an index scan.1Page index,2Page number
Third timeYesindex Scan+Table scan, scanned1Page index,7Page number
[The graphical interface also consumes CPU and IO resources. You can also see which one is the best.]

By comparison, we can easily see that the second and third methods use indexes when there are indexes, while the left method does not, this is the simplest example of optimization.
If you understand the above, you may have a new preliminary idea about SQL optimization, a pile of SQL Optimization on the InternetArticleIs that true? You can try it on your own, instead of blindly remembering those things. You can try it on your own and see how MSSQL is executed.

Important:In my example, a clustered index scan is used. The fields are letters and numbers. You can try pure numbers, letters, and Chinese characters, learn how mmsql changes SQL statements to use indexes. Then try the non-clustered index? What is the relationship between non-indexing? How does MSSQL execute a subquery? In does not use indexes, and like does not use indexes? Does function use no index? Or, And, union? What about subqueries? Here I will not try to show you one by one. As long as you know how to view the execution plan of MSSQL (graphics and text), many things will be clear.

Summary:There may be a variety of SQL statements that implement the same query function. If you determine which optimization method you want to perform, if you only test it in time, it will be affected by many external factors. We understand how to execute MSSQL, i/O logic reading, querying the graph query plan, and SQL statements executed after optimization are the real ways to optimize SQL statements. Note: The amount of data may sometimes affect the execution plan of MSSQL for the same query statement, which is particularly obvious in non-clustered indexes, in addition, in the case of multi-CPU and single-CPU concurrency, the execution plan of query statements in the same way may be different, this requires you to have the opportunity to try it out (I have not had much experience in this area to share with you ). Write this first, because I still know MSSQL very lightly. please correct me if there is something wrong.