Data access optimization steps in SQL Server

Use Indexing

• 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

It's really tempting to create index on all eligible columns in your database tables. But, if you are working with a transactional database (An OLTP system where update operations take place most of the times), creating indexes on all eligible columns might not be desirable every time. In fact, creating heavy indexing on OLTP systems might reduce overall database performance (As most operations are update operations, updating data means updating indexes as well).

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 advanced indexing and denormalization

• 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 database performance problems

• Diagnose performance problem, use SQL Profiler and Performance Monitoring tool effectively.
  

Optimize database files and apply partitioning

• Organize the file groups and files in the database
• Apply partitioning in the big fat tables
  

Original Source: M.M.Al-Farooque Shubho