SQL Server Query Performance Tuning

Here are some tips for tuning SQL Server queries

Basics Tips

1. Avoid use of SELECT *
2. Avoid too many joins
3. Avoid using cursors
4. Always restrict rows and columns of result set.
5. Verify if server is having enough free space. Ideally 30% of space should be free.
6. SQL Server is case insensitive, so avoid use of functions like UPPER, LOWER
7. Decreasing performance order of operators is >, >=, <, <=, Like,  <> (Slowest)
8. Use Exists, Not Exists instead of IN, NOT IN
9. When using like operator try to leave wild character on right side.
10. Avoid use of function in WHERE Clause.
11. Use LIKE instead of SUBSTRING in WHERE
12. Use OR ColunmName IS NULL instead of COALESCE/IsNULL functions.
13. It is better to have multiple queries with UNION ALL instead of one query with may ORs.
14. Create functions to reuse code but don't exaggerate use of functions.
15. Use truncate to delete all data instead of delete.
16. Define covering index.
17. Make sure that each table is having primary key.
18. Avoid use of triggers.
19. Avoid use of sub-queries.
20. Avoid use of DISTINCT clause
21. Use Union ALL instead of Union
22. Create joins with ON rather than WHERE
23. Use WHERE instead of Having to filter data to the extent possible.

Advance Tips

1. Have maintenance plan to automate Index rebuild and update stats.
2. Use WITH (NOLOCK) on SELECT queries running against data warehouse or any system where you know that data is updated only during non production hours.
3. Use batch commit, in case you are dealing with large volume of data.
4. Save data to temporary table and join in case same set of joins are being repeated inside a stored procedure.
5. Define aggregate tables in order to avoid long running aggregation queries, if possible.

Ways to Identify Non Performing Query

Use Activity Monitor to Identify long running queries

Right click on database instance and select Activity Monitor.

Query to find expensive queries

SELECT TOP 10 Substring(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset
                                                                              WHEN -1 THEN Datalength(qt.TEXT)
                                                                              ELSE qs.statement_end_offset
                                                                            END - qs.statement_start_offset ) / 2 ) + 1),
              qs.execution_count,
              qs.total_logical_reads,
              qs.last_logical_reads,
              qs.total_logical_writes,
              qs.last_logical_writes,
              qs.total_worker_time,
              qs.last_worker_time,
              qs.total_elapsed_time / 1000000 total_elapsed_time_in_S,
              qs.last_elapsed_time / 1000000  last_elapsed_time_in_S,
              qs.last_execution_time,
              qp.query_plan
FROM   sys.dm_exec_query_stats qs
       CROSS APPLY sys.Dm_exec_sql_text(qs.sql_handle) qt
       CROSS APPLY sys.Dm_exec_query_plan(qs.plan_handle) qp
ORDER  BY qs.total_logical_reads DESC -- logical reads

-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time