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.
I've been scratching my head for days now... Any ideas?