Procedure is getting executed by .Net application which provides store list (it would be 800 some store list in it as a string). This procedure takes from 9seconds runtime to 1mins and 50seconds.

This procedure is getting executed constantly and is taking between 9seconds runtime to over 1min sometimes. Wanted to know which part in the procedure is the bottleneck and is there any thing we can do to prevent it?

This is the procedure parameters listed as:

ALTER PROCEDURE [dbo].[usp_Transaction_GetTransactionToProcess]
@SourceStore VARCHAR(MAX) = ", — Empty for all or comma-delimited string of stores for use in IN clause – ex. 'C0001','C0002'
@TransactionTypeID INT = NULL, — NULL for all types – should pass NULL – not used anymore
@TopX INT = 1,
@TransactionTypeIDs VARCHAR(MAX) = " — Comma-delimited string of transaction type ids to include in results – ex. 3,4,5

We see EXEC(@SQL) is the highest counters with CPU and READS, but if we run the query building from the dynamic SQL runs in less than a seconds from SSMS.