3

I've been reading this article on Microsoft website https://technet.microsoft.com/en-us/library/ms175502(v=sql.105).aspx.

It mentions

A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan.

I thought each query (statement) gets its own execution plan. Is execution plan per-batch or per-statement?

dp0891
  • 45
  • 3
  • If you run two statements simultaneously, do you get one plan or two? – Jacob H Feb 08 '18 at 20:52
  • @JacobH I see two diagrams, but I am not sure of how many 'execution plans' I am seeing as I am confused by the definition of an execution plan. Maybe they are just parts (statement-compiled) of execution plan (batch-compiled)? If I put two statements then put a GO, maybe one plan? If I put GO after each statement, maybe two plans? The article mentions statement-level compilation as well. – dp0891 Feb 08 '18 at 21:56
  • Yes, but when you run two statements you get one *Execution Plan*, potentially having multiple *Batches*, containing multiple *Query Plans*. – Jacob H Feb 09 '18 at 13:46
  • @JacobH This is very interesting, but confusing at the same time. Regardless of whether I separate my two statements by 'GO' or not, the two statements seem to be in separate batches within XML. I thought GO was used to separate batches? I also see that the plan have one element wrapping other elements; so execution plan is per a 'batch sequence' instead of a batch? – dp0891 Feb 09 '18 at 14:45
  • Try it and see? – Jacob H Feb 09 '18 at 14:46
  • @JacobH I did try and see. I am not sure what is a batch sequence. I also don't know why GO seems to have no effect – dp0891 Feb 09 '18 at 14:48
  • It does, but it's also a bit confusing. Check the `StatementID` value in the XML. This post has a decent explanation of GO https://stackoverflow.com/a/20711983/7948962 – Jacob H Feb 09 '18 at 14:53

1 Answers1

1
"SQL Server compiles the statements of a batch into a single executable 
unit, called an execution plan. The statements in the execution plan are 
then executed one at a time"

SQL server makes an Execution Plan for the batch. Each statement has a Query Plan that makes up part of the Execution Plan. You can demonstrate this by running several statements in a single window and viewing the query plans, each with a piece of info labeled "relative to the batch". The confusion probably lies in the fact that if you run a single statement your one query plan is the Whole execution plan. The article OP referenced made the assumption the readers were familiar with the Execution Plan/Query Plan dynamic.

The important things to take away from the article linked by OP in the question are the Rules For Using Batching. Specifically the first two: 

--If you CREATE a rule, constraint, or view in the batch, you cannot 
reference   it by another statement in the same batch.

--If you alter a table, you cannot utilize the new schema in a different 
query   in the same batch.

This implies to me that SQL server caches schema info for all statements in a batch before executing them, and does not update the schema info while a batch is being processed.

Another, and probably more important and more often utilized, element of batching statements from the application to the server is the use of transactions. You can use a transaction to manage your commits and rollbacks in the event of errors within any of the statements you batched. This touched on briefly in the linked article near the top under the section titled "Batches"

user7396598
  • 1,269
  • 9
  • 6
  • The term isn't reused, you've just used it incorrectly. SQL Server builds an `Execution Plan` with multiple `Query Plans`. You can demonstrate this easily by running two statements, saving the plan as XML, then open it up in a text editor. – Jacob H Feb 09 '18 at 13:48
  • In management studio they are called "Execution Plans". The options for making them visible are "Display Estimated Execution Plan" and "Include Actual Execution Plan". I believe this terminology is what was causing the OP's confusion. I will update wording of the answer. – user7396598 Feb 09 '18 at 15:45