Optimizing Database Performance with Filtered Indexes: Insights from TechDays

1. INDIA │ 18-20 august2010 virtual techdays Filtered Indexes – The unexplored index … Vinod Kumar M │ Microsoft India Technology Evangelist – DB and BI www.ExtremeExperts.com http://blogs.sqlxml.org/vinodkumar T: @vinodk_sql

2. INDIA │ 18-20 august2010 virtual techdays • Filtered Indexes • What • Why • How • Filtered Indexes vs. Indexed Views • Plan Reuseability A G E N D A

3. INDIA │ 18-20 august2010 virtual techdays • Filtered Indexes are an index on a subset of rows in a table • WHERE clause in CREATE INDEX • Filtered Statistics are stats over a subset of rows • No auto-created filtered statistics What is Filtered Index

4. INDIA │ 18-20 august2010 virtual techdays • Improve query performance • Improve plan quality • Statistics are more accurate • Reduce index size • Reduce index maintenance cost • Large number of indexes are feasible • Query optimizer will choose the right index Why use Filtered Index

5. INDIA │ 18-20 august2010 virtual techdays • Sparse columns • Most rows contain NULL • Also when small subset of rows contain NULL (e.g. End Date) • IsDeleted flag columns • Categories of data in same table • Bikes, Clothing, Accessories • Sets of related properties • IN clause • Window Search - different types of properties • Ranges of data values • Dollar amounts, time ranges, date ranges • SharePoint lists (AllUserData table) • Table partitions Why: Use Cases for Filtered Index

6. INDIA │ 18-20 august2010 virtual techdays • < filtering_predicate > ::= {Conjunct [AND Conjunct]} • <Conjunct> ::= { Disjunct | Comparison } • <Disjunct> ::= { column IN (constant, …)} • <Comparison> ::= {column <comparison op> constant} • <comparison op> ::= IS | IS NOT | = | < > | ! = | > | > = | ! > | < | < = | ! < } • = NULL not supported (even with setting) How: Syntax

7. INDIA │ 18-20 august2010 virtual techdays • age = 10 and category > 20 • age = 10 and category > 20  and category < 50 • age in (10, 20, 30)  • age in (10, 20) and category in (15, 25) Examples of Predicates

8. INDIA │ 18-20 august2010 virtual techdays • Filtered indices can only be created as non-clustered indices. • Filtered indexes are not allowed on views unless they are indexed views. • Filtered indexes cannot be created on XML, spatial, or fulltext indexes. • If a filtered index is marked Unique, it means for all the qualifying rows, the index values are unique. • The filter expression cannot reference a computed column or UDT Rules

9. INDIA │ 18-20 august2010 virtual techdays • No ALTER INDEX to alter where clause • Use CREATE INDEX WITH DROP_EXISTING = ON • Online operations work • DTA recommends filtered indexes • Missing indexes don't report filtered index Behaviors

10. INDIA │ 18-20 august2010 virtual techdays • SET options required • ANSI_NULLS - ON • ANSI_PADDING - ON • ANSI_WARNINGS - ON • ARITHABORT - ON (set by ANSI_WARNINGS - ON) • CONCAT_NULL_YIELDS_NULL - ON • NUMERIC_ROUNDABORT - OFF • QUOTED_IDENTIFIER - ON • If not set correctly: • Insert/update/delete don't work • Optimizer doesn't consider plan • Can’t create index SET Options

11. INDIA │ 18-20 august2010 virtual techdays • Must match exactly • Filtered index col > 500 • Col > @a - no • Col > @a and Col > 500 - yes • Don't get used with autoparameterization • If parameter could be outside the filter Parameterized Queries

12. INDIA │ 18-20 august2010 virtual techdays • Filtered Stats updated based on colmodctr • Filter predicate not considered • Filtered Stats use same sampling algorithm • Filter predicate not considered • Filtered stats can become • Out of date or updated too frequently • Not representative of true cardinality • Solution: Use scheduled stats update • with FULLSCAN Filtered Statistics And Updating

13. INDIA │ 18-20 august2010 virtual techdays • sys.indexes • sys.stats • sys.dependencies Metadata

14. INDIA │ 18-20 august2010 virtual techdays DEMO: Filtered Index Explained and Explored !!! Vinod Kumar M www.ExtremeExperts.com

15. INDIA │ 18-20 august2010 virtual techdays • Filtered Indices work best on columns where the Filter index criteria is simple and filters out a major portion of the rows in the table. • These work best in situations where columns are sparsely populated and index criteria is used to select only data that is populated with relevant values. • Generally using filtered indexes are useful for Sparse columns where most of the values in the column are NULL. • If the index needs to cover all rows in a table, use a regular index • If not, and the filtering condition is simple (fits within the filtered index grammar set), use a filtered index. • If filtering condition is complex, use an indexed view. Best Practices

16. INDIA │ 18-20 august2010 virtual techdays Technology Comparison

17. INDIA │ 18-20 august2010 virtual techdays • Filtered Indexes: an alternative to indexed views • Available in all SQL Server editions • Only simple filtering predicates • Variety of Uses • Sparse columns the biggest • Filtered Statistics: space saving over indexes • But watch sampling and updating Summary

18. THANKS│18-20 august2010 virtual techdays www.ExtremeExperts.com http://blogs.sqlxml.org/vinodkumar T: @vinodk_sql