Stored Procedures Optimization Tips (performance tuning) in sql server

We can optimize the Stored Procedure with making simple changes in the code. Below are some tips which can make our Stored Procedures optimize.

Ø  Don't use "SELECT*" in a SQL query

Ø  Try to avoid using temporary tables inside your stored procedures

Ø  Use Proper Indexes

Ø  Call stored procedures using their fully qualified name

Ø  Don't use the prefix "sp_" in the stored procedure name

Ø  Avoid using cursors

Ø  Avoid DISTINT and ORDER BY

Ø  CAST and Convert

Ø  Try to avoid using DDL (Data Definition Language) statements inside your stored procedure

Ø  Try to avoid IN

Ø  Use TRY-Catch for error handling

Ø  Avoid More where clause Hits

Ø  Avoid unnecessary variables 

Ø  Try to avoid dynamic SQL

Ø  Avoid to use COUNT () aggregate in a sub query

Ø  SET NOCOUNT ON

Ø  Use the sp_executesql stored procedure instead of the EXECUTE statement

Ø  SELECT Vs SET

Ø  Include Column List in Insert statement

Ø  Avoid Scalar Function Call in Column list and where clause

Ø  Keep the Transaction as short as possible

Ø  Try to avoid deadlocks

Ø  Avoid using DDL 

Ø  Try to use UNION to implement an "OR" operation

Ø  Break down a very large stored procedure into several sub stored procedure

Read more in details below.

Don't use "SELECT*" in a SQL query

Try to use only required number of column in select clause instead of *. Using * returns all columns, which unnecessarily create a fat RecordSet. For example

  

Try to avoid using temporary tables inside your stored procedures

Using temporary tables inside stored procedures reduce the chance to reuse the execution plan. Try to use variable table instead of temporary table. Temp tables can cause SP to recompile. If we use the variable table SP will not be recompile.

If result set doesn’t have the huge number of record the use variable table.

Use Proper Indexes

Index scans and index seeks are much faster than table scans. SO identify the table scans from the execution plan. But when a table returns smaller number of row, then it is better to use a table scan.

Call stored procedures using their fully qualified name

The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name.

Using fully qualified names eliminates any confusion about which stored procedure we want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names. See the example

Don't use the prefix "sp_" in the stored procedure name

The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend using the prefix "sp_" in user-created stored procedure names as SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, followed by the stored procedure using dbo as the owner (if one is not specified).

When we have the stored procedure with the prefix "sp_" in a database other than master, the master database is always checked first. If the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Avoid DISTINT and ORDER BY

If we don’t need the DISTINCT/ORDER by clause, then try to avoid so. Unnecessary DISTINCT or Order by clause cause extra work for the database engine. Hence these are making performance slower.

CAST and Convert

Try to use CAST instead of CONVERT. CAST is ANSI-92 stander but CONVERT works in MS SQL only. Better to use CONVERT only when we need to format the DATETIME datatype with the style option. CAST can’t do this.

 Try to avoid using DDL (Data Definition Language) statements inside your stored procedure

Using DDL statements inside stored procedures also reduce the chance to reuse the execution plan.

Try to avoid IN

Try to avoid IN. while checking the existence of some values, then use EXISTS instead of IN. IN counts the NULL values also, but EXISTS not. EXISTS returns Boolean (Yes/No) but IN returns all values hence result set for IN is heavier then EXISTS.

Use TRY-Catch for error handling

Use Try catch for Error handling.

BEGIN TRY --Your t-sql code goes here END TRY BEGIN CATCH --Your error handling code goes here END CATCH

Learn more about Try and Catch Read Here:  ErrorHandling in SQL Server

http://bageshkumarbagi-msbi.blogspot.in/2016/06/error-handling-in-sql-server.html

Avoid More where clause Hits

Avoid unnecessary conditions in where clause. Also try to avoid a function in where clause as it presents Sql server engine to do index seek. Even it forces SQL full index scans or even table scans.

Avoid unnecessary variables 

Use as few as possible variables. It frees spaces in cache.

Try to avoid dynamic SQL

Unless really required, try to avoid the use of dynamic SQL because. Dynamic SQL is hard to debug and troubleshoot. If the user provides the input to the dynamic SQL, then there is possibility of SQL injection attacks.

Avoid to use COUNT () aggregate in a sub query

Ø  When we use COUNT (), SQL Server does not know that we are doing an existence check. It counts all matching values, either by doing a table scan or by scanning the smallest non-clustered index.

Ø  When we use EXISTS, SQL Server knows we are doing an existence check. When it finds the first matching value, it returns TRUE and stops looking. The same applies to using COUNT () instead of IN or ANY.

SET NOCOUNT ON

With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information.

So we recommend using SET NOCOUNT ON for the sake of performance unless there is a very good reason for using it.

CREATE PROC GetEmployeeDetail AS SET NOCOUNT ON; --Procedure code here SELECT EmployeeKey,FirstName,LastName FROM DimEmployee -- Reset SET NOCOUNT to OFF SET NOCOUNT OFF; GO

 

Use the sp_executesql stored procedure instead of the EXECUTE statement

The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improves readability of our code when many parameters are used.

When we use the sp_executesql stored procedure to execute a Transact-SQL statement that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

SELECT Vs SET

A single Select statement can assign values to different variable.

DECLARE @EKey varchar(10), @Fname varchar(50), @Lname varchar(50); select @EKey ='1',@Fname='Bagesh',@Lname='Singh'

In set we need to assign the multiple times.

DECLARE @EKey varchar(10), @Fname varchar(50), @Lname varchar(50); SET @EKey ='1' SET @Fname='Bagesh' SET @Lname='Singh'

Select is faster then SET.

Include Column List in Insert statement

Best practices to use the columns list in our insert statement.

  

This allows we change the format of our table by adding or removing columns without breaking our Insert statement.

Avoid Scalar Function Call in Column list and where clause

Don’t execute scalar function in column list in a select statement or where clause, If the scalar function returns data.

Doing this cause cursor like performance. Function will be called for every row returned. We can replace scalar function with using below method

Ø  Using Join

Ø  Inline table value function

Ø  Table variable

Ø  View

Ø  Stored Procedure

Keep the Transaction as short as possible

Avoid nested transactions.

Ø  Commit only save data from the outer most transaction.

Ø  Rollback nukes all transaction, both inner most and outer most.

Ø  The length of transaction affects blocking and deadlocking. 

Ø  Exclusive lock is not released until the end of transaction.

The transaction should be kept as short as possible.

Try to avoid deadlocks

Ø  Always access tables in the same order in all our Stored Procedures and triggers consistently.

Ø  Keep our transactions as short as possible. Touch as few data as possible during a transaction.

Ø  Never, ever wait for user input in the middle of a transaction.

Avoid using DDL 

Using DDL statements inside stored procedures also reduce the chance to reuse the execution plan. Don’t create temp table in loop. Don’t declare cursors that refer a temp table.

Avoid using SQL Server cursors

Cursor uses a lot of resources for overhead processing to maintain current record position in a RecordSet and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop. We can replace the cursor by using table variable or temp table.

Using cursors make the program slower as it works against SET based sql. Try to use temporary table or table variable with identity column and ten iterate all the tables using while lops and a looping counter, which will map with the identity column.

Try to use UNION to implement an "OR" operation

Try to use Union instead of OR.  UNION to combine the result set of two distinguished queries. Better use UNION ALL if a distinguished result is not required. UNION ALL is faster than UNION as it does not have to sort the result set to find out the distinguished values.

Read More about Union and Union ALL

http://bageshkumarbagi-msbi.blogspot.in/2016/05/difference-between-union-and-union-all.html

Break down a very large stored procedure into several sub stored procedure

The stored procedure will be recompiled when any structural changes are made to a table or view referenced by the stored procedure (an ALTER TABLE statement, for example), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if we break down a very large stored procedure into several sub-procedures, there's a chance that only a single sub-procedure will be recompiled, while other sub-procedures will not.