@AaronBertrand Well, just put a large bucket at the end of the desk, turn the handle and Bingo! Corporate clean desk policy implemented!

@Keith You're fighting an uphill battle trying to index that SP (the term "peeing on a forest fire" comes to mind). You'd be better off fundamentally reworking that SP - as @ypercube noted you've got a lot of converting from type to type going on (function calls - i.e. COALESCE - are killer for performance because then the SQL Server has to run that function over every row in the table before trying to join to it).

@Keith Also as @dartonw noted, you've got a ton of columns in that SP, then eventually you boil most of that away. If you don't have to drag all that data around (and through nested subqueries at that), you'll find it's a lot easier to figure out what's going on and what's going wrong :)

@ypercube nice answer

thnx