Log Shipping – NORECOVERY vs. STANDBY mode

• Create index on columns that are frequently used for data retrieval or in filter conditions. For example in SELECT statement and WHERE clause or in ORDER BY, GROUP BY, and DISTINCT clauses. As in our first example, if most of the queries are referencing emp_name column instead of emp_id column, then it is wise to create index on emp_name instead of emp_id i.e. create indexes based on use.
  
• Every table should have a clustered index otherwise it will act as heap table only even if it has non-clustered indexes, resulting performance degradation.
  
• Create indexes on selective columns only that are frequently used.
  
• Try to keep the keys of clustered index as small as possible in size. (i.e. int, smallint)
  
• Optimize cluster index for ranged data i.e. data that are retrieved using BETWEEN, >, >=, <, and <=.
  
• Creative covering indexes are a wise decision for most frequently used queries.
  
• In case of composite indexes, order the columns according to the restrictiveness i.e. smaller quantity first as described in the composite index example above.
  
• Create index on all foreign keys which may be helpful in joining operations.
  
• Use multiple narrow indexes (i.e. on smaller number of columns, may be single column index) rather than a few wide indexes (composite indexes).
  
• Avoid using indexes that are rarely used or else drop them.
  
• Identify poorly performing queries with the help of SQL Profiler, Query Execution plan, and try to optimize it.
  
• Last but not the least; remove indexes while performing BULK insert or heavy insert operations especially in case of Clustered Index as it will perform additional operations for rearranging the indexes for each insertion of the record. Instead, drop indexes before BULK insert and recreate it after insertion operation finishes.