SQL Server Performance Improvement

There are lots of people asking about how I can improve performance of store procedure. In this blog I am giving a brief about how to optimize SP. So whenever someone ask to check why this procedure is running slow or how we can optimize or improve its performance, I will do following check/activities step by step:-

1. Database server for resource availability:-Check database server for resource availability for procedure execution, making user about server has sufficient resource to run query.

2. Coding standard in  store Procedure:-Make sure that listed below coding standard followed while writing store procedures:-

• Include SET NOCOUNT ON statement: - In sql server with any select/insert/update query server will return number of rows affected, In store procedure this details are not required unless you are debugging it. SO just turn it off.
• Use schema name with object name: - This will help in finding the complied plan directly instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases in performance.So always refer the objects with qualified name in the stored procedure.
• Do not use the prefix “sp_” in the stored procedure name: - If a procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database will causes extra overhead, even a wrong result if another stored procedure with the same name is found in master database.
• Use IF EXISTS (SELECT 1) instead of (SELECT *):- We are not going to the output of the internal statement. Hence, to minimize the data for processing and network transferring we should use select 1.
• Try to avoid using SQL Server cursors: - Cursor uses a lot of resources for processing to maintain current record position in a recordset and this decreases the performance. 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. While loop is one of set base approach and can replace cursor.
• Keep the Transaction short:- The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. Lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
• Use TRY-Catch for error handling

3. Optimize a Stored Procedure using the Execution Plan: - We can optimize SP by using execution plan. For this First, enable the execution plan, Go to the SSMS menu, Query>> Include Actual Execution Plan. Now you have enabled plan for current session. Now when you execute your stored procedure, there will be one new tab “Execution Plan” in output window. There you can see execution  plan for store procedure
(For in detail you can refer this link:- http://sqlserverplanet.com/optimization/how-to-optimize-a-stored-procedure-using-the-execution-plan )

In Execution plan you will get Query Cost Relative to Batch, this cost is calculated by SQL Server by probably combining resource usage (CPU, IO, Time etc.)
Now you have execution plan, you need to follow below steps:-

• STEP 1: Find the Most Costly Statements:- Bye using query analyzer you can find batch/statement which is taking more time to execute. This is done by looking at the cost for each operation in the execution plan. Under each operation, you will see a “cost: x%”. You want to find the operation with the highest percentage and focus on that.
• STEP 2: Determine why the statement is costly:- Now that you have  found the statement, you need to find out what is causing the statement to be slow. Many time it is one of the following time which is having more cost.

• Symptom	Cause(s)	Detail
  Table scan, index scan, clustered index scan	Missing or improper Indexes, cardinality < 5%	Scan operations are not bad when the cardinality of the column being searched is less than approx 6%, or when the amount of records being scanned is below around 500 records. Otherwise, if you have a scan that has a high cost associated with it, you will want to find the appropriate indexes to fix this issue.
  Thick lines (arrows) from one operation to another	Bad Joins, missing filter operation	This happens lot of times because of bad order of operations.  What that means is that the optimizer did not choose the limit the overall result set by joining the smallest tables first.  Instead, it is trying to join and filter from the largest tables, and then join the smaller tables.  While this may not be the only scenario this happens. To fix this, you may consider breaking the query up and dump results into a temp table prior joining on the rest of the tables.  Make sure to index your temp table!
  RID Lookups, Key Lookups	Data is not at the leaf level, can use include clause for indexes	These are also not always bad.  In fact you cannot get around this issue all the time.  Why?  Because you can only have one clustered index, and you don’t want to include every column in the table in all your non clustered indexes.  However if you run into this issue having a high cost, you will want to consider changing your clustered index, or adding the columns being looked up in the Key Lookup using the INCLUDE statement for indexes.
  Paralellism	Bad join, improper order of operations, maxdop or parallel threshold set too low on server	Parallelism can have a significant impact on queries and your server.  What this means is that the amount of data being usurped by your query is very large, so in order to speed it up, SQL Server thinks it would be best to create more SPIDs to handle the operation.  When this happens look for your page life expectency to dip low and your disk utilization to go high.   Consider employing the same technique as above of breaking up your query.  Also look at the order of operations and change the query around.  If that doesn’t help, add the query hint OPTION (MAXDOP 1).
  Compute Scalar	Joining on differing data types, implicit conversion required	This guy may fool you when it comes to the cost factor, however it can be a big hindrance to performance when this operation is between table joins. The “scalar” part should worry you, because it does perform somewhat of a row-by-row operation. When this is between table joins, this operation is there to do an implicit conversion of a datatype in order to join the columns correctly. Since it cannot join the two tables directly it will often force too many records to be touched in order to be converted. The conversion may then be dumped into a worktable (in the background) then that worktable will be used for joins after the conversion. Just make sure to join like data types.

  
  
• STEP 3: Optimize:- Now optimize and apply the changes discovered above!

NOTE:- 

To clear the compiled execution plan, use:
DBCC FREEPROCCACHE

To clear the data pages, use:
DBCC DROPCLEANBUFFERS

DBCC: - The Database Console Commands (DBCC) are a series of statements in Transact-SQL programming language to check the physical and logical consistency of a Microsoft SQL Server database.