August 18, 2011 at 7:02 am
Here's what I know, so we don't have to go over it again.
1. "Go" is the default batch separator for sending statements to SQL Server.
2. "Go" can be changed to something else, and is used by the client app to decide when to send a group of statements to the SQL Server for processing.
3. "Go" is NOT a TSQL key word.
4. Although the client may send a group of statements to SQL Server in a batch, this does NOT determine where a transaction begins or ends within or around said batch.
5. SQL Server will construct an execution plan based on the group of statements in the batch.
That said, what is the advantage of a batch in SQL Server?
I have read something about them being good for reducing network traffic, because it means less back and forth between client and server if you send a bunch of commands at once.
Addressing number 5, above, I am not clear on if this means that it will create a separate execution plan for each statement or on execution plan for the entire batch. Can't see how it would be advantageous to have a single execution plan for an entire batch. See example:
Select Column1 from Table1 where Column2 Is Null
Update Table3
Set Column8 = (Select Max(Column4)
From Table99
Where Column6 = 'Y')
Create Table Table5 (
ID Int Identity(1,1) Not Null,
FirstName VarChar(25) Null,
LastName VarChar(50) Null,
PhoneNumber Char(10) Null)
Go
Does it make sense to have a single execution plan for the above "batch"?
A transaction has definite affects in a transaction log, but what affect, if any, does batching statements have on a transaction log?
What brings this up is I have people around me that have definite ideas about what batches do on SQL Server, such as they think that it signifies a transaction, and I need to know all of the ins and outs of batches for future reference.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
August 18, 2011 at 7:09 am
A batch is just how the commands are packaged to SQL. There has to be a boundary somewhere, a start and end of the communication. It's nothing fancier than that
Execution plans are at a batch-level for ad-hoc queries, object-level for stored procs and functions. Your example would have a single entry in the plan cache that contains execution plans for two queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2011 at 7:36 am
So, to take this a little further, Is there any advantage to example 2 over example 1, besides network communication (in fact example 2 would cause MORE network communication, right)? For these examples each of the tables mentioned have several million rows, and a log that is set to Simple.
Example 1
Use FL_MPI
Alter Table dbo.PHARM_HEADER
Alter Column NUM_ASSOC_PRSCRIP varchar(12)
Alter Table dbo.PHARM_HEADER
Alter Column NUM_PRESCRIP varchar(12)
Alter Table dbo.PHARM_HEADER
Alter Column NUM_PRESCRIP_SUB varchar(12)
Alter Table dbo.PHARM_HEADER_Hold
Alter Column NUM_ASSOC_PRSCRIP varchar(12)
Alter Table dbo.PHARM_HEADER_Hold
Alter Column NUM_PRESCRIP varchar(12)
Alter Table dbo.PHARM_HEADER_Hold
Alter Column NUM_PRESCRIP_SUB varchar(12)
Alter Table dbo.PHARM_HEADER_TRIAL1
Alter Column NUM_ASSOC_PRSCRIP varchar(12)
Alter Table dbo.PHARM_HEADER_TRIAL1
Alter Column NUM_PRESCRIP varchar(12)
Alter Table dbo.PHARM_HEADER_TRIAL1
Alter Column NUM_PRESCRIP_SUB varchar(12)
Example 2
Use FL_MPI
Alter Table dbo.PHARM_HEADER
Alter Column NUM_ASSOC_PRSCRIP varchar(12)
Go
Alter Table dbo.PHARM_HEADER
Alter Column NUM_PRESCRIP varchar(12)
Go
Alter Table dbo.PHARM_HEADER
Alter Column NUM_PRESCRIP_SUB varchar(12)
Go
Alter Table dbo.PHARM_HEADER_Hold
Alter Column NUM_ASSOC_PRSCRIP varchar(12)
Go
Alter Table dbo.PHARM_HEADER_Hold
Alter Column NUM_PRESCRIP varchar(12)
Go
Alter Table dbo.PHARM_HEADER_Hold
Alter Column NUM_PRESCRIP_SUB varchar(12)
Go
Alter Table dbo.PHARM_HEADER_TRIAL1
Alter Column NUM_ASSOC_PRSCRIP varchar(12)
Go
Alter Table dbo.PHARM_HEADER_TRIAL1
Alter Column NUM_PRESCRIP varchar(12)
Go
Alter Table dbo.PHARM_HEADER_TRIAL1
Alter Column NUM_PRESCRIP_SUB varchar(12)
You see, I usually write my code as one big "batch", since that's how it's done in a stored proc, and instead of passing little pieces of the script to the server I just pass it all at once, which to me, seems like the smallest amount of network traffic, rather than several "batches".
As I said, I have encountered people who think that the batching will have an effect on things like the transaction log, in that putting a Go in between every statement in a script will cause the DB, set to Simple Recovery, to empty the transaction log as if it was a transaction "delimiter".
I even have people that believe you have to have a "Go" after a "Use DBName" statement. I have been running queries without "Go" in them for a long time now and suffered no ill effects that I am aware of. If I am missing something I am interested to learn what that is.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
August 18, 2011 at 7:47 am
Stamey (8/18/2011)
So, to take this a little further, Is there any advantage to example 2 over example 1, besides network communication
Not in this case.
Sometimes you have to do things in 2 batches because of the parser. An entire batch is parsed before any execution starts, so this would fail:
ALTER TABLE SomeTable
Add AnotherColumn INT;
SELECT Col1, Col2, AnotherColumn FROM SomeTable
It would fail because at the point the parser goes over that, SomeTable does not have a column called AnotherColumn, hence the select is invalid.
As I said, I have encountered people who think that the batching will have an effect on things like the transaction log, in that putting a Go in between every statement in a script will cause the DB, set to Simple Recovery, to empty the transaction log as if it was a transaction "delimiter".
Nope. In Simple recovery a checkpoint truncates the log. Nothing else. A Commit transaction also does not truncate the log in simple recovery
I even have people that believe you have to have a "Go" after a "Use DBName" statement. I have been running queries without "Go" in them for a long time now and suffered no ill effects that I am aware of. If I am missing something I am interested to learn what that is.
USE Database does not need to be in its own batch. Some statements (create proc, create view come to mind) to have to be the only statements in the batch.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2011 at 1:49 pm
GilaMonster (8/18/2011)
Stamey (8/18/2011)
As I said, I have encountered people who think that the batching will have an effect on things like the transaction log, in that putting a Go in between every statement in a script will cause the DB, set to Simple Recovery, to empty the transaction log as if it was a transaction "delimiter".
Nope. In Simple recovery a checkpoint truncates the log. Nothing else. A Commit transaction also does not truncate the log in simple recovery
Sorry, I didn't explain myself well, not that it matters. Neither the Go or an explicit transaction actually empties the log, but an open transaction will keep the log from emptying in a checkpoint, at least for the transaction. Those that are uninformed think that the "Go" will end the transaction, in effect, or do something else, to allow the log to be truncated when the checkpoint occurs.
I'm just trying to make sure my thought that the Go is pointless in most of the places that other people tend to put them is correct. I do understand about the creation of the column and using it need to be separated, as well as the creation of a procedure, and such things, but the majority of the time I see Go being used unnecessarily, perhaps more as a way to make the code look good, such as in many MS examples in BOL. The unfortunate part is that it leads people to believe it is required in places where it does not good.
A classic example from BOL:
USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N't2', N'U') IS NOT NULL
DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
DROP TABLE t1;
GO
CREATE TABLE t1
(a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
(a INT NOT NULL REFERENCES t1(a));
GO
There are no actual Go statements required in this script to run it.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
August 18, 2011 at 8:19 pm
As a bit of a side question, why are you trying to do these things from the client instead of within SQL Server itself?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2011 at 5:21 am
Simplest way to do it.
I have to put together scripts to change data types or expand columns, depending on the business requirement. As part of the data type change, from VarChar to Date or Numeric I need to validate the data and set invalid data to Null. I am working with a DB that has hundreds of millions of rows in it, and disks with limited space.
My goal is to put together these scripts so that they don't overload the disk with transaction log space, but some. like a data type change on a table with 400M rows is going to be unavoidable with a straight change, even with the DB set to simple recovery.
As I said, others around me believe that having a "Go" between each statement in the script is going to help the transaction log to be checkpointed and emptied more quickly. They are adamant enough that I felt the need to verify what I know about it, and learn anything I don' know, so that when I write my scripts, they will work as they are supposed to, regardless of the "Go" statement I am forced to put into them.
I generally do not use Go in my scripts unless it is required, such as between the drop of a proc and the creation of a proc. I have had others come to me and request that I add Go statements to my scripts because of their ideas on what Go does.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
August 19, 2011 at 5:38 am
Stamey (8/19/2011)
As I said, others around me believe that having a "Go" between each statement in the script is going to help the transaction log to be checkpointed and emptied more quickly. They are adamant enough that I felt the need to verify what I know about it, and learn anything I don' know, so that when I write my scripts, they will work as they are supposed to, regardless of the "Go" statement I am forced to put into them.
Adamant, but wrong
I generally do not use Go in my scripts unless it is required, such as between the drop of a proc and the creation of a proc. I have had others come to me and request that I add Go statements to my scripts because of their ideas on what Go does.
I tend to use GO between DDL statements. Yes, you can do without by without but I prefer it this way. Means I don't have to worry about parsing errors from earlier DDL statements that haven't yet been run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply