I am dealing with an issue that I cant seem to solve. I was given this stored procedure that is having speed issues, it takes 35 seconds or so. When ran adhoc it completes instantly. This made me think it was something with parameter sniffing. I have tried rewriting this to use local variables, optimize for unknown\variable\etc, with recompile. This table the scan is happening on is massive, but even with stats updated with fullscan, estimates are still WAY off. Perhaps the query could just be rewritten, but would like to figure this out.

[all data from testing environment with names altered] .sql text files for sp and ad hoc query - let me know if this doesnt work - https://file.io/8Rh5DA3nePHW

ad hoc - https://www.brentozar.com/pastetheplan/?id=Hkvg5Ge40

Parameters for adhoc -

SP - https://www.brentozar.com/pastetheplan/?id=S1w49GxER

Parameters for SP -