SQL Query on Enterprise Edition slowed down using the VSIFT indexed view.

CraigNeedham
CraigNeedham Member Posts: 3
in SQL Performance
Dear Experts

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? 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, why would it all of a sudden execute the same code significantly slower than the day before...

Kind regards and thank you.

Answers

Categories