July 24, 2013 at 12:34 pm
Hello,
I am facing a "problem" with the TDS network protocol. When I send a batch like the one below I receive back from SQL Server a lot of packets with the DONE message saying that a statement was executed. I understand that for each while iteration this message is generated. The problem is when I execute this command over a WAN link I can allocate all the network bandwidth of my WAN link for these messages.
declare @i int
set @i = 0
while @i < 10000
begin
set @i = @i + 1
end
From what I could find I can not disable this DONE message. Does anyone know if I can disable this message?
I can execute this batch directly in the server, but I wish to know if I could disable it.
Thanks,
Thanks,
July 24, 2013 at 1:09 pm
Take a look at:
http://it.toolbox.com/blogs/programming-life/tsql-tip-use-nocount-on-19362
This normal best practice.
July 24, 2013 at 2:10 pm
Hello David,
I tried that, but it did not change anything. The DONE message is sent even with the NOCOUNT enabled.
Thanks for you help.
July 24, 2013 at 2:32 pm
How is the batch being sent to the server? Is it through an ODBC or other connection that may use settings that would override the ones you set? I'd try a profiler trace on the connection to see what's actually hitting the server and which re-sets get in last.
July 24, 2013 at 4:13 pm
The text for SET NOCOUNT ON, says: SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. DONEINPROC is generated in place of DONE, if you are in a stored procedure. So that would indicate that if you put the batch in a stored procedure with SET NOCOUNT ON, you should not get any packets, although I would not really count on it. But give it a try and report back!
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 25, 2013 at 5:20 am
Hello,
I created a SP with the code I sent before and I also added a SET NOCOUNT ON at the beginning. This way the network traffic generated was very low. I think this way I will not have this "problem".
The only problem is that now I need to put my ad hoc batches inside a SP.
Thanks for the help!
July 25, 2013 at 3:53 pm
Interesting. That would indicate a difference between stored procedures and loose batches that I was unaware of.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 26, 2013 at 2:43 pm
Exactly how did you conclude that you got those DONE packets even with SET NOCOUNT ON? I spoke with Jonathan Kehayias, and he ran a test with Wireshark, and he says there are no DONE packets when NOCOUNT is ON.
He put forth the suspicion that you had some sort of statement-level trace running. This can have quite a penalty on a tight loop like yours.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 26, 2013 at 2:59 pm
Hi,
I executed the script from SSMS and captured a network trace with Network Monitor 3.4. I filtered the capture to capture only packets where the source our destination port was 1433. During the tests I was connected only to one SQL Server server.
The first run, with NOCOUNT OFF, generated around 200 packets. Most of the packets (90%) were coming from the server to my workstation. Then I executed the script again, but now with NOCOUNT ON. Again around 200 packets were generated. And most were sent from the server to my workstation.
From the packets' details I can see a lot of TokenType with DONE. So I conclude they are DONE packets.
July 26, 2013 at 3:30 pm
I played some with Wireshark (which I don't have any previous experience of) and I was able to confirm your findings. I did not analyse whether all packets are DONE packets, but there is a lot of traffic for a batch, even if NOCOUNT is ON. This is not true for a stored procedure.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply