Timeline for What is the effect of replacing indexes with filtered (non-null value) indexes?
Current License: CC BY-SA 4.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 8, 2019 at 17:44 | history | edited | Marcello Miorelli | CC BY-SA 4.0 |
added link to another answer as quoted
|
| Oct 5, 2015 at 8:29 | comment | added | Kahn | Also, in some cases using page compression was far preferable, so we ended up doing that instead. It's also handy since you can simply create the existing clustered index with DROP_EXISTING = ON, to make it far, far faster than going the sparse route. Especially since it avoids the whole hassle of re-managing indexes and FK's. | |
| Oct 5, 2015 at 8:27 | comment | added | Kahn | Also, we did change quite a few columns to sparse types. The problem with that however is that as you'll see from MSDN, altering a column type to sparse basically forces the whole clustered index to be recreated. Making this rather heavy for large, complex tables. So we renamed the constraints and the table, created a new one with the same model and original name but with sparse columns, and then transferred the data into the new table in appropriate batches. Then once checked that everything was ok and all the indexes and FK's were again in place, dropped the old tables. | |
| Oct 5, 2015 at 8:25 | comment | added | Kahn | Hi. A bit late to the fray but yes, while we abandoned the approach described in this topic a long time ago, we did recently come back to it with a more selective approach. Basically, we looked at the statistics usage and business model to confirm the indexes on a table per table basis. Then tested it by adding a new filtered index on the side of the normal one, and checked to see over a few weeks which one ended up being used. After confirming that ONLY the filtered indexes were used in new plans, we dropped the normal nonfiltered ones. | |
| Oct 4, 2015 at 7:20 | history | edited | Marcello Miorelli | CC BY-SA 3.0 |
added 195 characters in body
|
| Oct 4, 2015 at 7:11 | history | edited | Marcello Miorelli | CC BY-SA 3.0 |
added 281 characters in body
|
| Oct 4, 2015 at 6:57 | history | answered | Marcello Miorelli | CC BY-SA 3.0 |