Improve SQL Server Performance

We are going to discuss some points that help improve database performance. Always try keep this point in mind before creating new database or writing query or procedures.
 
Schema

  • Define all primary keys and foreign key relationships.
  • Define all unique constraints and check constraints.
  • Choose the most appropriate data type.
  • Normalize first, denormalize later for performance.
  • Use indexed views for denormalization.
  • Partition tables vertically and horizontally.

 
Queries

  • Write correctly formed queries.
  • used required field in select query.
  • Return only the rows and columns needed.
  • 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.
  • Fully qualify database objects.

 
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.
  • Remove unused indexes.
  • Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
  • Create composite indexes with the most restrictive column first.
  • Use temporary tables and table variables appropriately.
  • Fully qualify database objects.

 
Transactions

  • Avoid long-running transactions.
  • Avoid transactions that require user input to commit.
  • Try to access resources in the same order.
  • Ensure that explicit transactions commit or roll back.
  • Access heavily used data at the end of the transaction.

 
Stored Procedures

  • Use Set NOCOUNT ON in stored procedures.
  • Do not use the sp_prefix for custom stored procedures.

 
Execution Plans

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

 
Execution Plan Recompiles

  • Use stored procedures or parameterized queries.
  • Use sp_executesql for dynamic code.
  • Evaluate hash joins.
  • Avoid cursors over temporary tables.
  • Compare actual versus estimated rows and executions.

These are few points that will help to improve the performance we update next level of this article soon with more details for performance.