Chris-475469 (10/26/2016)
Thanks for you reply.I discovered someone had added an index on the risk__tag table which was causing the strange plan in production. I agree the code isn't great and I'm currently working my way through a mountain of inefficient SQL.
There is more than just a mountain of inefficient SQL code here. Your data requiring concatenation isn't good. Nor is those "match on a data hit OR some default value". I would also probably OPTION (RECOMPILE) everyone of those queries for a variety of reasons.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service