Improve performance of Stored procedure

Ajit Goel 2017-04-11 21:09:39

The stored procedure uses unions, searching several columns using a string and table valued functions. SQL server tuning advisor suggests adding an index with three more columns. How can I improve the performance of this stored procedure? Any inputs will be appreciated

Aaron Bertrand 2017-04-11 21:39:00
I'd have to know more about your queries and the definitions of your table-valued functions. Could you generate another actual plan from within Plan Explorer and this time not anonymize it (or at least update to a more recent version of Plan Explorer first)? I assume the table-valued functions are multi-statement, is there any possibility you could make them inline?
Ajit Goel 2017-04-11 22:10:01
Thanks Aaron Bertrand, Here's the non-anonymized version of the plan. I believe some of the issues are caused due to the order of the columns in the non-clustered index, changing the order improved the performance by 10% but not as much as I expected.
The table valued functions are inline multi statement table valued functions.

link text