Lynn Pettis (4/17/2014)
ScottPletcher (4/17/2014)
Lynn Pettis (4/17/2014)
ScottPletcher (4/17/2014)
You need to do further research before you change the existing index to a filtered index, because that index will no longer satisfy queries originally using it if you filter out the values it was using.Besides, I was hoping that the date range itself would eliminate enough rows to improve I/O significantly without impairing any existing uses of the index.
What date range? The query wants EVERYTHING prior to a specific date.
That's still a date range, from beginning date to the specified date. As I noted earlier, specific row counts would be necessary to do a full analysis, but coming in by date at least guarantees reducing the total number of rows that need searched.
Isn't if a far greater concern that willy-nilly changing an existing index to a filtered index could destroy the performance of a large number of existing queries that currently perform quite well?
There is no specified start date. For all we know there could be 10+ years of data in this table and the query wants it all up to a specified point in time. As more data is added and the endpoint of this query progresses, more data will be pulled by the query.
And actually, I would create a new filtered index, not modify an existing index to be a filtered index. Modifying an existing index to be a filtered index could break other queries dependent on that index.
It's still a date range, from the starting date to a specified ending date. Not sure why you'd object to that. For all we know, they may run queries that specify and ending date where they need only one years' worth of data. If they need to read 10+ years' worth of data, they'll have to do that anyway.
And actually, you did suggest changing the existing index to filtered.
I don't believe it's worth the big overhead of an additional index on this large a table when you already have an index that, with a very small addition, can cover this query.
It was clear from earlier runs that other filters on the data didn't limit the data dramatically either. Given everything, with all known trade-offs considered, I'd rather have SQL read even a million more 15 bytes rows than create another index. Maybe if this query is to run frequently, and/or at peak/critical times, you might create a custom index. But otherwise, I don't see how such an index is worth its high cost.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.