0

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:

goodquery

But for some reason, it turns into this:

enter image description here

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?

2

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:

| improve this answer | |
  • I understand what you are saying - but I'm not sure how to fix it. We query this table all the time using where SaleDate = '8/1/2016' and this doesn't happen. This is the same way I queried the good plan pictured above. I added an explicit cast and convert, with no luck. – IronicMuffin Aug 4 '16 at 13:33
  • Ok, scratch that - an explicit convert with option (recompile) seems to have done the trick. I still find this behavior strange. Thank you for your help. – IronicMuffin Aug 4 '16 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.