how to add option(recompile) within this stored procedure?

Question

In my current environment, I have many instances of stored procedures, just like the one shown below, where a bunch of parameters are passed to the procedure, and then within the procedure a select exists is run and based on the result, different logic paths are run within the stored procedure.

I have a couple of questions regarding the procedure below:

1) is it a good candidate for parameter sniffing?

2) how could I add option(recompile) within the code?

There are differences between adding option(recompile) within the code and creating a stored procedure with recompile.

I would be more keen on the option(recompile) if that is possible.

ALTER PROCEDURE [dbo].[usp_upd_activity]
 @activityId INT,
 @title VARCHAR(100), 
 @description VARCHAR(MAX),
 @inclusions VARCHAR(MAX),
 @locationId INT,
 @imageUriMain VARCHAR(255),
 @uploadToBucket VARCHAR(200),
 @path VARCHAR(200)
AS
BEGIN
 SET NOCOUNT ON;
 BEGIN TRAN 
 BEGIN TRY
 DECLARE @documentId INT
 IF NOT EXISTS (SELECT 1
 FROM document 
 WHERE activityId = @activityId)
 BEGIN
 INSERT INTO document 
 ( [uploadToBucket], [path], [activityId])
 VALUES (@uploadToBucket, @path, @activityId)
 SET @documentId = SCOPE_IDENTITY();
 END
 ELSE
 BEGIN
 UPDATE document
 SET uploadToBucket = @uploadToBucket,
 [path] = @path,
 activityId = @activityId
 WHERE activityId = @activityId 
 SET @documentId = 
 (SELECT documentId 
 FROM document 
 WHERE activityId = @activityId)
 END
 UPDATE activity
 SET title = @title,
 description = @description,
 inclusions = @inclusions,
 locationId = @locationId,
 imageUriMain = IsNull(@imageUriMain, imageUriMain),
 documentId = @documentId
 WHERE activityId = @activityId 
 COMMIT
 END TRY
 BEGIN CATCH
 DECLARE @ErrorMessage nvarchar(max), 
 @ErrorSeverity int, 
 @ErrorState int;
 SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + 
 cast(ERROR_LINE() as nvarchar(5)), 
 @ErrorSeverity = ERROR_SEVERITY(), 
 @ErrorState = ERROR_STATE();
 IF @@TRANCOUNT > 0
 ROLLBACK;
 RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
 END CATCH 
END

• Shanky: For recompile this should work ALTER PROCEDURE [dbo].[usp_upd_activity] @activityId INT, @title VARCHAR(100), @description VARCHAR(MAX), @inclusions VARCHAR(MAX), @locationId INT, @imageUriMain VARCHAR(255), @uploadToBucket VARCHAR(200), @path VARCHAR(200) WITH RECOMPILE AS.
• Marcello Miorelli: @Shanky thank you but I would prefer option(recompile) if possible.
• Aaron Bertrand: @Shanky There are very few scenarios where you should set that at the procedure level; perhaps zero in modern versions of SQL Server. See Parameter Sniffing, Embedding, and the RECOMPILE Options.
• Shanky: @AaronBertrand I just gave him option as he was looking for one.
• Biju jose: @marcellomiorelli, Some thoughts, your ELSE part can benefit from OUTPUT clause I believe. Use a table variable to store the document id in it. So a lookup to the document table can be reduced.

Answer 1

No, this does not look to be a good candidate for recompile. From the code, it looks like activityId is unique and will result in a trivial plan so you'll get the same plan regardless of the parameters passed. Add DDL to your question if this is not the case in your situation.

In cases where the optimal plan differs based on the actual parameter values passed (because these are used in WHERE/JOIN clauses and the optimal plan varies accordingly), you could specify the OPTION(RECOMPILE) query hint on specific problem queries to avoid reusing non-trivial plans. However, if the query is executed quite frequently, the compilation cost could outweigh the benefits. It would be better in that case to use an OPTIMIZE FOR hint or use the query store (or plan guides) to coerce a specific plan.