Talking about the operation of SQL Server 2000 T-SQL
T-SQL (TRANTSACT-SQL) is a structured query language developed on the basis of Microsoft, is a tool for application and stored procedures and SQL Server communications and access. Include ANSI89 and ANSI92 standards. So T -SQL is not a standard programming language, which must be analyzed and run through the data engine of SQL Server, how does SQL Server compiles and runs T-SQL statements?
SQL Server has passed the following three steps when processing any T-SQL statement:
1. Resolution T-SQL statement
2. Compile T-SQL statement
3. Execute the T-SQL statement
When a T-SQL batch is submitted to the SQL Server server, the server is analyzed as a whole, optimized, compiled, and finally executed step by step.
I. Analysis
The so-called "parsing" means that the SQL Server command parsing module first checks the process of T-SQL batch syntax. If an error is not found, the command parser divides the source code into multiple logical units, such as: keywords, identifiers, and operations Vallation. Then the command parser builds an internal structure and finally generates the detailed steps required for DDL operations or DDM operations through this internal structure. If the T-SQL batch contains a query, then this internal structure is a query tree ( Query Tree, if the T-SQL batch is a process, then this internal query is a sequence tree.
Figure 1: Part of the relationship engine part of SQL Server
You can see the left side of Figure 1, mainly T-SQL analysis, compilation and query optimization. This is a very critical part of SQL Server running T-SQL. On the right side of the figure is the execution component, when T The -sql statement is compiled and passed directly to the execution structure to run. In the middle part is the SQL manager, control the parsing, compilation and execution of the entire T-SQL batch. SQL Message is from the client (TDS) data accepted from the client .Express Services libary is data conversion, data, and calculation and statistics, while also formatting data.
II. Compilation
This step is mainly to generate the sequence tree as an execution plan. The Query Optimizer is mainly evaluated to evaluate the resources you want to retrieve in the T-SQL statement, generate I / O time, time and other logic. Time to process. The Query Optimizer is then tried to take advantage of a minimum resource.
This scenario includes a list of tasks (such as: security check, constraint check, trigger check, etc.). This is an execution plan.
Executive
The execution component is running and rearing in the cache in the execution plan, and the different steps that perform the plan will be sent to different components of the relational engine for processing: DML Manager, DDL Manager, Storage Procedure Manager, Transaction Manager, and Utility Manager. Processing results will be collected by the result set to return to the caller.
The execution plan will be kept in the cache for a period of time. If the same user or other user issues a similar request T-SQL batch, the relational data engine will give priority to the implementation plan for matching in the cache. If the execution plan will exist With operation, if there is no existence, SQL Server will resolve and compile this T-SQL batch.
If SQL Server needs to be insufficient, it removes some execution plan from memory. SQL Server has a good "aging" algorithm that can count the time and number of times of execution plan. If the memory is enough, Execution planning to memory in an infinite increase.
Fourth. Simple query execution planning
Simple T-SQL batch can only be reused in the case of 2:
1. The text of the second query must be exactly the same as the text described in the cache, each of which matches, including: space, wrap, rearrangement, including characters in case sensitive SQL Server Size.
2. Query the completely modified database object to reuse the execution plan.
Select * form pubs.dbo.sales
So in this case: "*" is higher than the statement of select column_list from tablename.
5. Reuse of the stored procedure execution plan:
The stored procedure is a major reason why simple query execution efficiency is: the execution plan of the stored procedure can be used to be reused. Figure: In implementing a simple query 3 times, SQL Server needs to perform 3 parsing - compile - compile - execution process And the stored procedure is most likely to be resolved and recompiled once before the first execution. The execution plan of the stored procedure is part:
1. Reproduction, can be used in the stored procedure of the person;
2. Including the part of the data context, that is, various parameters of the stored procedure during execution;
There is a component-inert writer in SQL Server to determine if the execution plan in the cache is reused and if you need to apply more memory. If there is a following operation, SQL Server will immediately redeese Perform planning, release memory:
l Obviously change the amount of data;
l Create and delete index;
l Adding and changing constraints;
l Change the distribution information of Index;
l Displaying sp_recompile to recompile the stored procedure or trigger;
When SQL Server establishes a program, a "compilation cost factor" will be planned. This cost factor value depends on the resource overhead required to create the execution plan. For example, the compilation cost of a large execution plan is 8, and a small compilation cost factor of a small execution plan is 2. His "age" will increase the value of compilation costs each time a client references the execution plan.
How does SQL Server's inert writer work? SQL Server's inert writer is used to reduce the "age" of the execution plan, and the inert writer process will cycle all the execution plans in the cache, and The "age" of the plan will be executed. 1. When the value of the programming cost factor is performed, SQL Server will reclaim the memory assigned to the execution plan.
Remarks:
The SQL Server Optimization Component automatically recomesces the stored procedure when the stored procedure reference is updated, and updates the execution plan. But when you add an index, SQL Server does not automatically recompile the stored procedure, you need to manually Refresh the cache execution plan (the easiest way, you can restart the MSSQLServer service), this is the stored procedure to be recompiled, or you can manually compile:
Exec sp_recompile sp_storedprocedureName
SQL Server also provides another highly efficient recompile method: if there is a large number of stored procedures and triggers reference a table, and add table index to improve the efficiency of the search. This is what we can use to recompile this table. Rebate all stored procedures and triggers:
EXEC SP_Compile Tablename.
All we have re-established an index or perform an operator that updates an index, must recompile the stored procedure, but this is the best way to restart the server.
reference:
Http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sqlquerproc.asp
