5

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?

This question had a bounty worth +150 reputation from Paul White says GoFundMonica that ended 3 hours ago. Grace period ends in 20 hours

This question has not received enough attention.

  • 2
    Could it be possible that the short index was in offline rebuilding state at that time? – Denis Rubashkin Oct 9 at 10:40
  • 2
    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
  • 2
    Actually - there is one strange thing. The UserID index will already have CommentID in it as that is the CI key - and that will be appended to the key columns to make it unique as clearly UserId isn't unique in its own right. So it should just be able to seek into that and get the results without needing a sort at all. Can you script out the full table definition including all indexes? – Martin Smith Nov 12 at 22:58
  • 2
    @SQLRaptor no, you're wrong. See web.archive.org/web/20190308085816/http://sqlblog.com/blogs/… For non unique indexes it is silently added in to the key columns. So the key columns should already be UserId,CommentId which can be seeked into from the end of the range and read in backwards direction to provide the desired order with no sort – Martin Smith Nov 14 at 2:24
  • 3
    @SQLRaptor The seek part doesn't really have any direction. It is down through the levels of the B tree to find the point where the scan starts. Then the scan part goes backwards or forwards to scan from that point It can certainly seek to the end of the range and go backwards. Look at the plan for this CREATE TABLE [dbo].[Comments] ( [CommentID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY, [UserID] int NULL index ix_userid, [Body] nvarchar(max) NOT NULL ) declare @UserID int; SELECT TOP 50 CommentID FROM Comments WHERE UserID=@UserID ORDER BY CommentID DESC i.stack.imgur.com/a2pdi.png – Martin Smith Nov 14 at 23:11
3

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 2 days ago
  • @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 yesterday
2

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

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
  • 1
    You should mention that index online rebuilding is available in Enterprise edition only. – Denis Rubashkin Oct 9 at 14:12
  • 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
-1

Statistics is needed to get better idea.

DBCC SHOW_STATISTICS (Comments,IX_hdComments_UserID) WITH HISTOGRAM;

It seem that statistics is not frequently updated.

Also by looking at both Plan it seem that distribution of data is very skewed.

Updating Statistics of big table is very time taking.

So you should create Filtered Statistics for certain UserID

CREATE STATISTICS userid_Comments ON dbo.Comments (userid)
WHERE userid =1280713
WITH FULLSCAN

Knowing and analyzing actual statistics data, gives better idea on what to write in "WHERE" part of Filtered Statistics.

Another area to notice in your Slow plan is EstimateRowsWithoutRowGoal="73281" EstimatedRowsRead="130251000"

Short Reading / With Example

If the actual data distribution is non-uniform, it may be less than the actual number of rows that have to be processed. Therefore, it could create an inefficient query plan. As a workaround for this issue, you can use the USE HINT (‘DISABLE_OPTIMIZER_ROWGOAL’)

INSERT INTO #tmptbl
SELECT TOP 50 CommentID --this is primary key
FROM Comments WITH(NOLOCK)
WHERE UserID=@UserID
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))
ORDER BY CommentID DESC

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.