Buy @ Amazon

Pro Tips For SQL Server DB Indexes

on

 

  1. Warning: If a table does not have appropriate index, the DB uses table scan instead of index scan to retrieve rows. Table scan is badly non-performant because every row of a table is accessed iteratively for match conditions before returning the ones that matched. So, do have an eye for table scan in your query profiling.
  2. At regular intervals, do evaluate the usage of existing indexes against its usage and also look for missing indexes.
  3. DO NOT create too many indexes as it can affect the performance, for the maintenance overhead it creates.
  4. If you find a frequently used query containing one or more AND operators in its WHERE clause, do consider having a composite index that includes all those columns in the WHERE clause.
  5. If your SQL query has a JOIN condition, then it is recommended that you index each join column.
  6. DO NOT create an index on a column, if most of the rows (80% or more) are returned matching the condition set for this column value. In this specific case, a table scan would be faster, and even the query optimizer would usually choose to use a table scan, ignoring the index.
  7. Always create the clustered index before creating any non-clustered indexes. DO BE AWARE that existing non-clustered indexes on tables are rebuilt when a clustered index is created.
  8. Heaps (aka Non-Clustered Tables) can be used as staging tables for large, unordered insert operations.
  9. To access any row in a Heap, the entire table must be read (a table scan). This is acceptable only in the cases of small tables.
  10. DO NOT use a Heap when the data is frequently:
    • updated; or
    • returned in sorted order; or
    • grouped together; or
    • accessed using range query
  11. SQL Server supports the creation of a descending order index on column value(s). Know your access pattern and create index accordingly for better performance.
  12. Descending indices should be used when you create a composite index on columns that have opposite sorting directions.
  13. Use Covering Index to boost query performance. Significant performance gains can be achieved when all columns in a query are included in the index, because the query optimizer can locate all the column values within the index pages without having to access pages with table data.
  14. DO leverage the WITH DROP_EXISTING=ON option in the CREATE CLUSTERED INDEX query to enhance the query performance when re-creating a clustered index on a table that also has a non-clustered index.
  15. DO leverage the WITH SORT_IN_TEMPDB=ON option in the CREATE CLUSTERED INDEX query to reduce the time required to create an index if tempdb is on a different disk than the user database.