Polishing your eyes to see SQL Server simple Select

This article mainly discusses a select statement that almost everyone is familiar with, but many others are unfamiliar. I don't know if you have thought about what it is, so that SQLServer can understand the select statement we wrote. What happened in the middle? Have you ever been impulsive to understand. At least I was eager to know about it, but I was mainly studying CLR and webform knowledge. Later, I focused on studying the internal mechanism of SQLserver. Today I will introduce this statement to you.

I. Sample Database script

Create database Test

Go
Alter database Test set recovery simple
Go
Use Test
Go

Create table Test

(
ID int identity (1, 1) primary key,
[Name] varchar (64) not null default '',
CreatedTime datetime not null default getdate ()
)

Insert into Test ([name]) values ('xiaojun ')

This script is not introduced, it is very simple.

Ii. Statement Analysis

Select * from Test

Simple: the title is a simple statement. Let's start to analyze this statement. If you already know the overall architecture of SQLServer or have read the first article in this series. When this statement is reliably passed to the command analyzer in the relational engine, the following occurs:

 

Analysis: checks the T-SQL from the syntax library for basic syntax checks. If a syntax error occurs, the entire statement is immediately stopped, prompting you to see a syntax error. For example, keyword, column, and table name used incorrectly. If no syntax error occurs, an analysis tree is generated and passed to the next step.

Binding: 1. name resolution: Check that all objects exist and are visible in the user's security context. This step is well understood mainly because every object in the database has permissions. If the logged-on account does not have the corresponding permissions, complete this step.

2. Type derivation: determine the final type of each node in the resolution tree. This step mainly supplements the analysis tree in the analysis step to determine its final type. I don't know why you have to take this step. Why not confirm it in the analysis? The main reason is efficiency. Type derivation consumes resources and does not need to be determined if the user has permissions on each object. Then, why not determine that the user has permissions for each object before analysis. This is because when no analysis is performed, the system cannot know the specific objects. I want to talk about it again. The design of SQLServer is really exquisite, even considering such details and resource consumption. It is worth learning.

3. Aggregation binding: determines where aggregation can be performed. This step is mainly related to whether there are aggregate operations in SQL.

4. Combined binding: bind the aggregate to the correct selection list. This step binds the aggregation operation to the column to be aggregated.

These two steps are mainly completed by the command analyzer, which finally gets the analysis tree and passes it to the most complex and optimal technical component of the SQL Server engine. None of them are the query optimizer. The query optimizer function is simple, that is, SQL optimization. The specific optimization model is as follows:

Optimization:1. Check that there is no corresponding execution plan in the execution plan cache. If not, continue with the following operations. If yes, cache is used. SQLServer compares the hash values of SQL statements. Think about why?

2. Pre-optimization: the query statement is very simple and the overhead is small enough to directly end the optimization. For example, a basic query without a join operation. It is a common plan with zero overhead. For example, the select statement can be pre-optimized.

3. Phase 0: Check the basic rules and the options of hash and nested join. Whether the overhead of this plan is less than 0.2. If yes, end optimization. Here, 0.2 and the following 1.0 are internal overhead values of SQLServer and are only used internally by SQLServer.

4. Phase 1: Test more rules and change the join sequence. If the minimum overhead is less than 1.0, end optimization. If not, continue to judge. If maxdop> 0 and the system is an SMP system, and the minimum overhead is greater than the overhead threshold of parallelism, the parallel plan is used. Compared with the overhead of parallel plans and the overhead of the best serial plan, the plan with lower overhead is passed to Stage 2.

Parallel plan refers to the process where the optimizer splits the appropriate operators into several processes that can run synchronously on different processors and requires multi-core support. Query of large data volumes can improve efficiency.

What is maxdop? This is an advanced configuration of SQLServer. We can view it through sp_configure. For example:

You may directly run sp_configure and cannot see this configuration. This is an advanced configuration item, which is disabled by default. Run the following statement and run sp_configure to view the medium information.

Sp_configure 'show advanced options', 1

Reconfigure

The Advanced Configuration value does not need to be modified by default. You must have a deep understanding of SQLServer and evaluate the impact of modification on SQLServer before modifying it. Let's talk about this maxdop. This is the maximum number of processors that SQL server uses to execute a parallel plan. 0 indicates that SQL Server determines the number of processors. Otherwise, the maximum degree of parallelism is specified by the user. Therefore, the above maxdop> 0 and the system is an SMP system means that when the user modifies this configuration item, therefore, the executor should give priority to the system overhead with a smaller maximum degree of parallelism modified by the user when evaluating the overhead. The SMP system is a symmetric processor architecture. Intel processor-based servers are basically SMP systems. Do not expand here.

I don't know if you have noticed that the graph query optimizer above outputs a good execution plan. Think about why? To view the internal conditions of the optimization process, use sys. dm_exec_query_optimizer_info to dynamically manage the view. For example, the following example shows that our select statement is a common plan. Follow these steps:

Dbcc freeproccache -- clear the execution plan Cache

Select * from sys. dm_exec_query_optimizer_info where counter in ('optimizations ', 'trivial plan', 'search 0', 'search 1', 'search 2 ')

The result is as follows:

Continue:

Select * from Test

Select * from sys. dm_exec_query_optimizer_info where counter in ('optimizations ', 'trivial plan', 'search 0', 'search 1', 'search 2 ')

The result is as follows:

Now, the trival plan type count + 1 indicates that the optimizer optimizes select * from Test as a general plan.

5. Phase 2: Test all possible plans and select the plan with the minimum overhead when the tested time limit is reached.

Execution: this plan is scheduled for execution. SQLOS is not covered in this article, as long as it is simply understood as being handed over to the CPU for execution.

3. End

This statement can be analyzed in many ways to compare the locking and scheduling execution of this statement. You still want to explain this in a later chapter. This article focuses on how SQL Server performs SQL parsing optimization. After careful research, you will find that the SQL Server Query Optimizer has made many optimization measures. Of course, other databases are similar components. In fact, you will find that these are blocked by most of our developers. Blocking is an improvement. java and. net garbage collection blocks developers' memory management. What does SQLServer block here? This requires studying the database history. Only by studying the history can we know why the current database looks like it from a high angle.

This is the end of today's analysis. If your description is inappropriate, please note that. Common progress is the final principle.

List the next six article plans:

Polish your eyes to see the historical origins of SQLServer

Polish your eyes to see the foundation of SQL Server's mathematics

Polish your eyes and look at SQL Server's simple insert

Polish your eyes to see the practical index of SQLServer

Polishing your eyes and viewing the lock mechanism of SQLServer

Polish your eyes to see the tangle of SQL Server Tempdb

......