SQL Server keep reverting to inefficient plan (Clustered index Scan) once a week

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

Your index on UserID is not the optimal one for that query. It leaves the optimizer a choice of using it and needing an additional sort by CommentID or scanning the table (backwards) to get the rows already sorted by commentID and filtered on the fly by the where clause and the top operator. Although the clustered PK column is included in each nonclustered one, it is just as pointer so can't be used for sorting.

The best way to avoid it for a critical query like you describe is to provide an optimal index, so the optimizer will more likely choose it every time. Based on the information you provided, your index should be a composite nonclustered index on (UserID, CommentID DESC) This will allow both direct access to the users rows, and also the first 50 rows can be scanned in order of CommentID leaving it the optimal choice, regardless of statistics and selectivity.

SQL server is smart enough to realize it. Give it a try... HTH