1

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

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".

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.

I've been scratching my head for days now... Any ideas?

PS. Tried removing the temp table bit - no success.

UPDATE: The table definition is very simple. I already mentioned in the code that CommentID is our PK and here's the full definition

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
)

UPDATE 2:

Pasting my execution plans here: slow fast

  • 2
    Could it be possible that the short index was in offline rebuilding state at that time? – Denis Rubashkin Oct 9 at 10:40
  • @DenisRubashkin no idea, will investigate... This table is the most freqeuntly written (hundreds inserts per second) - can this be connected? – jitbit Oct 9 at 12:07
  • Can you check the UserID both plan was compiled with? Do a count on the value, and do you see a higher value for the UserID when you get clustered index scan? – SqlWorldWide Oct 9 at 12:53
  • @JohnEisbrener thanks for the link, checking it out. Also added table definition to the question – jitbit Oct 9 at 14:51
  • 1
    I deleted my earlier answer as I'm not certain either are your issue based on the estimated plans posted. Is there any way you can manually run that query and post the actual execution plan? – John Eisbrener Oct 10 at 15:16
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
  • depends on the specific maintenance plans in place and what is chosen for the defragmentation options. It's definitely a workable answer, but care needs to be taken with using index hints. – Jonathan Fite Oct 9 at 13:28
  • 1
    You should mention that index online rebuilding is available in Enterprise edition only. – Denis Rubashkin Oct 9 at 14:12
  • 1
    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

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.