I have an Azure SQL table which is synced to Azure Search using an indexer. The indexer datasource is configured with a "change tracking policy" High watermark column.
Based on the link below, it is recommended to use a rowversion data type for the high water mark column. https://docs.microsoft.com/en-us/azure/search/search-howto-connecting-azure-sql-database-to-azure-search-using-indexers
So my SQL table have a RowVersion field of datatype timestamp, with an index defined on it.
When I look at the Query Performance Insight of my database, one of the worst performing query is the following:
(@hwm bigint)SELECT * FROM [dam].[Asset] WHERE [RowVersion] > @hwm ORDER BY [RowVersion]
I assume this is the query made by indexer, since the count of executions fits with the refresh frequency of the indexer.
Notice how this query is using a bigint parameter.
This cause a full index scan when I look at the query execution plan... look at the Predicate, it uses a CONVERT_IMPLICIT()
Why isn't the indexer using the correct timestamp datatype to prevent this casting ?
