SQL Server Performance Forum – Threads Archive
SET NOCOUNT ON — When it is recommended
Hi all I have all procedures in Database Server,My Application reside in Application serverIn this case my procedure required SET NOCOUNT ON,if it thire what was the problem,if it is not thire what was the problem
Please advance me.
Thanks in Advance
SPONGURU
"If Iam Wrong suggest me Right"
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. So if you have SET NOCOUNT ON it won’t return you number of rows processed which reduce the network traffic —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/
You should use SET NOCOUNT ON always in your procs. It should be straight the first command in the proc. As Dinesh mentioned does it suppress the DONE_IN_PROC messages and can many times speed up procs considerably. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
As it is only available in parse-time option is set when the code is parsed, so it will be set even if the branch of code it’s in is never executed. A run-time option is set only when
that part of code really does execute. See "SET Options" in Books Online for more details. As you are in SQL 2005 following blogs should help about recompilation issue too:
http://sqlserver-qa.net/blogs/perft…op-stored-procedures-that-are-recompiled.aspx http://sqlserver-qa.net/blogs/perftune/archive/2007/05/14/reset-cached-plan-how-to-and-what-to.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Use SET NOCOUNT ON is a good habit can speed up proc performance.
]]>