SQL Server Performance tuning - Part 1

Working on Performance Tuning is very tough task specially on large scale data hence the minor changes will give dramatic (+ Ve or -Ve) impact on performance.

Before going to tune the query as an experience developer you should aware on

If your application stops working suddenly, it may not be a database issue. For example, maybe you have a network problem. Investigate a bit before you approach the DBA!

The below are the few generic points for tuning.

Query Level Tuning

1. Return only the rows and columns needed.

2. Avoid expensive operators such as NOT LIKE in filtered clauses.

3.  Avoid explicit or implicit functions in WHERE clauses.

4.  Use locking and isolation level hints to minimize locking.

5. Use stored procedures or parameterized queries.

6. Minimize cursor use.

7. Use temporary tables and table variables appropriately i.e keep heavy transactions table and function results in the  temp table table with required columns and then create the index on this temp table.

8. Limit query and index hint use.

9. Fully qualify database objects i.e keep <Database Name>.<Schema Name>.<Table Name>.