NO COUNT
By default, every time a stored procedure is executed, a message is sent from the server to the client indicating the number of rows that were affected by the stored procedure. Rarely is this information useful to the client. By turning off this default behavior, you can reduce network traffic between the server and the client, helping to boost overall performance of your server and applications.
To turn this feature off on at the stored procedure level, you can include the statement:
SET NOCOUNT ON at the beginning of each stored procedure you write. This statement should be included in every stored procedure you write.
If you want this feature turned off for your entire server, you can do this by running these statements at your server:
SP_CONFIGURE 'user options', 512 RECONFIGURE
but i don't recommend this as in certain cases you might need that all important row count.
Keep Transact-SQL transactions as short as possible within a stored procedure. This helps to reduce the number of locks, helping to speed up the overall performance of your SQL Server application.
Three ways to help reduce the length of a transaction are to:
1) break up the entire job into smaller steps (or multiple stored procedures or user defined functions) so each step can be committed as soon as possible
2) take advantage of SQL Server statement batches, which acts to reduce the number of round-trips between the client and server.
3) if there is a certain set of statement or calculations that is being done repeatedly in procedures use user defined functions to reduce code size and improve performance
Procedure Naming
If you are creating a stored procedure to run in a database other than the Master database, don't use the prefix "sp_" in its name. This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly.
The reason for this is that by default, any stored procedure executed by SQL Server that begins with the prefix "sp_", is first attempted to be resolved in the Master database. Since it is not there, time is wasted looking for the stored procedure.
If SQL Server cannot find the stored procedure in the Master database, then it next tries to resolve the stored procedure name as if the owner of the object is "dbo". Assuming the stored procedure is in the current database, it will then execute. To avoid this unnecessary delay, don't name any of your stored procedures with the prefix "sp_"
Before you are done with your stored procedure code, review it for any unused code, parameters, or variables that you may have forgotten to remove while you were making changes, and remove them. Unused code just adds unnecessary bloat to your stored procedures, although it will not necessarily negatively affect performance of the stored procedure.
Executing Strings
When you need to execute a string of Transact-SQL, you should use the sp_executesql stored procedure instead of the EXECUTE statement. Sp_executesql offers two major advantages over EXECUTE. First, it supports parameter substitution, which gives your more options when creating your code. Second, it creates query execution plans that are more likely to be reused by SQL Server, which in turn reduces overhead on the server, boosting performance.
Sp_executesql executes a string of Transact-SQL in its own self-contained batch. When it is run, SQL Server compiles the code in the string into an execution plan that is separate from the batch that contained the sp_executesql and its string.
