set nocount on and ad hoc query

[Brian Brian]

Hi All,

We are running an application with a lot of ad hoc queries in terms of
thousands per second (they are prepared). My question is that, if we append
SET NOCOUNT ON to each and every ad hoc query, will there be any benefit of
doing that? I understand it will help stored procedures...

we are running SQL 2005 64bit EE on Windows 2003 64bit EE. Application is
programmed in Java.

Thanks very much,
--
Brian

[Erland Sommarskog]

Brian Brian (Brian...@discussions.microsoft.com) writes:
> We are running an application with a lot of ad hoc queries in terms of
> thousands per second (they are prepared). My question is that, if we
> append SET NOCOUNT ON to each and every ad hoc query, will there be any
> benefit of doing that? I understand it will help stored procedures...

Well, if you don't need the rowcount, it's not going to hurt you to add
it. But if the batches are single queries, the effect may not be that great
either. It matters more if you have batches with many statements.

Rather than add SET NOCOUNT ON to the query batches, I think it is better
to set it when you connect. True that, will be an extra roundtrip. If you
have a central method to run a query, you can add SET NOCOUNT ON there.

It is also a good idea to add SET ARITHABORT ON, as some features requires
that setting to be on. (Despite it has no effect when ANSI_WARNINGS are on.)

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx