- Apply proper indexing in the table columns in the database
- Make sure that every table in your database has a primary key.
- Create non-clustered indexes on columns which are, frequently used in the search criteria, Used to join other tables, Used as foreign key fields , Of having high selectivity (Column which returns a low percentage (0-5%) of rows from a total number of rows on a particular value) or Used in the ORDER BY clause
- Create appropriate covering indexes
- Use Database Tuning Advisor’s help while creating covered index
- Defragment indexes if fragmentation occurs
A rule of thumb can be suggested as follows: If you work on a transactional database, you should not create more than 5 indexes on the tables on an average. On the other hand, if you work on a Data warehouse application, you should be able to create up to 10 indexes on the tables on an average.
Re-factor TSQLs and apply best practices
- Move TSQL codes from application into the database server
- Identify inefficient TSQLs, re-factor and apply best practices
- Don’t use “SELECT*" in SQL Query
- Avoid unnecessary columns in SELECT list and unnecessary tables in join conditions
- Do not use the COUNT() aggregate in a subquery to do an existence check
- Try to avoid joining between two types of columns
- Try to avoid deadlocks
- Write TSQLs using “Set based approach” rather than using “Procedural approach”
- Try not to use COUNT(*) to obtain the record count in the table
- Try to avoid dynamic SQLs
- Try to avoid the use of Temporary Tables, try to use Table variables.
- Instead of LIKE search, Use Full Text Search for searching textual data
- Try to use UNION to implement “OR” operation
- Use VARCHAR(MAX), VARBINARY(MAX) and NVARCHAR(MAX)
- Do not call functions repeatedly within your stored procedures, triggers, functions and batches.
- Try to avoid the use of triggers.
- Try to avoid nested transaction.
- Understanding the query execution plan
- Getting rid of the unnecessary Text column in the SELECT list
- Apply some advanced indexing techniques
- Implement computed columns and create index on these
- Create "Indexed Views"
- Create indexes on User Defined Functions (UDF), using computed columns
- Create indexes on XML columns
- Apply de-normalizations, use history tables and pre-calculated columns
- Diagnose performance problem, use SQL Profiler and Performance Monitoring tool effectively.
- Organize the file groups and files in the database
- Apply partitioning in the big fat tables
Original Source: M.M.Al-Farooque Shubho