SQL Server keep reverting to inefficient plan (Clustered index Scan) once a week I have a very simple query:

INSERT INTO #tmptbl
SELECT TOP 50 CommentID --this is primary key
FROM Comments WITH(NOLOCK)
WHERE UserID=@UserID
ORDER BY CommentID DESC


against this table:

CREATE TABLE [dbo].[Comments] (
    [CommentID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [CommentDate] datetime NOT NULL DEFAULT (getdate()),
    [UserID] int NULL ,
    [Body] nvarchar(max) NOT NULL,
--a couple of other int and bit cols, no indexes on them
)


I have a simple index on the UserID column (no cols included) and everything works just fine and super-fast.

But once every 5-8 days I see timeouts in that part of the application. So I go to investigate in the Query Store and I see that the server stops using my index and reverts to a stupid "clustered scan". Removing the temp table doesn't help.



In order to resolve this - I reset the plan cache for this particular query (just for the record here's how I do this)

select plan_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
where text like '%SELECT TOP 50 CommentID FROM hdComments%'
--blahblahblah skipped some code
DBCC FREEPROCCACHE (@plan_handle)


And then starts working normally again.

Execution plans: slow fast 

I've been scratching my head for days now... Any ideas?
Should SQLRaptor's answer not work out for you, one other drastic thing you can try is using the query hint FORCESEEK. This essentially forces the optimizer to always use a plan that does an index seek instead of an index scan (if possible). 

One reason it's not a first go-to is because it limits the number of query plans that the optimizer can choose to use and in certain cases will error out by saying no plan available for that query hint. Typically query hints are more of a last resort bandaid fix (except in specific edge cases) but arguably less drastic than running the DBCC FREEPROCCACHE command and probably less drastic than always recompiling the query with the OPTION RECOMPILE hint from KumarHarsh's answer too.

(This ended up being the best solution for one particular scenario I recently ran into with a table that had billions of records and the optimizer was always trying to use a clustered index scan but there was a nonclustered index that was more applicable to the query and was actually always faster as a seek.)

See the FORCESEEK section of the Microsoft doc for more information: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15