Why would SQL Server stop excluding partitions appropriately?

I have a Fact Table that will run with an appropriate plan during our morning processing. Occasionally, later in the day, if I go to query it, it will have a really strange plan.

When querying a table partitioned on date, you usually see a seek predicate like so:

But for some reason, it turns into this:

Yes, I’m aware that there are two different tables shown above. I pulled the good example from a similarly configured table, and it appropriately displays the seek predicate I would expect.

Both of these are Clustered Index Seek operators. Updating stats doesn’t seem to help, but some part of Ola’s maintenance scripts seem to fix it each morning.

Has anybody seen this before? Any tips on how to restore the good plan?

In the second execution plan, the seek predicate has a CONVERT_IMPLICIT function in it. This means the wrong data type was input and SQL Server has to convert the data type to something else. In this case, it was expecting a DATE data type and something other than DATE was passed in… most likely a (VAR)CHAR or N(VAR)CHAR value. The fix would be to either explicitly cast/convert the value before submitting the query or fix the code to use the proper data type.

Here are some further resources on the matter:

• Implicit_Conversions
• Conversion Matrix
• Performance Issues with Convert_Implicit