MSSQL optimization ---- exploring MSSQL execution plans)

Author: no_mIss 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. Start now:

First, we insert 100 data records.

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 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.

 

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

 

 

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:

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] <'abcde9'), WHERE :( like ([T_UserInfo]. [Userid], 'abcde8% ', NULL) ordered forward) Ctrl + L to 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:

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.

 

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.

 

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/no_mIss/archive/2006/11/09/1374978.aspx