Parameter Sniffing Not working

Question

I am using MSSQL. I have a stored procedure which works fine for couple of days and later on it becomes slow. I came to know that Parameter Sniffing will work for it. How ever after implementing it it became slow for ever. I also tried to Recompiling job. I faced the same slowness issue immediately.

Can some one please help me with this ?

Below is the structure of my Stored Procedure.

@START_VALUE int=null,
@END_VALUE int=null
@UID NVARCHAR(MAX)=null,
AS
BEGIN

SELECT
dbo.TABLE1.ID, 
ROW_NUMBER()  OVER (ORDER BY TABLE1.UPDATED_ON desc) AS RN,   
CONVERT(VARCHAR(10), dbo.TABLE1.DATE, 101) AS TDATE,
CATEGORY = (
        SELECT TOP 1 COLUMN1
        FROM TABLE5 CT1
        WHERE TABLE1.CATEGORY = CT1.CATEGORY_ID
   ), 
TYPETEXT = (
        SELECT TOP 1 COLUMN1
        FROM TABLE6 CT1
        WHERE TABLE1.TYPE = CT1.TYPE_ID
   ),
IMAGE = STUFF(( SELECT DISTINCT ',' + CAST(pm.C1 AS varchar(12))
                FROM TABLE2 pm
                WHERE pm.ID = TABLE1.ID AND pm.C1 IS NOT NULL AND pm.C1 <> '' 
                FOR XML PATH('')),
              1, 1, '' ) INTO #tempRecords       
FROM dbo.TABLE1 
WHERE ((@UID is null OR  dbo.TABLE1.ID = @UID )
ORDER BY TABLE1.UPDATED DESC      

SELECT @count = COUNT(*) FROM #tempRecords;

SELECT *, CONVERT([int],@count) AS 'TOTAL_RECORDS'
FROM #tempRecords 
WHERE #tempRecords.RN BETWEEN CONVERT([bigint], @START_VALUE) AND       CONVERT([bigint], @END_VALUE)      

 END

 GO

Answer 1

To make the query optimized for parameter sniffing, declare dummy variables and use them in your query, as opposed to using the original parameters as such.

CREATE PROCEDURE test_proc 
      @START_VALUE INT=NULL, 
      @END_VALUE   INT=NULL, 
      @UID         NVARCHAR(max)=NULL
    as 
      BEGIN 

        DECLARE @START_VALUE_SNIFF INT=NULL, 
        @END_VALUE_SNIFF   INT=NULL, 
        @UID_SNIFF         NVARCHAR(max)=NULL

        SET @START_VALUE_SNIFF = @START_VALUE
        SET @END_VALUE_SNIFF = @END_VALUE
        SET @UID_SNIFF = @UID

        select * from 
        FROM dbo.TABLE1 
        WHERE ((@UID_SNIFF  is null OR  dbo.TABLE1.ID = @UID_SNIFF )
        ORDER BY TABLE1.UPDATED DESC   

    END