Performance improvement in SQL Server

Performance improvement 

• Use shortest data type possible
• Make sure the data types of the columns participating in a JOIN are of same type
• Use Calculated columns instead of on the fly complex calculations 
• Consider using RCSI (read committed snapshot isolation) to preclude readers from blocking writers (writers will still block other writes but this is much graceful than using NOLOCK hints)
• Partition Alignment (64K or 1024k) and block size (or cluster size) of 64KB
• Keep the transactions as short as possible to preclude blocking/deadlocking/TLog  bloating issues
• If they need to trace, use server side trace
• If it is Ad hoc workload, consider turning on the "Optimize the Adhoc Workload" option
• Teach them how to figure out missing indexes from DMVs but add them only necessary - tweak the existing indexes first (make them composite and include columns)
• Minimize queries that use text searches, rather use full-text indexes on those columns
• Familiarize themselves with In-Memory OLTP feature
• Avoid float data types if you need accuracy
• Avoid nested views
• Familiarize with different granular levels of compilation
• Pitfalls of using HEAPS
• Revisiting the index configuration every few months
• Familiarize themselves with Blocked Process Report
• Using SET NOCOUNT ON, avoiding sp prefix, etc
• Parameter Sniffing 
• Using triggers sparingly
• Use of sp_executesql instead of EXEC
• Pros and cons of using natural keys vs surrogate keys
• Data growth and its adverse effects on query time and ways to mitigate them
• Avoiding all hints unless absolutely necessary
• No user prompts within explicit transactions
• Peer review and unit test the code before submission
• Caching rarely changing lookup table data on the client side to mitigate network chatter

Refer from