Skip to main content
2 of 5
added 314 characters in body
SqlZim
  • 2.4k
  • 1
  • 12
  • 22

Your where condition that checks for equality for utcdt converted to a date is SARGable, but uses a dynamic seek.

The problem is that these hidden implicit conversions can result in inaccurate cardinality and distribution estimations at any stage of the plan. So, even if you get a seek, the plan might be way off overall. - Dynamic Seeks and Hidden Implicit Conversions - Paul White

Try converting your where condition to an explicit range and see if there is any improvement:

select *
from Tick.X_H
where Symbol = 'donkey'
  and utcdt >= convert(datetime2(7),'20110102')
  and utcdt <  convert(datetime2(7),'20110103')

Based on this answer by Martin Smith the explicit range may reduce unnecessary reads.

References:

SqlZim
  • 2.4k
  • 1
  • 12
  • 22