I spent a little more time playing with SQL 2008 this week, though not as much time as I would have liked. I took a look at filtered indexes and some enhancements and changes around the display of the execution plans in Management Studio.
Filtered indexes
These are an interesting feature. There are many uses for these in larger systems and on larger tables. Normally an index is built over an entire table. With a filtered index, one or more predicates is specified when the index is created and the index only contains data that satisfies the predicates. The columns involved in the predicate do not have to be part of the index key, or the index include columns.
A use I can see for this is a history table, where many months of data is stored within the table, but only the current month’s data is extensively queried. Previously any index had to be created over the entire table, potentially including lots of rows that would never be queried. Now, the indexes can be created only on the activly queried portion of the table, reducing the size of the index tree and the space taken on disk
I need to do some investigation on how well the optimiser can match queries to filtered indexes. Expect more written on that point in the future.
Execution plan
There have been some interesting and useful changes in how the execution plan is displayed in management studio.
I say displayed, because the structure of the XML plan has not changed from SQL 2005. The XML Schema definition is the same and SQL 2005’s management studio can read and display an execution plan. This is good in one sence, in that xpath queries that were developed for the 2005 showplan format will still work in SQL 2008. On the other hand, it means that the new 2008 features (sparse columns, filtered indexes) won’t be flagged as such in the execution plans.
Missing indexes are now flagged in the graphical execution plan.This may or may not be a good thing. It does increase the visibility of the lack of indexes, however the missing index report within an execution plan is just the optimiser’s quick guess as to what index would have made the query more efficient. There may well be a very similar index already existing. My concern is that this feature will result in lots of useless or near useless indexes created. I suppose it can’t be worse than no indexes though.
The execution plan now shows the number of executions that occurred for a particular operator, as well as the number that was estimated. This has been missing since Query Analyser and I am very glad to see it coming back. Knowing whether the sort executed once or a thousand times does really help in identifying the cause of slowness.
More 2008 stuff probably next week. If I get a chance to play, that is.
I could say that it is not so easy to find a real business case to implement that “Filtered Index” feature… Let’s choose the case you propose “History table”, i wonder if every month you have to redefine the index and then rebuild it for get the expected behavior and get the benefits listed. What do you think? Another interesting question could be how to get the index working (as expected) without a constant monitoring of the new incoming values to ensure well-defining the data set(MS documentation states: “A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data.”)
I discussed a good use for them with a friend, then he blogged on it before I could…
http://craign.net/2008/08/18/a-practical-use-for-filtered-indexes/
If you’re using it for a history table, then yes, you would probably have to redefine the index at the end of the month, but then if you were storing month data partitioned (using partitioned tables or a manual partitioning) there’s maintenance that would have to be done at month end also.
It’s not something with immediate uses. I have some ideas for where it might be useful, but I have to do some testing first.