I have a very simple Query that selects records from a table where the timestamp (cast as a BigInt) is greater than a previously stored BigInt. It runs on SQL 2012 EE used to be quite fast (~100ms) and overnight slowed down (~5 Seconds). I looked at the execution plan and noticed it is using an Indexed View. I am not sure if it has always used the indexed view, but I suspect it swapped to using the indexed view and was using the primary key before. When I restructure the Query by swapping the cast, the query behaves differently on this server and uses the Primary Key and is quite fast.
There has been no Server configuration changes, Hardware or software changes and the data in the database is changing constantly.
My Question is: In what circumstances can SQL swap to using the Indexed View on EE when in fact the Primary key executes the query faster? I have read NoExapand will prevent the View from being considered as long as it is named in the Query, but it is not. The Query is very straight-forward:
SELECT DISTINCT cast(tab.[Document Type] as varchar)+','+tab.[Document No] AS RecID FROM [MyTableName] AS tab WHERE tab.[timestamp] > cast(cast(918273645 as bigint) as timestamp) AND (tab.[Document Type]=1)
I would really appreciate your insights into the inner workings of SQL Server 2012 Enterprise Edition
Kind regards and thank you.