I’ve just finished an overview on 7 Steps to SQL Server performance optimization and If you haven’t read that yet, it’s a good place to start.

To recap, here are the 7 steps to SQL Server performance optimization:

1. Indexing <- you are in the deep dive to Indexing
2. Query Optimization
3. Database Design
4. Hardware Resources
5. Statistics and Query Plans
6. Maintenance Tasks
7. Monitoring and Tuning

This guide is the 1st in the series of deep dives into the steps. There’ll be a total of 7 deep dives, one for each of the steps.

Indexing is a crucial aspect of SQL Server performance optimization. Properly designed indexes can dramatically improve query performance, while poorly chosen indexes can lead to unnecessary overhead and slower execution times. In this article, we’ll explore effective indexing strategies, discuss how to choose what to index, and examine the tradeoffs involved.

Understanding Indexes

An index in SQL Server is a database object that provides quick access to data in a table based on the values in one or more columns. Indexes are used to speed up data retrieval operations by reducing the amount of data that needs to be scanned. They work similarly to the index in a book, allowing you to locate specific information quickly without having to read through the entire book.

Choosing What to Index

When deciding which columns to index, consider the following factors:

1. Frequently Used Columns: Focus on columns that are frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses. These columns are prime candidates for indexing.
2. Selective Columns: Choose columns with high selectivity, meaning they have a large number of distinct values relative to the total number of rows in the table. Indexing highly selective columns can significantly improve query performance. Avoid Columns like ‘Gender’ unless you call yourself ‘woke’ 😉
3. Foreign Key Columns: Index foreign key columns to speed up JOIN operations and enforce referential integrity.
4. Columns Used in Range Queries: If your queries often involve range conditions (e.g., BETWEEN, >, <), consider indexing the columns involved in these conditions. It’s a bit like sorting your data by value of that range.
5. Columns with High Cardinality: Cardinality means the uniqueness of data in a column. Columns with high cardinality (many unique values) are good candidates for indexing. As you’ve probably noticed, this point is somewhat similar to 2.

Types of Indexes

SQL Server offers several types of indexes, each with its own characteristics and use cases. I’ll explain them and give some rather weird examples to demonstrate how to create those indexes and to entertain you:

1. Clustered Indexes: A clustered index determines the physical order of data in a table. Each table can have only one clustered index. Choose a clustered index on a column that is frequently used for sorting and has a wide range of values. For example, if you add a clustered index to the table of friends by first name, that’s great for quickly retrieving all friends named ‘Jenny’.
   Example:
   
   CREATE CLUSTERED INDEX IX_friends_first_name
ON friends (first_name);
   
   Remember, only 1 clustered index per table so use it wisely.
   
2. Non-Clustered Indexes: Non-clustered indexes are separate structures from the table data and can be created on multiple columns. They are useful for improving the performance of queries that search for specific values or ranges of values.
   Let’s say you’ve got some folks in your ‘friends’ table that aren’t that close and you frequently happen to remember only their last names…
   
   CREATE NONCLUSTERED INDEX IX_friends_last_name
ON friends (last_name);
   
3. Unique Indexes: Unique indexes ensure the uniqueness of values in one or more columns. They are automatically created when you define a primary key or unique constraint on a table.
   You’ve probably also got about 80 gazillion friends and want to know which one of them called or emailed, so we’ll add unique indexes for those columns to save your servers from burning up:
   
   CREATE UNIQUE INDEX UX_friends_phone_number
ON friends (phone_number);

CREATE UNIQUE INDEX UX_friends_email
ON friends (email);
   
4. Covering Indexes: A covering index includes all the columns needed to satisfy a query, eliminating the need to access the table data. This can significantly improve query performance by reducing disk I/O.
   Now please don’t use it like that at your job but for educational purposes let’s imagine you frequently query millions of friends by firs name, last name and email – so here’s the covering index for that:
   
   CREATE NONCLUSTERED INDEX IX_friends_city_name_email
ON friends (city)
INCLUDE (first_name, last_name, email);

Indexing Tradeoffs

While indexes can greatly enhance query performance, they come with certain tradeoffs:

1. Disk Space: Indexes consume additional disk space. Each index requires storage for its structure and data.
2. Insert/Update/Delete Performance: Indexes can slow down data modification operations (INSERT, UPDATE, DELETE) because the index structures need to be updated along with the table data.
3. Maintenance Overhead: Indexes require regular maintenance, such as rebuilding or reorganizing, to ensure optimal performance. This maintenance process consumes system resources and can impact database availability.

Best Practices

To optimize your indexing strategy, consider the following best practices:

1. Analyze Query Workload: Regularly analyze your query workload to identify the most frequently executed queries and the columns they rely on. This information helps prioritize your indexing efforts.
2. Avoid Over-Indexing: Creating too many indexes can negatively impact performance. Strike a balance between query performance and the overhead introduced by indexes. Some times you have to test and see.
3. Monitor Index Usage: Use SQL Server’s built-in tools, such as Dynamic Management Views (DMVs) and Index Usage Stats, to monitor index usage and identify unused or inefficient indexes.
4. Regularly Review and Maintain Indexes: Periodically review your indexes and remove any that are no longer needed. Regularly rebuild or reorganize indexes to maintain their efficiency and prevent fragmentation.
5. Consider Covering Indexes: Implement covering indexes where appropriate to minimize the need for table lookups and improve query performance.

By understanding indexing strategies, carefully choosing what to index, and considering the tradeoffs involved, you can optimize your SQL Server performance and ensure efficient data retrieval. Remember to regularly monitor and fine-tune your indexes based on your specific workload and performance requirements.

Let’s recap: We’ve discussed what to index and how, looked at the types of indexes, added some indexing tradeoffs and concluded with a list of best practices.

If you’re new to this just dive in, make some mistakes on your companies’ dev server and improve some query times for heavily used queries by adding a genius index. If you’ve seen it all I still hope you got at least 1 bit of good info from my work here and in any case I invite you to check out the general article on 7 steps to SQL Server performance optimization as well as my 7 articles on each of those steps.

Next up in our series is my 2nd deep dive article on Query Optimization. See you there!

p.s.: If you want help squeezing out performance or identifying bottlenecks, check out our AI tool or get in touch about consulting. We’re always here to help and take responsibility.