SQL Server Tips – Best Practices for Indexing

Today we will focus on the best practices for SQL Server Indexing. These practices are known to us and is a recap which will assure optimal performance through indexes.

1) Guidelines on indexing decisions

– Keep the columns in an index to a minimum as wide indexes take longer to scan.

– Ensure a clustered index on every table. The clustered index should be on an unique column and used most frequently for retrieving data

– The column of the clustered index should not be frequently updated as every time a clustered index is updated, SQL Server also  maintains the non-clustered index along with the clustered index for the non-clustered index contains a pointer to the clustered-index.

– Eliminate duplicate indexes i.e., multiple indexes on the same set of columns. This adds to extra storage space and has a performance penalty on the INSERT, UPDATE and DELETE statements.

– Creation of indexes primarily depends on frequently executed queries and the queries most important to the users. Index tuning wizard and Database Engine Tuning Advisor are built-in tools for  analysing and recommending indexes and analysing the performance of SQL Server instances but the recommendation are relevant for the workload supplied to the tool.

2) Determine the fragmentation state of indexes

Fragmentation happens due to inserts, updates and deletes. Get the list of all indexes on the database where fragmentation is 15% or greater  and index page count is greater than 50. Work out plan to rebuild the heavily  fragmented indexes and reorganize the lesser fragmented indexes.

3) Ensure the data type of surrogate primary keys is correct.

The best example here would be a transaction table which has a surrogate key of int data type. As the records cross the 2147483647 limit, the DBA would realize that the data type should not be an int. Ensure the database is scalable.

4) Understand the following myths are not true:

– Rebuilding the clustered index would rebuild all the associated non-clustered indexes. This is true only if the REBUILD WITH DROP EXISTING option is specified.

– Reorganizing a clustered index causes all non-clustered indexes to be reorganized.

– A fill factor of 0% is not the same as a fill factor of 100%.