Indexing SQL Server Relational Databases For Performance

by Toan T
In a relational database, indexing is used to retrieve data quickly from a system. With indexing, data is sorted in a way that made it relatively easy for users to search for which is very similar to an index in a book. In this article, we will be talk about some issues that are often overlooked with regards to indexes. Generally, having more indexes means that quickly you will be able to retrieve the data. However, large amount of indexes can also increase the time it will take to insert new data. In Microsoft SQL Server, there are two supported indexes: clustered and nonclustered. Clustered indexes mean that the data are already physically ordered while nonclustered indexes contain pointers in the data rows that typically contain a reference to the location in the clustered index if it exist. Indexes are often automatically created by the database to ensure that no duplicate data can be inserted. In MS SQL Server, 256 nonclustered indexes can be created per table. That might seems to be a lot but it is no advisable to do so because indexes require additional storage space in both memory and on the disk drive. They also lead to a decrease in performance when performing insert statements because the data needs to be inserted according to the indexes instead of the first available space which requires more time to complete.

To ensure the best performance, the article recommends some guidelines that would help when creating indexes.

a. Choose the right data types

b. Ensure the indexes are actually uses

c. Pay attention to the order of columns when creating multi-column indexes

d. Limit the number of columns in clustered indexes

e. Avoid clustered indexes on frequently updated columns.

f. Split operations

g. Rebuild the indexes properly

h. Use fill factorwisely.

I think this article is relevant to the class because we just went over indexing last lecture and that the powerpoint did  provide some rules on how to use indexing. However, it didn’t  really actually give any information as to why someone would want to use indexing in their database and why it is so important to actually do it right. Utilizing indexes in database to reduce uptime is one thing but implementing it without making a big hit in performance is another.

Source: http://www.techrepublic.com/article/indexing-sql-server-relational-databases-for-performance/5148062

Image: https://encrypted-tbn1.google.com/images?q=tbn:ANd9GcReEzVnd_UrJxt6vs2m5UnNVJmhpt6LhJsSnwz0mkAz8rR7XdYJ