When it comes to optimizing the performance of your SQL Server database, one of the key factors to consider is the proper use of indexes. Indexes help improve query performance by allowing the database engine to quickly locate and retrieve the requested data. While there are different types of indexes in SQL Server, in this blog post, we will focus on non-clustered indexes.

A non-clustered index is a separate structure from the actual data in a table. It contains a copy of the indexed columns along with a pointer to the corresponding data rows. This allows for efficient searching and sorting of data based on the indexed columns. Unlike a clustered index, a table can have multiple non-clustered indexes.

To get a better understanding of the non-clustered indexes in your database, you can use the following script:

SELECT COUNT(i.TYPE) AS NoOfIndex,
       [schema_name] = s.name,
       table_name = o.name
FROM sys.indexes i
INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.TYPE = 'U' AND i.TYPE = 2
GROUP BY s.name, o.name
ORDER BY schema_name, table_name

This script will provide you with the number of non-clustered indexes on each table in your entire database. It can be a useful reference to quickly assess the indexing situation in your database.

However, it’s important to note that having excessive indexes is not recommended as it can negatively impact performance. The number of indexes alone is not an indication of bad indexes. Proper indexing requires careful consideration of various factors, such as the type of workload, the presence of clustered indexes, and the identification of missing or unused indexes.

For example, tables without a clustered index (known as heaps) are often not recommended for OLTP workloads. Unused indexes should be dropped after careful observation to avoid unnecessary overhead. Missing indexes can be identified and added after thorough testing to improve query performance.

While this blog post only scratches the surface of the topic, there are many resources available to dive deeper into SQL Server indexing. You can explore additional articles, scripts, and videos to enhance your understanding and expertise in this area.

Remember, optimizing your database’s performance is an ongoing process that requires continuous monitoring, analysis, and adjustment of indexes based on the specific needs of your application.

Happy indexing!