Tips and tricks of creating stored procedures on SQL Server

Recently, we helped one of our customers to migrate one of their existing applications to .NET Framework 3.5 employing LINQ technologies. We organized the application into different layers (i.e., layered architecture). Unfortunately, we are not allow to change their stored procedures to their equivalent .NET codes. After the migration, we discovers that the application runs very slowly. Upon investigation, we noticed all the existing user-defined stored procedures prefix with a "sp_".
 
I would advise all developers to avoid using the sp_  prefix when you name local stored procedures, to easily distinguish system stored procedures. Another reason to avoid the sp_ prefix for stored procedures in a local database is to avoid unnecessary searches of the master database. When a stored procedure with a name beginning with sp_ is called, SQL Server searches the master database before it searches the local database.
 
Ok. Cut the story short. The following are my tips and tricks on optimizing the performance of user-defined stored procedures on SQL Server:
 
1. Qualify object names referenced by a stored procedure with the appropriate schema name. This ensures that tables, views, or other objects from different schemas are accessible within the stored procedure. If the referenced object name is not qualified, the stored procedure’s schema is searched by default.
 
2. Design each stored procedure to accomplish a single task.
 
3. Create, test, and troubleshoot your stored procedure on the server, and then test it from the client.
 
4. Avoid using the sp_  prefix when you name local stored procedures, to easily distinguish system stored procedures. Another reason to avoid the sp_ prefix for stored procedures in a local database is to avoid unnecessary searches of the master database. When a stored procedure with a name beginning with sp_ is called, SQL Server searches the master database before it searches the local database.
 
5. Use the same connection settings for all stored procedures. SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS options when a stored procedure is created or altered. These original settings are used when the stored procedure is executed. Therefore, any client session settings for these SET options are ignored during stored procedure execution.
 
6. Minimize the use of temporary stored procedures to avoid contention on the system tables in tempdb, a situation that can adversely affect performance.
 
That’s all!!
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: