Do you frequently upgrade your on-premises hardware in order to keep a certain level of performance?

Are your Azure SQL bills bigger than you expected?

Are your databases wasting money through inefficiency?

You can keep expenses under control by improving the efficiency of your databases. You can improve your databases using SQLFacts, a FREE suite of tools for SQL Server database engineers. Please refer to my previous article for a general description of the SQLFacts toolkit. By the way, we added a new tool to the suite since that article.

SQLFacts provides many handy tools for performance tuning. The tools allow you to quickly find ways to increase efficiency in your databases. Inefficiency will contribute, directly or indirectly, to higher expenses. Inefficiency leads to bigger/better on-premises hardware, bigger Azure SQL bills, or unhappy users. If you move an inefficient database to the cloud it does not magically become more efficient. The inefficiencies show up in the size of your cloud service bills instead.

This article will focus on the index-related performance tuning tools in SQLFacts. Be sure to see the README file in the SQLFacts toolkit download for more information about all the tools.

Janitor

The Janitor tool returns information about read/write activity for tables/indexes. The information includes name, type, storage size, number of days since the last occurrence of various operations (seeks/scans/lookups/updates), and a generated DROP statement. The tool makes it easy to remove debris from the database. The debris consists of abandoned tables and unused indexes. The Janitor tool has two variants, one for tables and one for indexes.

Janitor_Tables

The Janitor_Tables variant identifies tables that are not accessed for any read/write operations. You can specify a minimum number of days without read/write activity for table DROP statement eligibility. This variant returns three result sets:

• Result set 1 is for tables with 0 (zero) seeks/scans/lookups within the minimum number of days AND 0 (zero) updates within the minimum number of days. It includes an enabled DROP statement because tables without any read/write activity have probably been abandoned.
• Result set 2 is for tables with 0 (zero) seeks/scans/lookups within the minimum number of days AND > 0 (zero) updates within the minimum number of days. It includes a disabled DROP statement because a logging table might legitimately show this kind of read/write activity.
• Result set 3 is for tables with > 0 (zero) seeks/scans/lookups within the minimum number of days. It does not include a generated DROP statement. It's just for information.

Janitor_Indexes

The Janitor_Indexes variant identifies indexes that are not accessed for any read operations while accessed for some write operations. You can specify a minimum number of days without read/write activity for index DROP statement eligibility. This variant returns three result sets:

• Result set 1 is for indexes with 0 (zero) seeks/scans/lookups within the minimum number of days AND 0 (zero) updates within the minimum number of days. It includes a disabled DROP statement because such indexes would probably be dropped along with the associated table.
• Result set 2 is for indexes with 0 (zero) seeks/scans/lookups within the minimum number of days AND > 0 (zero) updates within the minimum number of days. It includes an enabled DROP statement because such indexes are being maintained without serving any beneficial purpose.
• Result set 3 is for indexes with > 0 (zero) seeks/scans/lookups within the minimum number of days. It does not include a generated DROP statement. It's just for information.

IndexHistory

The IndexHistory tool returns a list of all existing rowstore indexes. The information for each index includes name, type, row count, storage size, column definitions, redundancy indicator, and any usage statistics that have accumulated since the last restart of the SQL Server instance. This information is very useful for identifying opportunities to consolidate, or simply remove, redundant indexes.

The presence of redundant indexes is harmful to efficiency because all indexes must be maintained with any INSERT or DELETE operations on the associated tables. It's pointless to maintain indexes that are not providing any benefit. However, a redundant index will not necessarily show up as unused. It might be used even though another index could be used instead. Further, a redundant index might be used because it's narrow(er), but that advantage may not justify the cost of its maintenance when a slightly wider index could be used instead.

You can improve efficiency by consolidating, or simply removing, redundant indexes. However, you must carefully evaluate and test such changes. In some cases the redundancy is very obvious, but in other cases it can be more complicated. You must pay attention to the nature of the index, the columns involved in the key, the included columns (if any), the filter (if any), the size, and the amount of usage. The IndexHistory tool provides all the information you need.

Example:

Assume you have two simple nonclustered indexes with the same key, neither one filtered, and each with a different included column. You can consolidate the two indexes into one index that has both included columns. In other words, DROP one of the two indexes and extend the other one to cover both needs.

IndexActivity

The IndexActivity tool returns a list of all existing rowstore indexes. The information for each index includes name, type, row count, storage size, and many low-level performance statistics that have accumulated with recent activity, often since the last restart of the SQL Server instance. This information is very useful for analyzing index access patterns and identifying areas of resource contention.

The IndexActivity tool provides information about INSERTs, UPDATEs, DELETEs, page splits, page IO waits, lock waits, hot spots, and row forwards. These details allow you to improve efficiency by tackling resource contention (such as waits and hot spots) and costly usage patterns (such as page splits and row forwards).

IndexNeeds

The IndexNeeds tool returns a list of "missing" indexes that SQL Server thinks would be useful. These indexes are only suggestions and each one ignores the others. They all must be carefully evaluated as part of the overall indexing strategy. SQL Server does not provide information about which stored procedures contributed to the index suggestions for versions prior to SQL Server 2019.

The IndexNeeds tool returns three result sets:

• Result set 1 is ordered by index benefit, first for database/schema/object and then for individual index.
• Result set 2 is ordered by name, first for database/schema/object and then for index key columns.
• Result set 3, disabled by default, is information about which stored procedures contributed to the index suggestions (for SQL Server 2019 and newer).

The IndexNeeds tool generates CREATE INDEX statements, but they are seldom used exactly as provided. It's common to consolidate two (or more) generated statements. It's also very common to consolidate a generated statement with an existing index, which is just extending an existing index to cover the new need. Good indexes are critical for the efficiency of your databases.

IndexNeedsPlus

The IndexNeedsPlus tool returns a list of "missing" indexes that appear in execution plans. In many cases (depending upon several factors), the information is very similar to the IndexNeeds tool. This routine provides an easy method to determine which SQL statements or stored procedures contributed to the index suggestions for versions prior to SQL Server 2019.

The IndexNeedsPlus tool returns two result sets:

• Result set 1 is for individual SQL statements.
• Result set 2 is for stored procedures.

The IndexNeedsPlus tool is often used along with the IndexNeeds tool. It can provide information for testing any proposed index changes.

SQLFacts

The SQLFacts tool is for conducting research on a SQL Server database. The tool returns a large number of result sets, with three of them being very relevant for this purpose:

• Fact 37 - Foreign Key Indexes
• Fact 38 - Index Redundancy
• Fact 39 - Questionable Indexes

Fact 37 (Foreign Key Indexes) is a list of existing foreign keys and their supporting indexes. Any row in the list with NULL values indicates a foreign key with no supporting index. Almost every foreign key should be supported by an index. The lack of a supporting index can cause inefficiency in ways that are not obvious. For example, if a large child table has no supporting index on a foreign key then deleting a stray row in the parent table can be very inefficient when SQL Server has to check to make sure there are no corresponding rows in the child table.

Fact 38 (Index Redundancy) is a list of pairs of indexes where one index key is a subset of the other index key. The information here is very similar to parts of the information provided by the IndexHistory tool.

Fact 39 (Questionable Indexes) is a list of indexes with dubious conditions. It points out wide columns in index keys and very low fill factors. The list flags disabled indexes. It also flags indexes that have three (or more) columns in a clustering key.

Summary

The efficiency of your databases impacts the cost of running SQL Server. It's true whether the cost is buying on-premises hardware or paying Azure SQL bills. You can find inefficiencies in your databases using the free SQLFacts toolkit. Check it out, and the bean counters will be very happy!