Hello,
I have a database where I have a select query which runs slowly just for some parameters. The database has actual statistics (full scan), I removed old execution plans (did not restart, just remove these plans), no missing indexes were found but for some parameters (especially parameter P4 and 2 values for this parameter), this command runs for several minutes, but for other it takes a few seconds.
Execution plan can be found here:
https://www.brentozar.com/pastetheplan/?id=HkWJSj6SO
I know, that the SQL is not ideal and also join over 6 tables is not ideal, but it is hard to say, that the design is not optimal when there is problem with just 2 parameters. I suspect an issue with parameter sniffing, but I am not sure about that (how to solved). I would use a workaround where I would generate a special table with agregated results, but I hope, that there exists a solution for this issue/situation. Can you please provide any ideas how to do that?
Thank you for any help or any guide to solve this issue.
Regards
Ondrej
