MSSQL Optimization execution plan

Today, we will explore the execution plan of MSSQL to let everyone know how to view the optimization mechanism of MSSQL to optimize SQL queries.

 

The code is as follows:

Copy code

-- 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 (), '000000' + 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 information about 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 records.

Then I wrote a query statement:

The code is as follows:	Copy code
SELECT * FROM T_UserInfo where userid = 'abcde6ef'

Select the preceding statement and press Ctrl + L, as shown in the following figure.

This is the execution plan of MSSQL: table scan: scan rows in the table

Then let's take a look at the read/write of the statement to IO:

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 disappear column:

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;

The number of pages read from the data cache;

The number of pages read from the disk;

Number of pages cached for query

Important: if there are multiple ways to write an SQL query, the logical read (logical reads) in the four values determines which one is optimal.

 

Next we will create a clustered index for it.

Run create clustered index INDEX_Userid ON T_UserInfo (Userid)

Then run SELECT * FROM T_UserInfo where userid = 'abcde6ef'

Switch to the 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,

We have added an index page. When we query the page, we need to read two pages (one index page + one data page). At this time, the efficiency is not as good as that of no index.

Then select the query statement, and then Ctrl + L,

Clustered index search: scans rows in a specific range in the clustered index.

The index is used.

OK. Now you should know the MSSQL query plan and how to view the read consumption of IO!

 

Next we will continue:

Now I will change the test data to 1000 records.

Execute set statistics io on and then

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)

In this way, the index query advantage is displayed when the data volume is slightly larger.

 

Summary:

When you build an SQL statement, press Ctrl + L to see how the statement is executed, whether to use index scan or table scan?

You can use set statistics io on to view logical reads and complete different SQL statements with the same function.

The smaller the query speed, the faster the query speed (of course, do not 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:

The code is as follows:	Copy code
SELECT * FROM T_UserInfo where userid like 'abcde8%' | -- Table Scan (OBJECT :( [student]. [dbo]. [T_UserInfo]), WHERE :( like ([T_UserInfo]. [Userid], 'abcde8% ', NULL )))

Ctrl + L check the index I added at this time:

The code is as follows:

Copy code

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 execution plan of the graph at this time:

Ctrl + L view the graph line at this time

When there is an index, we can write another SQL statement:

The code is as follows:	Copy code
SET SHOWPLAN_ALL ON SELECT * FROM T_UserInfo where left (USERID, 4) = 'abcde8%'

View StmtText:

The code is as follows:	Copy code
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% '))

 

Let's take a look at the IO operations in three cases.

They are 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:

For the first time, we scanned the table and scanned 7 pages, that is, the full table scan.

The second index scan scanned one page of index and two pages of data.

The third is index scan + table scan, which scans one page of index and seven pages of data.

[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, haha.

 

If you understand the above, you may have a new preliminary idea about SQL optimization. Is that true for the SQL optimization articles on the Internet? 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.

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.