SQL Server Performance Tuning – Proper scenarios in creating Indexes on Table Columns

By Santhosh N

This explains the various precautions needs to be taken care while indexing and choosing what columns needs to be indexed for better performance.

The first thing one need to keep in mind and know is that indexes are useful for SELECT queries and slow down DML (INSERT, UPDATE, and DELETE) queries.

Following point help you to create proper indexes for better performance of over all databases.

1) Always try to create indexes for those columns which are participating in select query in where clause.
2) Don’t create indexes on small tables.
3) Don’t create many indexes in single table and create for those which are desperately needed as the DB space is increased for maintaining the indexes and also DML.
4) Create multi column indexes for better performance instead of many single column indexes depending on the queries you mostly use in the application.
5) Always try to index the columns in ORDER BY, GROUP BY in exact order and number of columns to make use of indexes properly.

Related FAQs

This describes the usage of DBCC SHOW_STATISTICS command which is used for performance tuning in the Sql Server.
This explains the performance overhead of using NULL values in the columns of the Database table.
This explains the drawbacks of not setting SET Options properly while working with XML Data Types.
This explains about using functions with Indexed columns in Sql query
SQL Server Performance Tuning – Proper scenarios in creating Indexes on Table Columns  (1106 Views)