0

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
  • 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. – Shanky Sep 19 '18 at 9:56
  • @Shanky thank you but I would prefer option(recompile) if possible. – Marcello Miorelli Sep 19 '18 at 10:07
  • @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. – Aaron Bertrand Sep 19 '18 at 11:42
  • @AaronBertrand I just gave him option as he was looking for one. – Shanky Sep 19 '18 at 12:45
  • @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. – Biju jose Sep 19 '18 at 16:21
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.