Sql-server – SQL Server keep reverting to inefficient plan (Clustered index Scan) once a week

execution-planindexperformancequery-performancesql serversql-server-2016

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.

why, Gosh, why???

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?

Best Answer

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

Related Question