Problem solved !!

I just ran the stats in production. The query is running under a second.

Auto update stats is turned ON and I dont think stats was the real issue.

REASON:...

Some more additional information from the developers....the query is hardcoded within the application and executed on SQL database. It is NOT being executed within a stored procedure.

Also, when the query...

Thanks Gianluca and Craig for the suggestions.

The explain plans are different in prod and test although test is a clone of prod.

The wait time and type are both zero...