Source:SQL Server Performance Tuning for Stored Procedures
...
One of the biggest advantages of using stored procedures over not using stored procedures is the ability to significantly reduce network traffic. And the more network traffic that can be reduced, the better the overall performance of your SQL Server-based applications.
...
- When an application executes a stored procedure, only a simple, small RPC (remote procedure call) is made from the client to SQL Server. But if the application is not using stored procedures, but sending Transact-SQL code directly from the client to SQL Server, network traffic can often very high. For example, if the amount of Transact-SQL code is 500 lines (and this would not be all that unusual), then it would take hundreds of network packets to transmit the Transact-SQL code from the client to SQL Server. On the other hand, if the 500 lines of Transact-SQL code are in a stored procedure, this code never has to travel the network, as it is already located on the server.....
....
*****
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.
....
To turn this feature off on at the stored procedure level, you can include the statement:
SET NOCOUNT ON
...
*****
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.
...
When a stored procedure is first executed (and it does not have the WITH RECOMPILE option), it is optimized and a query plan is compiled and cached in SQL Server’s buffer.
...
If the query in the stored procedure is exactly the same each time, and the query plan is the same each time, then this is a good thing. But if the query within the stored procedure is dynamic (for example, the WHERE clauses changes from one execution of the stored procedure to the next), then this may not be a good thing, as the query may not be optimized when it is run, and the performance of the query can suffer greatly.
...
If you know that your query’s query plan will vary each time it is run from a stored procedure, you will want to add the WITH RECOMPILE option when you create the stored procedure. This will force the stored procedure to be re-compiled each time it is run, ensuring the query is optimized with the correct query plan each time it is run. Yes, this will circumvent the reuse of cached query plans, hurting performance a little, but it is more desirable than reusing incorrect query plans. [6.5, 7.0, 2000, 2005] Updated 12-6-2005
*****
*****
Here’s a way to handle the problem of not knowing what parameters your stored procedure might face.
As it happens I have a solution, one that I’ve used a lot with success. It involves the creation of what I like to call a ‘delegator’. Consider again spTest. I propose to rewrite it like this:
CREATE PROCEDURE dbo.spTestDelegator (@query bit) AS
IF @query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
IF @query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
....
For best performance, all objects that are called within the same stored procedure should all be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner.object_name or schema_owner.object_name.
...
In addition, SQL Server cannot reuse a stored procedure “in-memory plan” over if the object owner or schema is not used consistently.
*****
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.
...
Unnecessary Stored Procedure Recompilations Due to Row Modifications and Automated Statistics Update
If your database has the “Auto Update Statistics” database option turned on, SQL Server will periodically automatically update the index statistics. On a busy database, this could happen many times each hour. Normally, this is a good thing because the Query Optimizer needs current index statistics if it is to make good query plan decisions.
....
To prevent unnecessary stored procedure recompilations, you should include all of your DDL statements at the first of the stored procedure so they are not intermingled with DML statements.
Unnecessary Stored Procedure Recompilations Due to Specific Temporary Table Operations
....
*****
Stored procedures can better boost performance if they are called via Microsoft Transaction Server (MTS)instead of being called directly from your application.
...
*****
When calling a stored procedure from your application, it is important that you call it using its qualified name. Such as:
exec dbo.myProcedure
instead of:
exec myProcedure
...
