SQL Server Mentalist

Hi all,

This is guest post by Mohammad Shahed

While working lot on optimisation in association with Vishal and Sunil we really got good hands on and perfect answers in the world of indexes

In this article I am going to cover Best Practices for indexes:

Use Clustered Indexes on Primary Keys by Default

• Balance Index Count
• Fill Factor
• Indexing Foreign Key Columns
• Index to Your Environment

Index Best Practices :

Similar to myths are the indexing best practices. A best practice should be considered the default recommendations that can be applied when there isn’t enough information available to validate proceeding in another direction. Best practices are not the only option and are just a place to start from when working with any technology.

When using a best practice provided from someone else, it is important to check them out for yourself first. Always take them “with a grain of salt.” You can trust that best practices will steer you in the correct direction, but you need to verify that it is appropriate to follow the practice. Given the preceding precautions, there are a number of best practices that can be considered when working with indexes. This section will review these best practices and discuss what they are and what they mean.

1. Use Clustered Indexes on Primary Keys by Default

The first best practice is to use clustered indexes on primary keys by default. This best practice recommends using clustered indexes on primary keys as a starting point. By clustering the primary key of a table by default, there is an increased likelihood that the indexing choice will be appropriate for the table. Many primary keys, possibly most, are built on a column that utilizes the identity property that increment as each new record is added to the table. Choosing a clustered index for the primary key will provide the most efficient method to access the data.

2. Balance Index Count

Indexes are extremely useful for improving the performance when accessing information in a record. Unfortunately, indexes are not without costs. The costs to having indexes go beyond just space within your database. When you build an index you need to consider some of the following:

· How frequently will records be inserted or deleted?

· How frequently will the key columns be updated?

· How often will the index be used?

· What processes does the index support?

· How many other indexes are on the table?

The trouble with balancing the index count on a table is that there is no precise number that can be recommended. Deciding on the number of indexes that it makes sense to have on an index is a per table decision. You don’t want too few, which may result in excessive scans of the clustered index or heap to return results. Also, the table shouldn’t have too many indexes, where more time is being spent keeping the index current than returning results. As a rule of thumb, if a table has more than ten indexes on it in a transactional system, it will be increasingly likely that there are too many indexes on the table.

3. Fill Factor

Fill factor controls the amount of free space left on the data pages of an index after an index is built or defragmented. This free space is made available to allow for records on the page to expand with the risk that the change in record size may result in a page split. This is an extremely useful property of indexes to use for index maintenance. Modifying the fill factor can mitigate the risk of fragmentation. For the purposes of best practices, we are concerned with the ability to set the fill factor at the database and index levels.

a) Database Level Fill Factor

One of the properties of SQL Server is the option to set a default fill factor for indexes. This setting is a SQL Server–wide setting and can be altered in the properties of SQL Server on the Database Properties page. By default, this value is set to zero, which equates to 100. Do not modify the default fill factor to anything other than 100. Doing so will change the fill factor for every index in the database to the new value; which will add the specified amount of free space to all indexes the next time indexes are created, rebuilt, or reorganized. On the surface this may seem like a good idea, but this will blindly increase the size of all indexes by the specified amount. The increased size of the indexes will require more I/O to perform the same work as before the change. For many indexes, making this change would result in a needless waste of resources.

b) Index Level Fill Factor

At the index level, you should modify the fill factor for indexes that are frequently becoming heavily fragmented. Decreasing the fill factor will increase the amount of free space in the index and provide additional space to compensate for the changes in record length that is leading to fragmentation. Managing fill factor at the index

Level is appropriate since it provides the ability to tune the index precisely to the needs of the database.

4. Indexing Foreign Key Columns

When a foreign key is created on a table, the foreign key column in the table should be indexed. This is necessary to assist the foreign key in determining which records in the parent table are constrained to each record in the referenced table. This is important when changes are being made against the referenced table. The changes in the referenced table may need to check all of the rows that match the record in the parent table. If an index does not exist, then a scan of the column will occur. On a large parent table, this could result in a significant amount of I/O and potentially some concurrency issues.

An example of this issue would be a state and address table. There would likely be thousands or millions of records in the address table and maybe a hundred records in the state table. The address table would include a column that is referenced by the state table. Consider if one of the records in the state table needed to be deleted. If there wasn’t an index on the foreign key column in the address table, then how would the address table identify the rows that would be affected by deleting the state record?

Without an index, SQL Server would have to check every record in the address table. If the column is indexed, SQL Server would be able to perform a range scan across the records that match to the value being deleted from the state table. Every record in the address table. If the column is indexed, SQL Server would be able to perform a range scan across the records that match to the value being deleted from the state table. By indexing your foreign key columns, performance issues, such as the one described in this section, can be avoided. The best practice with foreign keys is to index their columns.

5. Index to Your Environment

The indexing that exists today will likely not be the indexing that will be needed in databases in the future. For this reason, the last best practice is to continuously review, analyse, and implement changes to the indexes in your environment. Realize that regardless of how similar two databases are, if the data in the databases is not the same, then the indexing for the two databases may also be different.

By: Mohammad Shahed

MSBI Developer

BE(Computers)

Working in TMG

Those who have not ye subscribe my Blog yet they can subscribe it !So that I can post you @ real time and all sort of knowledge in your mail without Zero spamming !!

Happy Learning and Sharing !!

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

Link Resource Blog >> Daily Interesting links

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS |