MSSQL Optimization-exploring MSSQL execution plans

MSSQL Optimization-exploring MSSQL execution plans

Author: nz. perfectaction QQ: 34813284
Time: 2006.11.07 23:30:00
Environment: win2003 + mssql2005

Recently, I have always wanted to understand and understand MSSQL, but I have never thought about it and have no time to write it. At night I wrote an execution plan to explore MSSQL, this article describes the execution plan, but not limited to the execution plan.
There are a lot of articles on SQL Optimization on the Internet. To be honest, I have been looking for such articles everywhere. There are a lot of articles about what to do, AND what to do, many people come up with examples of time difference of several seconds or even milliseconds to prove something (a bit ridiculous), so that many people do not know whether it is right or wrong. SQL optimization is a required course for every programmer who wants to deal with the database. So I wrote this article and shared it with my friends.
When it comes to optimization, indexes must be involved, just like locking must be a transaction. So you need to know about indexes. It takes only half a day to talk about indexes, so I won't talk about indexing (I am tired of typing a lot of words, and I know little about it). I can refer to the relevant articles. This is a lot of online materials.
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.

-- 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 [/B]. Start now: [/B] [/B]
First, we insert [/B] 100 [/B] pieces of data [/B] [/B]
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 the execution plan of [/B] MSSQL [/B]: Table scan: scan the rows in the table [/B] [/B]

Then let's take a look at the read and write operations of this statement on [/B] IO [/B]: [/B] [/B]
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;
Number of pages read from the data cache [/B];
The number of pages read from the disk;
Number of pages cached for query
Important: if there are multiple writing methods for a [/B] SQL [/B] query, then the logical reading of the four values [/B] ([/B] logical reads) [/B] determines which one is optimal. [/B] [/B]

Next we will create a clustered index for it [/B] [/B]
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.

Select the query statement and then Ctrl + L, for example:

[/B]
[/B]
Clustered index search: scan the rows in a specific range in the clustered index [/B] [/B]
The index is used.

OK,[/B]At this point, you should have known[/B]MSSQL[/B]Query the plan and how to view[/B]IO[/B]Read consumption![/B][/B]

Next we will continue: [/B] [/B]

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.

I would like to summarize [/B]:
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.).

Further dive: [/B] [/B]
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 view the execution plan of the graph 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] [/color]
Where :( like ([t_userinfo]. [userid], 'abcde8% ', null) ordered forward) Ctrl + L view the graph row execution plan at this time:
CTRL + L view the execution plan of the graph 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 view the execution plan of the graph at this time:

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:
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
The third index 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, 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.

Conclusion: [/B] [/B]
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.