Esat Erkec
SQL Server filtered index and stored procedure

Introduction to SQL Server Filtered Indexes

August 17, 2021 by

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.

Execution plan of a query

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.

After creating the index, we will re-execute the query and analyze its execution plan.

Filtered Index and execution plan

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.

Actual Logical Read attribute of an execution

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.

Size of an index

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.

Now, we will execute the same query again analyze the execution plan.

SQL Server non-clustered index scan

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.

Actual Logical Read

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.

OptimizerStatsUsage attribute details

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.

DBCC SHOW_STATISTICS command usage details

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.

Size of an index SQL Server filtered index

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.

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.

SQL Server filtered index and stored procedure

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.

How to use index hint in a query

As a second step, we try to invoke the stored procedure.

Query processor could not produce a query plan because of the hints defined in this query - error details

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.

When we execute the stored procedure with the same parameter, it will run successfully.

Execute a stored procedure with a filtered index

However, if we invoke the procedure for the following parameters SQL Server continues to return an error.

SQL Server stored procedure error

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.

Esat Erkec
Latest posts by Esat Erkec (see all)
677 Views