This post will describe how a SQL Server will PROCESS a query. The first time through running an Ad Hoc query or Stored Procedure, SQL Server will go through each of the following steps.
- The first step is to Parse the statement into keywords, expressions, and operators. This is where the syntax of your query statement is checked for accuracy.
- The second step is to Resolve object names (Tables, Views, Columns, etc.) to see if they exist. Now, I needed an ‘R’ word to spell the word PROCESS, but if you read other books and blog posts, this step is technically called the Binding phase because it binds aliases to column or tables. It will also check data types and perform implicit data type conversions if necessary. Both the first and second step are where you will find syntax errors. In most cases, these errors are caused by misspellings or putting commas in the wrong place.
- The third step is to Optimize the query. This is where the query optimizer will find different ways of locating data from your tables based on available indexes and/or statistics. Once it figures a good enough plan to retrieve your result set using the least amount of resources, it will create an Execution Plan. Errors would only occur during this stage if there is a lack of hardware resources. Most compile errors happen at the next step.
- The fourth step is to Compile the Execution Plan and store it in the Procedure Cache for future use.
- Finally, the Execution Plan will Execute and hopefully return the desired SQL Sets. This is where Run-Time errors will occur that need to be handled using Exception Handling. (SQL Sets are normally called recordsets or rowsets, but I’m making a blog post on the PROCESS of queries not the PBOCERS of queries).
Additional submissions of the query from a Stored Procedure, will check the Procedure Cache for existing or similar Execution Plans that could be re-used for the query. If this is the case, the existing Execution Plan will be used to retrieve the SQL Sets use the Execution Context of the Stored Procedure.
It is possible for an Ad Hoc query to also re-use an existing plan from the procedure cache if the only thing that has changed in the query is the parameter value. Example: If a query had been written WHERE ProductID = 732 and was re-written WHERE ProductID = 738 the execution plan could be re-used, but if anything else changed, even by adding a space, the optimizer would recompile a new execution plan.
One final thing. You will noticed that you can have both an Estimated Execution Plan and an Actual Execution Plan. The difference is the first will show you what the plan would look like before the plan is compiled and the second is what the plan is after it has been compiled and placed in the procedure cache.