SET NOCOUNT ON

SET NOCOUNT ON and ASYNC_NETWORK_IO

SET NOCOUNT ON

From BOL , SET NOCOUNT ON the count is not returned. When is OFF, the count is returned.

Additional, prevents the sending of DONE_IN_PROC messages to the client . This is used within stored procedures and triggers to avoid showing the affected rows message. (XXX rows affected)

So , after every statement , in a stored procedure or trigger, sql server sends a message DONE_IN_PROC (rows affected) .With SET NOCOUNT ON, you can suppress these messages.

If you are not in a stored procedure , if you are in a batch , SQL Server sends DONE message/token for each statement.  This can lead (in case of client not consume it fast) to waits s ASYNC_NETWORK_IO.  To reduce it , you can use a temp stored procedure or sp_executeSQL .

When a statement is executed, the result ends with a DONE, DONEPROC, or DONEINPROC, depending of the where this statement is: stored procedure, batch…

• DONE_IN_PROC, DONE_PROC are sent for statements executed in stored procedures , triggers
• DONE are sent for statements executed in batch

Conclusion:

SET NOCOUNT ON , suppress only DONE_IN_PROC and not DONE messages.

S

Simple parameterization

I was checking my cache plan , and founded that there was a database with more then 44k+ plans , that are only one time used.

Digging up, I discovered a query and asked my self , way it wasn’t parameterized.

Here is a sample:

select *
from sales.SalesOrderHeader as soh
where soh.SalesOrderID = 43675

 

select TOP(1) *
from sales.SalesOrderHeader as soh
where soh.SalesOrderID = 43675

 

Here is the execution plan(part of it)

 

and xml representation:

 

Simple parameterization can happens , in the following situation :
– must be a trivial plan . if it’s not trivial, no simple parameterization
in xml plan is : StatementOptmLevel=”TRIVIAL”

– the query must not have JOIN, IN, TOP,GROUP BY, HAVING, Distinct , Subqueries …

 

Conclusion:

QO decide to parameterize a query when it consider it safe to param , in a trivial plan , and not have : joins, In, Top, subqueries….

 

S