SQL Server Performance - consideration on unique index or filtered index.
It is no doubt that when a database consists 'well-designed' indexes can reduce disk I/O operations and consume fewer system resources therefore improving query performance.
You might be wondering I'm going back to basics for indexes!
So when the database consists filtered indexes, they are very handy to optimize the performance.
So where these filtered indexes stored and why they are helpful?
In the execution of variety of queries that consists SELECT, UPDATE or DELETE statements, SQL optimizer will look for indexes to evaluate each available method for retrieving the data and selects the most efficient method. During this process a table scan or index scan will be processed if they exists and SQL Server optimizer's job is to choose the best way to execute a query. To improve query execution time the index is used and if a table that doesn't have indexes, or if the optimizer decides not to use an existing index or indexes, the system performs a table scan.
Index seeks are generally preferred for the highly selective queries, this means optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition. For the better optimization of performance and reduce fragmentation the rebuild of indexes and update statistics on relevant tables is compulsory. Also keep in mind that a database's index fillfactor might affect the performance and might not be the same so it has the indexes spaned accross more pages than the other database so this causes index fragmentation then performace issues.
When the query optimizer uses an index, it searches the index key columns, finds the storage location of the rows needed by the query and extracts the matching rows from that location. Generally, searching the index is much faster than searching the table because unlike a table, an index frequently contains very few columns per row and the rows are in sorted order. Periodically collecting the SQL trace for the complex queries and submit the same to Database Engine Tuning Advisor to help with the analysis of your database environment and in the selection of appropriate indexes.
To talk about filtered indexes, as they are introduced as a part of new features in SQL Server 2008 which helps optimizing the non-clustered indexes. For instance if a column which has a range of status values ranging from 0 to 99 and the application queries obtains the data between the values 20 and 30 then this is where the 'filtered index' will be suited for this scenario since it will have the data in a well defined subset. Index means storage which is the first thing comes into mind of DBA and for such doubts on the storage needs for the index will be reduced which goes hand in hand with lower maintenance overhead since the rebuild or update of the stats will take lesser time in this case. Not only that this will help for performance gain as well since it is smaller in size as compared to the regular non-clustered index.
You may be aware that creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. No difference between the data validation between them and as usual the query optimizer does not differentiate between a unique index created by a constraint or manually created. For the need of data integrity you should consider UNIQUE or PRIMARY KEY constraint and it may not be achievable if duplicate key values exist in the data. Say if the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. For additional optimization it is better to consider creating non-clustered indexes that contains included nonkey column, see Index with Included Columns link.
Filtered indexes can provide the following advantages over full-table indexes:
- Improved query performance and plan quality
A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.
- Reduced index maintenance costs
An index is maintained only when data manipulation language (DML) statements affect the data in the index. A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.
- Reduced index storage costs
Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.
It is a best practice to include a small number of key or included columns in a filtered index definition, and to incorporate only the columns that are necessary for the query optimizer to choose the filtered index for the query execution plan. The query optimizer can choose a filtered index for the query regardless of whether it does or does not cover the query. However, the query optimizer is more likely to choose a filtered index if it covers the query.
Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. Examples are:
- Sparse columns that contain only a few non-NULL values.
- Heterogeneous columns that contain categories of data.
- Columns that contain ranges of values such as dollar amounts, time, and dates.
- Table partitions that are defined by simple comparison logic for column values.
Reduced maintenance costs for filtered indexes are most noticeable when the number of rows in the index is small compared with a full-table index. If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. In this case, you should use a full-table index instead of a filtered index.
Filtered indexes are defined on one table and only support simple comparison operators. If you need a filter expression that references multiple tables or has complex logic, you should create a view.