Improve performance of Stored procedure

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

avatar image By Ajit Goel 1 asked Apr 11, 2017 at 09:09 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Apr 11, 2017 at 09:39 PM

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?

avatar image Ajit Goel Apr 11, 2017 at 10:10 PM

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

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

We are Moving!


Follow this question



asked: Apr 11, 2017 at 09:09 PM

Seen: 400 times

Last Updated: Apr 11, 2017 at 10:10 PM