gistfile1.txt

CREATE INDEX ix_whatever ON dbo.Users (Reputation, Age, CreationDate);
GO 

CREATE OR ALTER PROCEDURE dbo.WORLDSTAR (@Reputation INT)
AS
SET NOCOUNT, XACT_ABORT ON 
BEGIN    

SET STATISTICS TIME, IO ON;

	/*Regular Key Lookup*/
    SELECT TOP (1000) 
	        u.*
    FROM dbo.Users AS u
    WHERE u.Reputation = @Reputation
    ORDER BY u.CreationDate DESC;

SET STATISTICS TIME, IO OFF;
    
SET STATISTICS TIME, IO ON;

	/*Self Join Instead*/
    SELECT TOP (1000) 
	        u2.*
    FROM dbo.Users AS u
    JOIN dbo.Users AS u2
        ON u.Id = u2.Id
    WHERE u.Reputation = @Reputation
    ORDER BY u.CreationDate DESC;

SET STATISTICS TIME, IO OFF;

END

/*oh no parameter sniffing! the little plan is first!*/
EXEC dbo.WORLDSTAR @Reputation = 2;

/*UH OH HERE COMES THE BIG PLAN*/
EXEC dbo.WORLDSTAR @Reputation = 1;