Menu
There is a stored procedure that we're trying to increase the performance of... at this point we're looking for non-intrusive 'fixes'.
This sproc gets called about 500,000 times a day - and it can call into up to 50 other stored procedures below. - DON'T ASK
Is there a way to SET NOCOUNT ON at the top of one sproc, and have it propagate down into all sprocs and statements below?
EDIT: Judging by the first two answers below - that leaves me to ask... is there a way to set the "user option" in the connection string or something so that it works for one 'user'? - any kind of 'secret tip' like this would be great.
558
For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
Using SET NOCOUNT ON can improve performance because network traffic can be reduced. SET NOCOUNT ON prevents SQL Server from sending DONE_IN_PROC message for each statement in a stored procedure or batch of SQL statements.
SET NOCOUNT ON/OFF statement controls the behavior in SQL Server to show the number of affected rows in the T-SQL query. SET NOCOUNT OFF – By default, SQL Server shows the number of affected rows in the messages pane. SET NOCOUNT ON – We can specify this set statement at the beginning of the statement.
When ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL. If SET ANSI_NULLS is not specified, the setting of the ANSI_NULLS option of the current database applies.
The short answer is no this is not possible to propogate it. You have to explicitly say:
SET NOCOUNT ON
A global change can be made based on the answer by @user92546 but I am always a bit wary about global changes. The minute you need it you realize that a global change was made...Proceed with caution :).
144
See User Options.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
