4

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?

3

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

3
+75

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

  • Thanks. I tried including "CommentID" into the index (kinda helped, still waiting for results), but your solution makes even more sense. – jitbit Nov 12 at 11:10
  • If commentid is defined as primary key clustered then, its already present in the NC index as a hidden column. I guess you are trying to emphasize on DESC order. – Learning_DBAdmin Nov 17 at 8:36
  • @jitbit just for testing, could you create a workload stress test, cloning Commnts table as Heap table, with 2 separate indexes and observe when the plan changes, huge clustered tables with nvarchar(max) columns , sometimes, may reveal satanic behavior – Diego Scaravaggi Nov 17 at 18:34
0

I have seen this before on a badly designed system where the primary key was a compound and one column of it was updated quite frequently on the day it was created. This lead to index fragmentation by the evening (it was a 24/7 system) hours before index rebuilds were scheduled. At that point SQL stopped using the best query and slowed down dramatically even though the query using the actual PK was still faster. When the indexes had been rebuilt, of course, SQL went back to the sane query plan.

A work around for this was to give the query an index hint. You can do this by:

select …..
from tableA a with(index(pk_tableA)) -- any table index allowed
inner join tableB b on b.Id = a.BId
where etc etc

It didn't feel like a good solution - ideally we would have redesigned the table and the way it was used - but budgets.

Note (as Jonathon reminds me in the comments) The index was rebuild online rather than offline. This needs to be specified in the rebuild command.

The below from MSSqlTips.com

ALTER INDEX [IX_Test] ON [dbo].[Test] REBUILD WITH (ONLINE = ON);

This option is not available if:

  • the index is an XML index
  • the index is a Spatial index
  • the index is on a local temp table
  • the index is clustered and the table contains a LOB database column(s)
  • the index is not clustered and the index itself contains a LOB database column(s)

Also, as Denis Rubashkin mentions, the online option is only in SQL Server enterprise version.

  • 1
    If the index is going into offline for rebuild then using the hint will cause the query to fail hard when that happens. – Jonathan Fite Oct 9 at 13:08
  • I don't know what the details of the rebuild were as I was a dev and not a DBA and didn't have access. It never caused any reported problems, even though the query was run every minute or so by the WinForms front end. Must have rebuilt it online (the table met the specs for it). – simon at rcl Oct 9 at 13:25
  • 3
    This answer is perpetuating a misnomer. To be clear, Index fragmentation has only minimal impact on execution plan behavior. Only when average white space per page approaches egregious levels does this matter. Honestly, you're really just performing a very expensive stats update operation against that table which, if performed instead, would have had the same positive impact on your execution plans. – John Eisbrener Oct 9 at 14:51
  • 1
    Tis solution was suggested to us via escalation by our corporate support contract representative to a very knowledgeable engineer who had worked in the SQL development team. (OK they may all say that but she certainly knew her stuff, including warning of its dangers, and it did work extremely well.) – simon at rcl Oct 9 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.