Contact Us

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 SQL and the discussion about clustered index as well.

Go Back to Our: Main Blog

Written by: Doron Farber - The Farber Consulting Group, Inc

There are 3 comments for "The Importance Of Clustered Index To Improve Database Performance In SQL"

avatar
By Brian McKillop | August 03,2015

This is a great example of quick, simple, high-return check every programmer should perform on their DB.

Make sure those indexes exist and your clients will be happy!

Brian

Reply
avatar
By Sotiris Filippidis | August 04,2015

ALWAYS be sure that your primary key, especially if it is referenced by other tables, is on a clustered index. This has exceptions, of course - if the usage pattern indicates that specific queries to specific columns are happening more often than primary key queries, then maybe a clustered index on this column/columns would be a better idea. You can have only one clustered index, and it defines the PHYSICAL order of data on the disk (yes, it's beneficial even on SSDs). So use wisely.

Reply
avatar
By Yaroslav Kazmin | August 17,2016

Very nice post, thanks for sharing this information

Reply

Add your comment