SQL Server 2000 T-SQL operation

SQL Server 2000 T-SQL operation

T-SQL (trantsact-SQL) is a structured query language developed by Microsoft on the basis of Sybase.ProgramAnd the tools for communicating and accessing stored procedures with SQL Server. Included ansi89 and ansi92 standards. So T-SQL is not a standardProgramming LanguageIt must be analyzed and run through the data engine of SQL Server. How does SQL Server compile and run T-SQL statements?

SQL Server performs the following three steps when processing any T-SQL statement:

1. parse the T-SQL statement

2. Compile the T-SQL statement

3. Execute the T-SQL statement

When a T-SQL batch is submitted to the SQL Server server, the server returns this T-SQL batch as a whole for analysis, in optimization, compilation, and finally in step-by-step execution.

I. Parsing

The so-called "resolution" refers to the SQL server command parsing module first checks the T-SQL batch processing syntax process, if no error is found, the command parser willSource codeSubdivided into multiple logical units, such as keywords, identifiers, and operators. the command parser then constructs an internal structure, and finally generates the detailed steps required for DDL or DDM operations through this internal structure. if the T-SQL batch contains a query, then this internal structure becomes the query tree if the T-SQL batch is a process, therefore, this internal query is converted into a sequence tree ).

Figure 1: link engine section of SQL Server

We can see on the left of Figure 1, mainly T-SQL parsing, compilation and query optimization (Query Optimizer ). this is a key part of SQL Server Running T-SQL. on the right side of the graph is the Execution Component, after the T-SQL statement is compiled, it will be directly passed to the execution structure for running. the middle part is the SQL manager that controls the parsing, compilation, and execution of the entire T-SQL batch. SQL message is the (TDS) data received from the client. express Services libary converts data, takes data into consideration, computes and counts, and formats the output data.

Ii. Compile

This step is mainly to generate the sequence tree as an execution plan, the query optimizer is mainly to evaluate the resources to be retrieved by the T-SQL statement, generation I/O time, over time and other logic processing time. then the query optimizer tries to use a minimum resource solution.

This solution also includes the list of tasks required for execution (such as security check, constraint check, trigger check, and so on). This becomes the execution plan.

Iii. Execution

The Execution Component runs and remains in the cache according to the execution plan. different steps of the Execution Plan will be sent to different components of the relational engine for processing: DML manager, DDL manager, stored procedure manager, transaction processing manager and Utility Manager. the processing result will be collected and merged as a result set and returned to the caller.

The execution plan will be retained for a period of time in the cache, if the same user or another user sends a T-SQL batch of similar requests, the relational data engine will first search for matching execution plans in the cache. if the execution plan exists, it runs. If not, SQL Server parses and compiles the T-SQL batch.

If the memory required by SQL Server is insufficient, it will delete some execution plans from the memory. SQL server has a good "aging"AlgorithmIt can count the usage time and times of an execution plan. If the memory is large enough, the execution plan can be infinitely increased to the memory.

Iv. Reuse of simple query execution plan

Simple T-SQL batch processing can only be reused in 2 cases:

1. the text for the second query must be exactly the same as the text described in the plan execution in the cache. Each item must match, including space, line feed, and strip, SQL Server is also case sensitive.

2. query fully-qualified database objects for reuse of execution plans.

Select * Form pubs. DBO. Sales

Therefore, the execution efficiency of "*" is higher than that of select column_list from tablename.

5. reuse of the stored procedure execution plan:

One of the main reasons why stored procedures are more efficient than simple queries is that the execution plan of stored procedures can be conveniently reused. comparison diagram: If you execute a simple query three times, SQL Server needs to execute the parsing-compilation-execution process three times, the stored procedure is most likely parsed and re-compiled before the first execution.

The execution plan of the stored procedure is divided into two parts:

1. The reentrant part can be used by the number of stored procedures;

2. Including the data context, that is, the parameters of the stored procedure during execution;

There is a component in SQL Server-lazywriter to determine whether the execution plan in the cache will be reused and whether more memory needs to be applied. if the following operations are performed, SQL Server immediately deletes the execution plan from the cache and releases the memory:

L significantly changed the data volume;

L create and delete indexes;

L add and modify constraints;

L change the index distribution information;

L display call sp_recompile to re-compile the stored procedure or trigger;

When SQL Server creates an execution plan, it will "compile the cost factor" for the execution plan ". the value of this cost factor depends on the resource overhead required to create the execution plan. for example, the compile cost factor for a large execution plan is 8, while the compile cost factor for a small execution plan is 2. each time a client references the execution plan, its "Age" will increase by a value of the compilation cost factor.

How does the SQL Server lazywriter work? SQL Server's inert writer is used to reduce the "Age" of execution plans. The inert writer process periodically traverses all execution plans in the cache and reduces the "Age" of execution plans by 1, when the compile cost factor of the execution plan is 0, SQL Server will reclaim the memory allocated to the execution plan.

Note:

The SQL Server Optimization Component (Query Optimizer) automatically re-compiles the stored procedure when updating the table data referenced by the stored procedure, and updates the execution plan. however, when you add an index, SQL server does not automatically recompile the stored procedure. You need to manually refresh the cache execution plan (the simplest way is to restart the MSSQLServer service ), this indicates that the stored procedure will be re-compiled, or you can manually force the re-Compilation:

Exec sp_recompile sp_storedprocedurename

SQL Server also provides another efficient re-compilation method: if a large number of stored procedures and triggers reference a table, and also add table indexes to improve the retrieval efficiency. here we can re-compile this table to re-compile all the stored procedures and triggers:

Exec sp_compile tablename.

After re-indexing or performing the Index Update operation, you must re-compile the stored procedure. However, this is the best way to restart the server.