Filtered indexes are one of those neat little SQL Server features that a lot of people don’t seem to know about, or if they do they don’t use them very much. While I agree that putting a filter on every index probably isn’t a good idea, there are cases where doing so can really help performance.
What Is A Filtered Index?
A filtered index is simply a nonclustered index that has a WHERE clause, which is used to filter the data that is stored in the index. Having a more targeted result set in your index can lead to improved performance in your queries that use that index. For a more detailed look at filtered indexes, see RedGate’s Introduction to SQL Server Filtered Indexes.
How Do I Find Filtered Indexes?
Filtered Indexes can be found by querying the built in sys.indexes DMV. To get the schema and table that the indexes belong to, you’ll also need to join to the sys.schemas and sys.tables DMVs. Below is a simple query that will provide a list of filtered indexes, with the schema and table, as well as the actual filter used in the index.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE YourDB GO SELECT [SchemaName] = s.[Name] ,[TableName] = t.[Name] ,[IndexName] = i.[Name] ,[IndexType] = i.[type_desc] ,[Filter] = i.filter_definition FROM sys.indexes i INNER JOIN sys.tables t ON t.object_id = i.object_id INNER JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.type_desc = N'USER_TABLE' AND i.has_filter = 1 |
The above query is database specific, so you will need to run it against every database you want to view filtered indexes on. Filtered indexes can also be checked using my SQL Server Metrics Pack project, and a copy of the above query can also be found in my Index Insight Scripts on GitHub.
This post is part of an ongoing SQL Scripts series, in which I list useful queries that I use in my day to day database administration.