1

We have a table that has roughly 5.5 million rows. We use Entity Framework and run a query on this table and get our row of interest based on 2 columns. One FK column that has an index (Type) and another column (ObjectId) that is of type NVARCHAR(MAX) that cannot have an index.

We experienced timeouts in our system. As a proof of concept I was going to change the ObjectId column to a fixed length so I could add an index.

I execute an alter statement . . .

ALTER TABLE Audit.Changes 
    ALTER COLUMN ObjectId VARCHAR(50) NULL

After 2 seconds or 2 minutes, I cancel the statement, it has NOT fully executed.

I re-run my test and the query starts to run without any timeout issue.

NOTE: I have tried to run UPDATE STATISTICS without an improvement.

NOTE: If I restore a backup, problem comes back, and I can consistently fix it by running the alter and cancelling it.

My question: what is SQL Server invalidating under the hood or what is it doing where triggering a change on the column or cancelling an alteration to the table would cause the timeout to go away? Is there a direct command I can use to instead of running the statement and a cancel.

2
  • 1
    Could be invalidating the query plans referencing this table. You can track the plans over time and force a good one if necessary using the Query Store. docs.microsoft.com/en-us/sql/relational-databases/performance/… Dec 3 '21 at 22:35
  • I'd suggest you do a update statistics with full scan, as your current scenario (i.e. trying to alter a column and then cancel the alter action) will very likely 1. cause the next run query plan recompiled and/or 2. cause the statistics on this column totally refreshed. That's why I suggest you to do a stats update with full scan, which should give you the same benefit.
    – jyao
    Dec 3 '21 at 23:27
0

I'd guess it has read a bunch of pages into the buffer pool and is now running with a "hot" cache. Do your cancelled query thing, test performance, then

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

And test again. Fast or slow?

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.