The Importance Of Clustered Index To Improve Database Performance In SQL

Clustered Index In Each Table Will Improve Your Database Performance In SQL

When working with MS SQL every table should have a clustered index, preferably on the primary key field. The clustered index definition in SQL Server logically sorts the data adjacent to each other in that index key.

Call us for Free Consultation for Remote DBA services at our SQL Consulting Firm at: 732-536-4765

This way when searching for a number on the “Primary Key” the search will be performed at lightning speed.

When creating a clustered index on the primary key, it is best to JOIN tables to each other while using the primary key, significantly increasing performance.

You can find many articles and blogs on the internet that explain how clustered indexes are built and why we should have one on each table.

We are going to provide you with some examples displaying the importance of clustered indexes and how it affects the performance in a stored procedure or the client’s application that executes these stored procedures.

We used to have had a third party software tool which was installed on the database in our client’s production server. At first glance everything seemed to work fine. The application ran quickly but later on the end users started to complain about performance degradation.

The Reason for Slow Queries In SQL

I started looking into the matter, and finally after drilling down, I found one big table that was running a lot of queries to perform a scan on the table which had a None Clustered Index. I identified the three most frequent running queries and tried to optimize them.

It turns out that columns which were included in the “WHERE” clause did not have any indexes. For example one of the fields was a date column which was filtered based on date range.

Instead of using an effective clustered index it scanned the entire table which was very costly in terms of performance.

As you can see on graph to the right, we measured the CPU’s performance before and after creating the index, and how drastically the clustered index reduced the system resource consumption.

Check our Custom Software Development Services.

Moving Hundreds Of Millions Records While Using SQL Is Possible With Clustered Index

Another advantage of having clustered index is to move millions of records from one table to another very quickly while looping through the records in small batches against the primary key of the table. Processing hundreds of millions of records just got much easier, while using SQL server database engine.  Please check the above link for more details. You may also check this article Create Unique Invoice Number in SQLand the discussion about clustered index as well.