I have the following expensive query that runs against our data warehouse:
SELECT d.DateYear, COUNT_BIG(*) AS WidgetCount FROM Widgets AS w JOIN Dates AS d ON d.DateId = w.DateId WHERE w.TypeId IN (1, 2) GROUP BY d.DateYear
I've discovered that I can hugely increase the performance of this query with the following indexed view (see also: Using indexed views for aggregates - too good to be true?)
CREATE VIEW IX_Widgets WITH SCHEMABINDING AS SELECT TypeId, DateId, COUNT_BIG(*) AS WidgetCount FROM Widgets GROUP BY DateId GO CREATE UNIQUE CLUSTERED INDEX IX_Widgets ON IX_Widgets ( TypeId, DateId )
The best bit is that SQL Server starts using this indexed view even though its not referenced in the original query - this is really nice for a data warehouse as it means that queries can be written against a well-defined reporting schema, and then supported by crazy (but super efficient) indexed views that do weird groups.
The frustrating thing is that as soon as I change the typeIds that I am filtering by or parameterise the TypeId values (e.g.
WHERE w.TypeId IN (@typeId1, @typeId2, ...)) SQL Server stops using the indexed view and reverts back to a really slow query.
What decides when SQL Server will use an indexed view? I've experienced similar problems in the past where it is difficult to determine when SQL Server will and won't use an indexed view. It would be great to be able to use this without having to change my query to explicitly use this view, but only if I know with certainty that its going to perform.