SQL Execution Plan (ii)

Recently always want to tidy up some of the MSSQL's understanding and sentiment, but has no mind and time to write, at night, no matter then wrote an exploration of the MSSQL Implementation plan, this paper said the implementation plan but not only to say the implementation plan.

The online SQL optimization of the article is really a lot of, say the truth, I have been everywhere to find such articles, what do not use in, what or, what and, many many, there are many people come up with only a few s or even a few Ms examples to prove what (a little ridiculous), Let a lot of people don't know whether it is right or wrong. and SQL optimization is every need to deal with the database of programmers, so write this article, with friends.

When it comes to optimization, it is necessary to refer to the index, like to talk about the lock must say the same thing, so you need to understand the index, just the index, you can talk for a long while, so the index I will not say (dozen many words is very tired, and I also know very little), you can refer to the relevant articles, this online

Explore the implementation plan of MSSQL today to let you know how to view the optimization mechanism of MSSQL to optimize SQL queries.

--drop TABLE T_userinfo----------------------------------------------------

--Build a test table

CREATE TABLE t_userinfo

(

Userid varchar, UserName varchar (20),

Regtime datetime, Tel varchar (20),

)

--Inserting test data

DECLARE @i INT

DECLARE @ENDID INT

SELECT @i = 1

SELECT @ENDID = +- - Change the data you want to insert here, and delete all data before re-inserting

While @i <= @ENDID

BEGIN

INSERT into T_userinfo

SELECT ' ABCDE ' +cast (@i as varchar) + ' EF ', ' Lee ' +cast (@i as varchar) ,

GETDATE (), ' 876543 ' +cast (@i as VARCHAR (20))

SELECT @i = @i + 1

END

--related SQL statement interpretation

---------------------------------------------------------------------------

--Build a clustered index

CREATE CLUSTERED INDEX index_userid on T_userinfo (Userid)

--Build non-clustered index

CREATE nonclustered INDEX index_userid on T_userinfo (Userid)

--Delete Index

DROP INDEX T_userinfo.index_userid

---------------------------------------------------------------------------

---------------------------------------------------------------------------

--Displays information about the amount of disk activity generated by Transact-SQL statements

SET STATISTICS IO on

--close information about the amount of disk activity generated by Transact-SQL statements

SET STATISTICS IO OFF

--Displays [returns details about the execution of the statement and estimates the resource requirements of the statement]

SET showplan_all on

--Close [returns detailed information about the execution of the statement and estimates the statement's requirements for resources]

SET showplan_all OFF

---------------------------------------------------------------------------

Keep in mind that set STATISTICS IO and set SHOWPLAN_ALL are mutually exclusive.

OK , start now:

First, we insert - Bar Data

Then I wrote a query statement:

SELECT * from T_userinfo WHERE userid= ' Abcde6ef '

With the above statement selected, press CTRL+l, as

this is MSSQL Execution Plan: Table scan: Scan rows in a table

then we'll look at the statement for IO Read and write:

Execution:SET STATISTICS IO on

Execute the SQL again at this time: SELECT * from T_userinfo WHERE userid= ' Abcde6ef '

Switching to the vanishing bar appears as follows:

Table ' T_userinfo '. Scan count 1, logic read 1 times, physical read 0 times, pre-read 0 times.

Explain the meaning of it:

The four values are:

Number of scans performed;

Number of pages read from the data cache ;

number of pages read from disk;

Number of pages placed in cache for query

IMPORTANT: If for a SQL queries are written in multiple ways, the logical reading of these four values (Logical reads) determines which is the most optimized.

Next we build 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 message bar as shown below:

Table ' T_userinfo '. Scan count 1, logic read 2 times, physical read 0 times, pre-read 0 times.

At this point the logic reads from the original 1 into 2,

We added an index page, and now when we query, the logical reading is to read two pages (1 index page +1 data page), at which time the efficiency is not as good as the index.

At this point, select the query statement, and then CTRL+l, such as:

Clustered index Lookup: Scan a specific range of rows in a clustered index

Description, the index is used at this time.

OK, Here you should already know the preliminary know MSSQL query plan and how to view the IO the read consumption of it!

Next we continue to:

Now I'm going to change the test data to 1000.

Then execute set STATISTICS IO on, then execute

SELECT * from T_userinfo WHERE userid= ' Abcde6ef '

In the case of non-clustered indexes:

Table ' T_userinfo '. Scan count 1, logic read 7 times, physical read 0 times, pre-read 0 times.

In the case of a clustered index: CREATE CLUSTERED index Index_userid on T_userinfo (Userid)

Table ' T_userinfo '. Scan count 1, logic read 2 times, physical read 0 times, pre-read 0 times.

(In fact, that is, it is read an index page, a data page)

Thus, when the amount of data is slightly larger, the query advantage of the index is displayed.

first Small summary below :

When you build SQL statement, press Ctrl+l you can see how the statement is executed, using an index scan or a table scan?

through SET STATISTICS IO on to see the logical reads, and to complete the same function differently SQL statement, logical read

The smaller the query, the faster it gets . ( of course, do not find that only hundreds of records of the example to counter me ) .

Let's go further:

OK, now let's look at it again, let's change the SQL statement, and see how MSSQL executes this SQL?

Now get rid of the indexes: DROP index t_userinfo. Index_userid

Now open [Show statement execution details]:set showplan_all on

Then execute: SELECT * from T_userinfo WHERE USERID like ' abcde8% '

Look at the results bar: Some specific parameters in the results, such as IO consumption, 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 look at the graph line execution plan at this time:

I'll add the index:

Off first: SET showplan_all off

Re-execution: CREATE CLUSTERED INDEX index_userid on T_userinfo (Userid )

Re-open: SET showplan_all on

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

Ctrl+L look at the graph line execution plan at this time:

In the case of an index, let's write another sql:

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

Let's look at the operation of IO in three different cases.

The following are the differences:

First case: Table ' T_userinfo '. Scan count 1, logic read 7 times, physical read 0 times, pre-read 0 times.

Second case: Table ' T_userinfo '. Scan count 1, logic read 3 times, physical read 0 times, pre-read 0 times.

The third case: Table ' T_userinfo '. Scan count 1, logic read 8 times, physical read 0 times, pre-read 0 times.

This shows:

The first time was a table scan, swept 7 page, which is the full table scan

The second time is the index scan, sweep the 1 page index, 2 Page Data page

The third time is an index scan + table Scan, sweep the 1 page index, 7 Page Data page

[The graphical interface also has CPU and IO consumption, you can also see which of the best!]

By comparison, hehe, it is easy to see: The second Third way of writing in the case of the index, like the effective use of the index, and left can not, such a simple optimization of the example came out, haha.

If you understand the above, then you may have a preliminary new idea of SQL optimization, the online heap of SQL optimization of the article is really like that? You have to try to know, and do not have to blindly remember those things, you try to see how the MSSQL in the end to understand how to do it.

In my example, the use of a clustered index scan, the fields are letters plus numbers, you can try the pure numbers, letters, characters, and so on, understand how the next mmsql will change the SQL statement to take advantage of the index. And then try again what is a nonclustered index? With no index and what about? Subquery How is MSSQL executed? in with no index, like with no index? function with no index? or,and,UNION? What about subqueries? Here I do not try to show you, as long as you know how to see the MSSQL Implementation Plan (graphics and text ), a lot of things are very clear.

Big Summary:

SQL can be written to implement the same query function may have many, if you decide which optimization, if only from time to test, will be affected by many external factors, and we understand how MSSQL to execute, through the IO logic Read, The real way to optimize SQL is by looking at the diagram's query plan, the SQL statements that are executed after it is optimized.

Another reminder: The amount of data can sometimes affect MSSQL 's execution plan for the same query notation, which is particularly noticeable on nonclustered indexes, and in the case of multi-user concurrency under multi- CPU and single CPU . The query statement execution plan of the same writing will be different, this will require you to have the opportunity to experiment ( I do not have too much experience to share with you ).

SQL Execution Plan (ii)