SQL Server Index Overview
When talking about SQL Server performance tuning and queries enhancement, the first thing to consider is the SQL Server Index. It serves to accelerate reading data from underlying tables by providing quick access to the requested rows. Thus, it won’t need to scan all the table’s records.
The SQL Server index provides those fast search capabilities due to the B-Tree structure of the index. This structure makes it possible to move through the table rows based on the index key quickly and retrieve the requested records at once. It won’t need to read the whole table.
SQL Server indexes’ types
Among the main types, we pay attention to the clustered and non-clustered indexes.
The Clustered index sorts the actual data in the data pages according to the clustered index key values. It stores the data at the ‘leaf’ level of the index, ensuring the possibility to create only one clustered index on each table. The clustered index is created automatically when a Primary Key constraint appears on the heap table.
The Non-clustered index contains the index key value and a pointer to the rest of the row columns in the main table. It is the ‘leaf’ level of the index, with the ability to create up to 999 non-clustered indexes on each table.
If your table has no clustered index created on it, the table is called the ‘Heap table.’ Such a table has no criteria to specify the data order inside the pages and the pages’ sorting and linking together.
When a clustered index is created on that table, we call the sorted table a clustered table.
There are other types of indexes provided by SQL Server as well:
- The Unique index enforces the column values uniqueness;
- The covering index contains all columns requested by the query;
- The composite index contains multiple columns in the index key;
- Other particular index types are XML, Spatial, and Columnstore indexes.
The advantage of the SQL Server index is that it enhances the queries’ performance. However, it works if only the index is correct. Should you misdesign it, it will negatively affect the queries’ performance and consume the SQL Server resources to store and maintain useless indexes.
Choosing the best index that fixes all problems is not an easy task. Adding a new index may speed up the data retrieval process, but it slows down the data modification processes. Any change performed to the underlying table getы reflected directly to all related indexes to keep the data consistent.
This is why you should study and test the new index’s impact before creating it in the production environment. It is also necessary to monitor its impact and usage after deploying it to the production environment.
Factors to consider when designing a new SQL Server index
The first factor is the database workload type. Suppose we deal with an OLTP workload with a large number of write operations. It requires the least possible number of indexes. Another case is an OLAP workload with many read operations – it will demand as many indexes as possible to speed up the data retrieval.
Also, you need to look at the table size. SQL Server Engine prefers to scan the underlying table directly, instead of wasting time and resources on choosing the best index for the data retrieval from that small table.
Once you’ve decided to create an index on that table, you need to identify the index type to serve your query. There, you specify the columns added to the index key. The bases are the column data type and position in the query predicates and join conditions.
These factors should guarantee the best data retrieval performance, in the correct order, and keep the index as short and simple as possible.
Another factor to consider when designing a new index is the index storage. It’s recommended to create non-clustered indexes on a separate filegroup and disk drive. This way, you isolate the I/O operations performed in the index data pages from the database data files.
Consider setting the index FILLFACTOR, which determines the percentage of space on each leaf-level page filled with data (the value differs from the default 0 or 100 percent). The goal is to leave space in each index data page for the newly inserted or updated records. It also minimizes the pages split occurrence that might lead to the index fragmentation problem.
Index Management
The database administrator’s role in enhancing the queries’ performance with SQL Server indexes is not limited to creating the index. You should proactively monitor the index usage to identify its quality. Besides, we need to maintain the index regularly to fix the fragmentation issues.
SQL Server Management Studio, with its robust reporting functionality, provides the most useful stats data to the database administrators. One of these built-in reports is Index Usage Statistics:
The Index Usage Statistics report describes how database indexes are used in the form of:
- Seeks: the number of times the index is used by SQL Engine to find a specific row.
- Scans: the number of times the index leaf pages are scanned by the SQL Engine.
- Lookups: the number of times that a Non-clustered index was used as Clustered index to retrieve the rest of the columns not listed in the non-clustered index.
- Updates: the number of times the index data is modified.
Note that the primary purpose of the index creation is to perform an index seek operation, as shown below:
The previous report helps significantly in specifying if the SQL Server takes advantage of these indexes to accelerate the data retrieval process or not. If it turns out that any particular index does not perform as it should, drop it, and replace it with a better one.
The second report provided by the SSMS is the Index Physical Statistics. It returns the statistical information about the index fragmentation percentage per each index partition, with the number of pages on each index partition.
It also recommends how to fix the index fragmentation issues by rebuilding or reorganizing that index, according to the fragmentation percentage, as shown below:
To apply the recommendations provided by the report, you can run the index defragmentation command for each index. Or, you can create a maintenance plan using SSMS to maintain the index in the best way.
dbForge Index Manager
dbForge Index Manager is an SSMS add-in, serving to detect and fix the SQL Server indexes fragmentation issues.
It is also a centralized tool, providing the ability to detect the index fragmentation percentage across the databases. You can fix these issues by performing an index rebuild. Another way is to reorganize operations, basing on the fragmentation severity of that index. Among other options, there is the T-SQL scripts generation for index-related commands execution, exporting the index analysis results for later reference, and using the command-line interface to automate the index maintenance tasks.
dbForge Index Manager is available on the Devart download page. You can install it to your machine using a straight-forward installation wizard. After the successful installation, this add-in is ready for usage.
To use it within the SSMS, right-click on the database and choose Manage Index Fragmentation from the Index Manager list:
From the Index Manager window, you can filter the database name that interests you.
Click on Reanalyze to perform the index fragmentation check for the selected database. It automatically shows the index fragmentation statistics for all indexes created under the selected database during this process.
The Index Manager tool also recommends the actions to fix the index fragmentation issues, based on the fragmentation percentage:
Checking indexes under the Actions Required section in the previous window makes it possible to export the actions list as a CSV report. It allows you to perform the suggested fix by reorganizing or rebuilding problematic indexes directly from that page or generate a script to do it later:
The index fragmentation fix in our scenario will be as below:
If you run the previous script or click on the Fix option, and then Reanalyze the result, you see that the fragmentation issue is fixed directly:
This way, we take advantage of the dbForge Index Manager to analyze and identify the index fragmentation issues and then report or fix them directly from the same place.
Useful tool
dbForge Index Manager brings smart index fixing and index fragmentation right into SSMS. The tool allows you to quickly collect index fragmentation statistics and detect databases that require maintenance. You can instantly rebuild and reorganize SQL Server indexes in visual mode or generate SQL scripts for future use. dbForge Index Manager for SQL Server will significantly boost your performance without much effort.
