Optimization Tips

Database Scheme Optimization Tips

• Devote the appropriate resources to schema design.
• Separate online analytical processing (OLAP) and online transaction processing (OLTP) workloads.
• Normalize first, denormalize later for performance.
• Define all primary keys and foreign key relationships.
• Define all unique constraints and check constraints.
• Choose the most appropriate data type.
• Use indexed views for denormalization.
• Partition tables vertically and horizontally.

Database Queries Optimization Tips

• Know the performance and scalability characteristics of queries.
• Write correctly formed queries.
• Return only the rows and columns needed.
• Avoid expensive operators such as NOT LIKE.
• Avoid explicit or implicit functions in WHERE clauses.
• Use locking and isolation level hints to minimize locking.
• Use stored procedures or parameterized queries.
• Minimize cursor use.
• Avoid long actions in triggers.
• Use temporary tables and table variables appropriately.
• Limit query and index hint use.
• Fully qualify database objects.

Tips to Optimize Indexes

• Create indexes based on use.
• Keep clustered index keys as small as possible.
• Consider range data for clustered indexes.
• Create an index on all foreign keys.
• Create highly selective indexes.
• Create a covering index for often-used, high-impact queries.
• Use multiple narrow indexes rather than a few wide indexes.
• Create composite indexes with the most restrictive column first.
• Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
• Remove unused indexes.
• Use the Index Tuning Wizard.

Tips to Optimize Transactions

Avoid long-running transactions.

Avoid transactions that require user input to commit.

Access heavily used data at the end of the transaction.

Try to access resources in the same order.

Use isolation level hints to minimize locking.

Ensure that explicit transactions commit or roll back.

 

Tips to Optimise Execution Plans

• Evaluate the query execution plan.
• Avoid table and index scans.
• Evaluate hash joins.
• Evaluate bookmarks.
• Evaluate sorts and filters.
• Compare actual versus estimated rows and executions.

Tips to Minimise Execution Plan Recompiles

Use stored procedures or parameterized queries.

Use sp_executesql for dynamic code.

Avoid interleaving data definition language (DDL) and data manipulation language (DML) in stored procedures, including the tempdb database DDL.

Avoid cursors over temporary tables.

Tips on Tuning Performance

• Use SQL Profiler to identify long-running queries.
• Take note of small queries called often.
• Use sp_lock and sp_who2 to evaluate locking and blocking.
• Evaluate waittype and waittime in master..sysprocesses.
• Use DBCC OPENTRAN to locate long-running transactions.