This article intends to give information about the SQL Server filtered indexes and their performance impacts.
Introduction
Indexes are the special data structures that help to improve the performance of the queries in SQL Server. Against this great benefit of the indexes, they occupy space on hard drives and can slow down the data modification operations (update, insert, delete) performance. When any query modifies the data in a table the database engine needs to update all of the related indexes where data has changed. In certain instances, to minimize these disadvantages of indexes, using SQL Server filtered indexes might be the appropriate approach. Assume that, we frequently query a small subset of a table with the same conditions and the rest of the table contains too many rows. In this scenario, we can use a SQL Server filtered index to access this small data set faster so that we can reduce storage and index maintenance costs.
What is SQL Server filtered index?
A SQL Server filtered index is a non-clustered index that allows us to apply specific conditions to cover a subset of rows in the table. Such as, we have a material table and the web application only requests the active material data. In this case, create an index for the only active material rows is very reasonable than creating an index for whole rows of the table. A well-designed filtered index can provide some advantages, and let’s tackle these advantages in the next sections.
Pre-requirements:
In this article, we will use the AdventureWorks database and we will also use the enlarging script to obtain a larger copy of this database.
SQL Server filtered index and query performance
As we stated, the indexes are one of the important database objects to improve the performance of the queries if they are designed effectively. Besides this, SQL Server can suggest missing indexes after processing a query and thinks that the suggested index can help to improve the performance of the query. Now, let’s enable the actual execution plan and execute the following query.
|
1 2 3 |
SELECT SalesOrderDetailID,UnitPrice,CarrierTrackingNumber FROM Sales.SalesOrderDetailEnlarged WHERE UnitPrice >1500 AND CarrierTrackingNumber IS NOT NULL |
As we can see, SQL Server suggests a missing index and it claims that the suggested index can improve the query performance by about 83%. We must not apply these index recommendations uncontrollably because SQL Server does not consider our workloads or other directories when creating these recommendations. Therefore before applying missing indexes we need to consider the pros and cons. However, for this query, the suggested index seems very reasonable because it covers CarrierTrackingNumber, UnitPrice columns so that all required columns will be involved in the index. Now, we will create the suggested index by the SQL Server but this index covers the whole rows of the table.
|
1 2 |
CREATE NONCLUSTERED INDEX IndexNonFiltered ON [Sales].[SalesOrderDetailEnlarged] ([UnitPrice],[CarrierTrackingNumber]) |
After creating the index, we will re-execute the query and analyze its execution plan.
|
1 2 |
SELECT SalesOrderDetailID ,UnitPrice,CarrierTrackingNumber FROM Sales.SalesOrderDetailEnlarged WHERE UnitPrice >1500 AND CarrierTrackingNumber IS NOT NULL |
The NonClustered Seek operator, which appears in the query’s execution plan, indicates that the storage engine finds matching rows using the b-tree structure of the nonclustered index. The Actual Logical Reads attribute of the nonclustered seek operator shows the number of pages read from the data cache.
Indexes occupy a size in the hard drives according to indexed column data types and the number of the table rows. The following query shows the index sizes and our index size is 179.136 MB.
|
1 2 3 4 5 6 7 8 9 |
SELECT i.name AS IndexName, SUM(s.used_page_count) * 8 AS IndexSizeKB FROM sys.dm_db_partition_stats AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.[object_id] = object_id('Sales.SalesOrderDetailEnlarged ') and i.name= 'IndexNonFiltered' GROUP BY i.name |
Now we will create a SQL Server filtered index for rows which are the UnitPrice column values are greater than 1500 and CarrierTrackingNumber column values are not null, that is, we will create a customized index for the where clause of our query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IndexNonFiltered' AND object_id = OBJECT_ID (N'Sales.SalesOrderDetailEnlarged')) BEGIN DROP INDEX IndexNonFiltered ON Sales.SalesOrderDetailEnlarged END ---Create Filtered Index CREATE NONCLUSTERED INDEX IndexFiltered ON [Sales].[SalesOrderDetailEnlarged] (UnitPrice , CarrierTrackingNumber) WHERE UnitPrice >1500 AND CarrierTrackingNumber IS NOT NULL GO |
Now, we will execute the same query again analyze the execution plan.
|
1 2 3 4 |
SELECT SalesOrderDetailID ,UnitPrice, CarrierTrackingNumber FROM Sales.SalesOrderDetailEnlarged WHERE UnitPrice >1500 AND CarrierTrackingNumber IS NOT NULL |
The execution plan shows us that, the SQL Server filtered index was used by the query optimizer to execute the query. However, we are seeing a warning sign over on the select operator but this a bug related to the SQL Server for this reason we can ignore this warning sign for this query. When we check out the l/O statistics of this query, it shows us the query only reads 483 data pages from the buffer cache. This value is very smaller than the full-table index.
Statistics stores the distribution of the column values and plays a key role in the query performance because the query optimizer uses statistics data to estimate how many rows can be returned from the executing query. When we create an index, statistics are automatically generated to store the distribution of the column values. On the select operator, we can find out which statistics are used by the query optimizer. OptimizerStatUsage attribute shows us the used statistics names and other details about the used statistics during the execution of the query.
The LastUpdate sub-attribute shows when was the statistics last updated and the Modification Count sub-attribute shows how many times the statistic is modified after the statistics are updated. DBCC SHOW_STATISTICS command is used to obtain details about the statistics. With the help of the following query, we can get information about the filtered index which is created by us.
|
1 |
DBCC SHOW_STATISTICS('Sales.SalesOrderDetailEnlarged','IndexFiltered') |
The above image shows us the histogram (statistical representation of your data) is created for only the filtered values.
At the same time, the space occupied by the filtered index on the hard disk is smaller than the full-table index.
|
1 2 3 4 5 6 7 8 9 |
SELECT i.name AS IndexName, SUM(s.used_page_count) * 8 AS IndexSizeKB FROM sys.dm_db_partition_stats AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.[object_id] = object_id('Sales.SalesOrderDetailEnlarged ') and i.name= 'IndexFiltered' GROUP BY i.name |
As a result, using index has provided us with 2 significant benefits:
- Improve the query performance and plan quality
- Reduce the index storage cost
SQL Server filtered index disadvantages
As with the most of features in SQL Server, filtered indexes have advantages and disadvantages. In this section, we will take a glace at these disadvantages of the filtered indexes. The stored procedures are the prepared SQL queries to invoke and can take input as parameters. Now, we will create a very simple stored procedure that accepts a parameter. This parameter name is @PUnitPrice and its data type is money.
|
1 2 3 4 5 6 7 |
CREATE OR ALTER PROCEDURE TestFilterIndex @PUnitPrice AS MONEY AS SELECT SalesOrderDetailID ,UnitPrice, CarrierTrackingNumber FROM Sales.SalesOrderDetailEnlarged WHERE CarrierTrackingNumber IS NOT NULL AND UnitPrice >@PUnitPrice |
After creating the stored procedure, we set the @PUnitPrice parameter to 1500 value and invoke the procedure. In this case, we expect that the query uses the filtered index. Let’s analyze the stored procedure execution plan.
|
1 |
EXECUTE TestFilterIndex 1500 |
The stored procedure query plan did not use the filtered index which was created and it used the clustered index scan operator instead of it. What is the reason for this behavior of the query optimizer? Actually, the answer is very basic because the stored procedures query plans are stored in the query plan cache so that the cached query plans can be reused by the query optimizer. However, if we invoke the stored procedure for the different parameters, the filtered index and its statistics don’t know anything about these values. Therefore, the query optimizer does not use a filtered index for the parameterized queries. Maybe, we can think of using an index hint because this hint forces the query optimizer to use the specified index in the query. Now we will alter the TestFilterIndex procedure and force it to use the filtered index with help of the index hint. SQL Server does not return an error during alter the stored procedure.
|
1 2 3 4 5 6 7 8 |
ALTER PROCEDURE [dbo].[TestFilterIndex] @PUnitPrice AS MONEY AS SELECT SalesOrderDetailID ,UnitPrice, CarrierTrackingNumber FROM Sales.SalesOrderDetailEnlarged WITH (INDEX(IndexFiltered)) WHERE CarrierTrackingNumber IS NOT NULL AND UnitPrice >@PUnitPrice |
As a second step, we try to invoke the stored procedure.
|
1 |
EXECUTE TestFilterIndex 1500 |
As we can see, during the execution of the stored procedure, SQL Server returns an error about this index hint usage. The RECOMPILE option can resolve this problem for only the parameters that provide filter index expression.
|
1 2 3 4 5 6 7 8 9 |
ALTER PROCEDURE [dbo].[TestFilterIndex] @PUnitPrice AS MONEY AS SELECT SalesOrderDetailID ,UnitPrice, CarrierTrackingNumber FROM Sales.SalesOrderDetailEnlarged WITH (INDEX(IndexFiltered)) WHERE CarrierTrackingNumber IS NOT NULL AND UnitPrice >@PUnitPrice OPTION (RECOMPILE) |
When we execute the stored procedure with the same parameter, it will run successfully.
|
1 |
EXECUTE TestFilterIndex 1500 |
However, if we invoke the procedure for the following parameters SQL Server continues to return an error.
|
1 |
EXECUTE TestFilterIndex 1000 |
Conclusion
In this article, we focused on the SQL Server filtered index. When the filter index is used in appropriate approaches, it gives us an advantage in query performance.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023