SQL Server 2012:SQL Server architecture--The life cycle of a query (part 2nd)

Original: SQL Server 2012:SQL Server architecture--The life cycle of a query (part 2nd)

Planning cache (Plan caches)

If SQL Server has found a good way to execute a piece of code, it should be reused as a subsequent request because Generating execution plans is time-consuming and resource-intensive, There is a point in doing so.

If the cached plan is not found, then command Parser generates a query tree on the T-SQL basis. The internal structure of the query tree is the execution of the action required in the query, represented by each node on the tree. The tree is then passed to the query optimizer (Optimizer) for processing. Our simple query does not have an existing plan, so a query tree is created and then passed to the query optimizer (Optimizer).

shows that command Parser is used to check the plan cache for an existing execution plan because no information is found in the cache for our queries, and the query tree from the command Parser output to the optimizer.

Query Optimizer is considered the most valuable property by the SQL Server team and is one of the most complex and confidential parts of the product. Fortunately, only the underlying algorithms and source code are well protected (even within Microsoft), how the optimizer works to be researched and monitored.

This is called cost-based (cost-based ), it means evaluating the various ways in which queries are executed, and then choosing the way to execute them that is considered to have the least cost. Execution is implemented in query plan and output from query Optimizer.

Based on the introduction just now, you think the optimizer's job is to find the best query plan that will be forgiven, because that seems like a pretty obvious idea. The real job, however, is to find a good plan over time, not the best plan. The goal of the optimizer is often described as finding the most efficient plan.

If the optimizer tries to find the best plan every time, it takes longer to find the best plan than a slow plan (some built-in heuristics actually ensure that the optimizer never takes longer to find a good plan, but instead finds a plan and executes it).

The optimizer also performs multistage optimizations on a cost basis, adding more available options at each stage to find better plans. When a good plan is found, the optimizer stops at that stage.

The 1th stage is called pre-optimization, and when the statement is simple enough and there is only one best plan, exit the remaining steps at the first stage and remove the additional cost requirement. Basic queries without joins are considered simple, and the planned cost output is 0, which is then referred to as the General plan (trivial plans).

The next phase of the optimization actually begins consists of three lookup periods:

• No. 0 Period-This period the optimizer will find the inner loop connection (nested loop joins) without regard to the parallel operator (parallel operators).

If a planned cost of less than 0.2 has been found, the optimizer will stop here. The plan generated at this stage is called Transaction processing (transaction processing) or TP plan.

• 1th period-The 1th period use a subset of the available optimization rules to find the existing plans for the common format (common patterns).

If you already know that the planned cost is less than 1.0, the optimizer will stop here. The plan generated at this stage is called the Quick Plan (plans).

• The 2nd period--in this final period the optimizer is doing its best (pulls out all the stops) uses all of its optimization rules. It will also look for parallel (parallelism) and indexed views (indexed view) (if you are running Enterprise Edition).

The completion of the 2nd period is the balance between finding the cost of the plan and the time needed to optimize it. The plan generated during this period has the complete level of optimization (levels of "full").

How much is it going to cost?

The costs mentioned here cannot be measured in seconds or other meaningful expressions; it simply marks an arbitrary number representing the value of the planned resource consumption. However, in the early days of the Microsoft SQL Server world, its origins were the benchmark Checker (benchmark) (running points) on desktop computers.

In the plan, each operator has a bottom line cost, which is then multiplied by the size of the row and the estimated number of rows to get the cost of that operator, which is the cost of all these operators.

Because the cost comes from the bottom line value and is independent of your hardware speed, in each SQL Server appliance (Like-for-like version. Bo Main note: not related to the version. The cost of generating each plan is the same.

Because our select query is very simple, it exits the operation during the pre-optimization period because the plan is very obvious to the optimizer (a general plan). Now that there is a query plan, it executes to the query executor (Executor).

Query Executor (Executor)

The job of the query executor is not self-explanatory, it executes the query. More precisely, it executes the query by doing every step of retrieving or modifying data that involves interacting with the storage engine.

(information needs to be perfected here ...). ）

This select query needs to retrieve data, so the request is passed to the storage engine (Storage engine) to the access method via the OLE DB interface (Access Methods).

The Execution plan (query plan), which shows the output as the optimizer, is passed to the query Executor, which introduces the storage engine (Storage engine), which is queried by the executor (query Executor) through OLE as an interface to the access method (Access Methods).

access Method (Access Methods)

The access method is to provide a storage structure for your data and indexes, as well as a batch of code to modify the interface through data retrieval or data. It contains all the code that retrieves the data itself without performing an action, which passes the request to the buffer manager.

Suppose our SELECT statement needs to read the data of some record rows just on one page. The code for Access Methods allows the buffer manager to retrieve the page, so it can prepare an OLE DB Recordset to pass back to the relational engine (relational engine).

Buffer Manager (Buffer Manager )

Buffer Manager, as the name implies, manages the buffer pool, which represents the primary memory usage of SQL Server. If you need to read some record lines from the page (when we talk about the update query that mentions the modification data), Buffer Manager checks the data cache in the buffer pool to see if there is a cached page in memory. If the page is cached, the result is passed back to the access method (Access Methods).

If the page is not cached, then the cache manager (Buffer Manager) takes the page from the disk, puts it into the data cache, and passes the results back to the access Methods.

the point you need to keep in mind here is that you always deal with data in memory. before returning as a recordset , each new data you request is read , first read from disk, and then written back to memory (the data cache).

This is why SQL Server needs to maintain a minimum level of available pages in memory, and you cannot read any new data if the first time there is no space in the cache to place the data.

The access Methods code determines that a select query requires a new page, so it takes it to the buffer manager. The buffer Manager checks to see if it is already in the data cache and, if it is not found, loads it from the disk into the cache.

Data Cache

The data cache is always the largest part of the buffer pool, so it is also the largest memory user in SQL Server. Each data page read from the disk is written back before it is used.

Each row of this sys.dm_os_buffer_descriptors dynamic management view (DMV) represents each data page held by the current memory, and you can use this script to see how much space each database occupies in the data cache:

1 SELECT Count(*)*8/1024x768  as 'Cached Size (MB)'2, Casedatabase_id3          when 32767  Then 'Resourcedb'4         ELSE db_name(database_id)5         END  as 'Database'6  fromsys.dm_os_buffer_descriptors7 GROUP  by db_name(database_id), database_id8 ORDER  by 'Cached Size (MB)' DESC

The output will look similar to the following:

Cached Size (MB)  Database

3287              People

34                tempdb

12                ResourceDb

4                 msdb

In this example, the People database has a 3287 MB data page in the data cache.

The amount of time the page stays in the cache is determined by the least recently used (least recently USED:LRU) policy.

(There is information to be perfected ........) ）

a simple SELECT statement (query) life cycle summary

The entire life cycle of a select query is described here:

2. The client's SQL Server network Interface (SNI) establishes a connection with a network interface (SNI) that uses a network protocol such as TCP/IP in SQL Server. The TDS endpoint is then established on the TCP/IP connection and a SELECT statement is sent to SQL Server as a TDS message.
4. SNI on SQL Server splits the TDS message, reads the SELECT statement, and transmits an "SQL command" to the command parser.
6. The command parser checks whether the plan cache exists in the buffer pool, and the available query plan that matches the statement is received by the command parser. If it is not found, the query tree is created from the SELECT statement to the optimizer to generate the query plan.
8. The optimizer generates a 0 cost plan or a general plan in precompilation because this statement is too simple. The resulting query plan is then passed to the query executor to execute.
10. At execution time, the query executor decides to read the required data to complete the query plan, thus passing the request through the OLE DB interface to the access method in the storage engine.
12. The access method needs to read a page from the database to complete the request from the query executor, which allows the buffer manager to provide this page.
14. The Buffer Manager checks the data cache to see if it already exists in the cache. It is not cached, so take this page from the disk, put it in the cache, and pass it back to the access method.
16. Finally, the accessor method sends the result set back to the relationship engine and sends it back to the client.

SQL Server 2012:SQL Server architecture--The life cycle of a query (part 2nd)