One of the common mistakes developers often perform is to execute T-SQL statements directly from their application. Worse, the performance may get degraded if the combinations of operators, such as LIKE and NOT LIKE, are used with the statements. It is always a good practice to use stored procedures rather than stuffing queries in your application or a web page. Stored procedures help improve the performance since they are precompiled.

Use minimal string operations as are often costly, so do not use them in a JOIN condition. Using implicit or explicit functions in the where clause might impact your server. Also, using complex business logic in triggers is yet another performance issue. When you are working with transactions, always use isolation levels. Proper utilization of isolation levels will help reduce locking and also avoid dirty reads and writes.

If possible, avoid using CURSOR's. The other way around is to use temporary tables with WHILE statements and break complex queries into many temporary tables and later joining them. Also when you are working with large tables, select only those rows or columns that are needed in the result set. Unnecessary inclusion on columns and rows will congest the network traffic which is, again, a bottleneck for performance.

Index Considerations

Create indexes only when they are really required because SQL Server needs to arrange and maintain records for each index that you define. To make sure that you are creating for the right purpose, you can create indexes on columns which are used in WHERE condition and ORDER BY, GROUP BY and DISTINCT clauses. Indexes which are not used may cause extra overhead. Also, it is always recommended to have smaller cluster indexes and moreover define a data range for the cluster indexes that you maintain. Once you define a column as foreign key, it is a good practice to create an index on it. You can also use the index tuning wizard to check the index performance and be sure to remove unused indexes.