Published on

March 15, 2013

Optimizing SQL Server Indexes: Creating Effective Indexes

Welcome to the sixth part of our series on optimizing SQL Server indexes. If you haven’t read the previous parts, I highly recommend doing so to fully understand the context of this article. In this installment, we will discuss how to create effective indexes that can replace redundant indexes.

In our previous conversations, we explored the concept of redundant indexes and how they can negatively impact performance. We learned that indexes with the same key column but different included columns are not considered redundant. Today, we will explore the possibility of creating a single index that can be as effective as multiple indexes.

Creating a single index that can replace multiple indexes is indeed possible. Instead of creating separate indexes with different included columns, you can create a single index with all the necessary columns included. Let’s take a look at an example:

CREATE NONCLUSTERED INDEX [IX_SampleTable1_Col1_Col2_Col3] ON SampleTable1 (Col1) INCLUDE (Col2, Col3)

In this example, we have created a single index that includes the key column Col1 and the included columns Col2 and Col3. The order of the included columns does not matter in the included clause.

To demonstrate the effectiveness of this single index, let’s compare it to two separate indexes with different included columns. We will run two queries on the SampleTable1:

SELECT Col1, Col2 FROM SampleTable1 WHERE Col1 > 10 AND Col1 < 20 ORDER BY Col1 DESC, Col2 DESC

SELECT Col1, Col3 FROM SampleTable1 WHERE Col1 > 10 AND Col1 < 20 ORDER BY Col1 ASC, Col3 DESC

When we examine the execution plan for these queries, we can see that each query is using a different index. The first query is using the index IX_SampleTable1_Col1_Col2, while the second query is using the index IX_SampleTable1_Col1_Col3. However, when we create the new index IX_SampleTable1_Col1_Col2_Col3, both queries will use this single index.

By creating this new index, the previously created indexes IX_SampleTable1_Col1_Col2 and IX_SampleTable1_Col1_Col3 become redundant and can be dropped.

Optimizing indexes can be a complex task, but tools like embarcadero DB Optimizer can greatly simplify the process. DB Optimizer provides precise details about index usage and helps identify whether indexes are useful or useless. It also assists with query optimization, index maintenance, and overall server health optimization.

As you continue to learn about indexes, it’s natural to feel like there is always more to discover. Relying on tools like DB Optimizer can help you navigate the complexities of index optimization and ensure optimal performance for your SQL Server.

Thank you for reading this article. Stay tuned for more insights on optimizing SQL Server indexes!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.