Performance Improvement

T-SQL SIDE

• Include SET NOCOUNT ON inside SP
• If table’s rowcount is required, then use sysindexes query instead of COUNT(*)
• Try to avoid CURSOR wherever required
• Try to use UNION ALL instead of UNION wherever required
• Try to avoid DISTINCT wherever required
• Try to avoid unnecessary complicated joins
• Try to return only required columns instead of using SELECT *
• Try to avoid IN, instead EXISTS query can be used
• Avoid triggers
• Try to avoid function in WHERE query or SELECT query
• Try to avoid CONVERT or CAST function
• If less number of records are involved, then TEMP variable can be used instead of TEMP table, otherwise go for TEMP table
• Avoid large number of DML operations in single transaction
• Avoid update on a table which is affecting lot of records. Divide this query in chunks and update the records in chunks which will be faster
• Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often
• Try to avoid ISNULL wherever required
• Avoid sorting if not required. Can it be achieved using Index?
• Try to use in-built function instead of using system tables.

e.g. if exists (select * from sysobjects where name = 'tblTest1908')

drop table tblTest1908

if object_id('tblTest1908') is not null
drop table tblTest1908

DESIGN & MAINTENANCE SIDE

• Have a database design as per normalization rule
• Create Clustered Index and non-clustered based on the usage
• Try to rebuild the indexes monthly/weekly based on the data insertion/updation
• In worst case, denormalization
• If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
• If you experience a large number page splits, then increase the Fillfactor which will reduce page splits as it will have more room to accommodate new data.

EXECUTION PLAN

• 
  

• Execution Plan will give you an idea which execution plan SQL Server internally uses.

• Table Scan

Ø This means, no clustered index is being used to retrieve the data. It is using a heap structure and does a full table scan.

Ø If a table is having less number of records, then it is fine otherwise index needs to be created.

Ø Sometimes, though a non-clustered index is available, SQL server uses table scan. This will happen in following 2 scenarios.

§ If total number of retrieved rows are higher relative to total number of records available in a table

§ If total number of rows having almost a same value are coming from a table

It will be efficient when output number of rows are greater than 50 % of total number of rows

• Index Seek

Ø This means, non-clustered index is being used to retrieve the data. It happens generally when fewer rows are being returned. It focuses on qualifying rows and qualifying pages which contain these qualifying rows. It will be efficient when output number of rows are less than 50 % of total number of rows

• Clustered Index Seek

Ø This means, clustered index is being used to retrieve the data. This is the fastest way to get the data

• Clustered Index Scan

Ø This is just like a table scan, only difference is – it is happening on clustered index table instead of heap structure. It happens due to same reason mentioned in table scan

• Bookmark Lookup

Ø It is a mechanism to navigate from non clustered index row to actual data row in base table (clustered index)

• Hash Join/Nested Loop/Merge Join

	Hash	Nested Loop	Merge
When being used?	No adequate index on join columns	When only one table has index and other does not have	When both the tables have clustered index (sorted rows)
Phase	Build Phase Probe Phase
Further Info	Smaller table will be build input and larger will be probe input. Each row of smaller table will be linked with larger one and being stored in Hash Table	For each row of outer table, all the rows of inner table will be executed	Very effective for larger tables. Scans n1 + n2 rows unlike n1 X n2 as in Nested loop because the records are physically sorted. So rowID = 6 will scan only till RowID = 6, the moment it gets 7, it skips.
Types	In Memory Stores in Memory Grace When there is no sufficient memory then it will be stored in partitions.

USING DTA (DATABASE TUNING ADVISOR)

• 
  
  
  

  Create one *.sql file with various DML operations or SPs on which you want to perform tuning
• 
  
  Open DTA from "Tools" Menu
• 
  
  Connect to a server
• 
  
  Select Database & chose tables which are being used in DML Operations or SPs you have specified in saved file
• 
  
  Select a file which you have saved in Step # 1

• 
  
  
  

  
  

  In "Tuning option", select different options and "Start Analysis"

• 
  
  
  

  
  

  
  

  It will give result like this.