3

This is an abstract question, at least to begin with. But I am looking for theories on why I am experiencing what I am experiencing.

I have a table with a number of columns, one of which is a Int that allows null. Lets refer to that as NullableIntCol.

We have a few non-clustered indexes on the table, some which includes the column here.

We are then Querying the table in various ways, some where we do a WHERE NullableIntCol is null and when we do that we are experiencing that the queries may take 5-10 seconds to execute. Fair, we need to optimize something.

In our test environment we have been able to replicate the issue. Although here we are talking 1-3 seconds. But the exact same pattern.

Datawise here the table has about 600.000 rows, ~350MB worth of data.

After we have played around with various combination of indexes etc. what I discovered was that if I run the following on the table:

BEGIN TRANSACTION;
UPDATE [dbo].[MyTable]
   SET [NullableIntCol] = -1
 WHERE [NullableIntCol] is null;
UPDATE [dbo].[MyTable]
   SET [NullableIntCol] = null
   WHERE [NullableIntCol] = -1;
COMMIT TRANSACTION; 

Suddenly query performance is way way better. (50ms-200ms in our test env)

This is the Query plan prior to executing the above.

enter image description here https://www.brentozar.com/pastetheplan/?id=H1VCvT9uh

Estimated:

enter image description here enter image description here

Executed:

enter image description here enter image description here


And this is the changed query plan after.

enter image description here https://www.brentozar.com/pastetheplan/?id=HJXMu69_2

Estimated:

enter image description here

Executed:

enter image description here

Which is clearly a change. But I still lack the "Why".

Further.

I can then restore the database to the state it was before and again replicate the issue.

Initially I was thinking that it could perhaps be that the above almost triggered a full rebuild of the indexes, however rebuilding the indexes does not seem to have much effect if any at all.

So right now, I am out of theories on why I may see such a behavior.

Update 2023-06-29


After running UPDATE STATISTICS [Scehma1].[Object1]; post the query above, it seems to go back to the bad query plan. Executing the UPDATE FLIP -1 <==> null where I see the performance improvement, it seems to namely update some statistics for that specific column, after running UPDATE STATISTICS it updates all of the statistics which seems to bring us back to the original Query plan.

I have been able to reproduce the two query plans by messing around with the Index1. And found the following.

The plan prior to any change: ~2 sec in dev env. https://www.brentozar.com/pastetheplan/?id=BJoclpcun Index1 = Index1 bellow Index2 = PK Index

If I disable the index used above (Index1 bellow) I get the following plan: https://www.brentozar.com/pastetheplan/?id=BkrTzp5d2 Index1 = Index2 bellow Index2 = PK Index

Finally if I change the index used initially (Index1 bellow) to: CREATE NONCLUSTERED INDEX [Index1] ON [Schema1].[Object1] ( [Column4] ASC, [Column9] ASC ) So switching the order of the columns. I then get: https://www.brentozar.com/pastetheplan/?id=r16QNpcd3

Now that I am actually making changes to the index, it makes quite allot more sense that I get changed plans, the question remains though, why it seems to pick such an sub-optimal plan in the beginning rather than just doing a table scan which is clearly faster. I Would get that when I begin to add paging and ordering on top it would


Now it's rather clear that these indexes has become a mess over time I think, we identified that rather quickly I think, but the strange thing to me was that the execution of that update statement changed so much. Which is perhaps answered now

Table Schemas


Tried to align it to the output from Sentry Plan Explorer, but it's probably not 100%. And it's not alligned to the old plans as I did not see the need to Anonomize all but Sentry Plan just takes it all.

CREATE TABLE [Schema1].[Object1](
    [Column1] [int] IDENTITY(1,1) NOT NULL,
    [Column2] [datetime2](7) NOT NULL,
    [Column3] [varchar](256) NOT NULL,
    [Column4] [int] NULL,
    [Column5] [varchar](256) NULL,
    [Column6] [varchar](256) NULL,
    [Column7] [varchar](max) NOT NULL,
    [Column8] [datetime2](7) NOT NULL,
    [Column9] [int] NOT NULL,
    [Column10] [varchar](256) NOT NULL,
    [Column11] [datetime2](7) NOT NULL,
    [Column12] [nvarchar](max) NULL,
    [Column13] [nvarchar](max) NULL,
    [Column14] [bit] NOT NULL,
    [Column15] [bit] NOT NULL,
 CONSTRAINT [PK_Object1] PRIMARY KEY CLUSTERED ( [Column1] ASC ) 
 WITH (
   PAD_INDEX = OFF, 
   STATISTICS_NORECOMPUTE = OFF, 
   IGNORE_DUP_KEY = OFF, 
   ALLOW_ROW_LOCKS = ON, 
   ALLOW_PAGE_LOCKS = ON, 
   OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
   ) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

ALTER TABLE [Schema1].[Object1] ADD  DEFAULT ((0)) FOR [Column14]
ALTER TABLE [Schema1].[Object1] ADD  DEFAULT ((0)) FOR [Column15]

ALTER TABLE [Schema1].[Object1]  WITH CHECK ADD  CONSTRAINT [FK_Column9]
  FOREIGN KEY([Column9])
  REFERENCES [Schema1].[Object2] ([Column1])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [Schema1].[Object1] CHECK CONSTRAINT [FK_Column9]

--
CREATE NONCLUSTERED INDEX [Index0] ON [Schema1].[Object1] ( [Submitted] ASC )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

-- This Index is the one that goes in and out of our plans, whenever it's used it seems performance is tanked.
CREATE NONCLUSTERED INDEX [Index1] ON [Schema1].[Object1] ( [Column9] ASC, [Column4] ASC )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

-- This has clearly not been updated as it should since all selects always includes all columns, so I guess that if it should make any sense to include what is currently there, 13,14 and 15 should be added as well
-- Not sure it has anything to do with the current problem though.
CREATE NONCLUSTERED INDEX [Index2] ON [Schema1].[Object1] ( [Column9] ASC )
  INCLUDE([Column1],[Column2],[Column3],[Column4],[Column5],[Column6],[Column7],[Column8],[Column10],[Column11],[Column12]) 
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [Index3] ON [Schema1].[Object1] ( [Column8] ASC )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]



18
  • 1
    You need to provide the actual execution plans for both slow and fast runs. Maybe it is tripping the column modification threshold for stats to be updated or you are just benefiting from the above bringing all the data into buffer cache. But a rebuild of indexes would also do those. Your query doesn't necessarily reverse back to the initial state though. Does the starting position definitely have no rows where NullableIntCol is already -1? Jun 28, 2023 at 10:46
  • 1
    Though actually unless NullableIntCol is the leading column in the index then rebuilding the index won't update the stats there. So potentially an explanation. What are the estimated and actual rows for WHERE NullableIntCol is null in the bad plan and the good plan? Jun 28, 2023 at 10:57
  • Should I include more than just the "picture" of the estimated query plan for now? It clearly changes after the update, but still searching for why so we can implement an improvement that remains - Running the query changes the same amount of rows in both passes, so there is no -1 values present already. It's approximately around 90% of all the rows that contain the value "NULL"
    – Jens
    Jun 28, 2023 at 11:51
  • And NullableIntCol is not the leading column in any of the indexes. Instead it's in an index with a foreign key and those two are always queried together, so it will always be "WHERE MyFk = {id} AND NullableIntCol is null" and sometimes more filteres are added as well.
    – Jens
    Jun 28, 2023 at 11:54
  • 2
    Please share the execution plans via brentozar.com/pastetheplan. If you want you can sanitize it using SentryOne Plan Explorer. Please also show the table and index definitions (again you can sanitize the names if you want). At a guess, removing the key lookup (by rearranging the index) would probably remove the chance of an index scan being picked. But hard to say without the right info. Jun 28, 2023 at 12:41

2 Answers 2

2

The difference between the two plans is that in the first (slow) one, Sql Server expects that only 20 rows will satisfy the WHERE clause, and it resolves to use an index seek followed by lookups in the main table. As it turns out, at least 1000 rows satisfy the clause and this plan comes out to be very inefficient because of the 1000 lookups.

In the second plan, Sql Server correctly estimates that at least 1000 rows will have to be retrieved, so it decides to do a full table scan (and it finds 1000 qualifying rows after reading just 2328 rows from the table).

Sql Server has a feature that auto updates the statistics of a table when a high percentage of the rows are modified. Since, as you say, It's approximately around 90% of all the rows that contain the value "NULL", the update of all those rows has triggered the auto update of the statistics of that table, allowing Sql server to choose a better plan.

You can test this by issuing an UPDATE STATISTICS command instead of your UPDATE block:

UPDATE STATISTICS [dbo].[MyTable];

EDIT: It's also possible that in the first query there was no statistics available, and Sql server created them after your updates. If the AUTO_CREATE_STATISTICS option in on, the Query Optimizer creates statistics on individual columns used in a predicate, if these statistics are not already available. So the updates could have triggered the creation of statistics on [NullableIntCol] and the following query will find and use them.

In this case, you could obtain the same result with:

CREATE STATISTICS statistics_name
ON [dbo].[MyTable] (NullableIntCol)
WITH FULLSCAN;

Update after your posting of the execution plans.

Please note that the original queries you posted had a TOP 1000 limit, which is absent in the query plans. A limit on the total number of rows to be retrieved has an high impact on the query plan the optimizer will choose so you are comparing apples to oranges.

Also a TOP 1000 has a meaning only if you specify an ORDER BY, which also have a big impact on the optimizer.

Nevertheless, the plans clearly show that 251959 rows satisfy your WHERE condition, out of a total of 569158. That's 44% of total rows. No index lookup can be of any benefit if you have to retrieve such a large part of the table, so the best plan is always a table scan. You could benefit from a covering index (which included all the columns you are selecting) but in your example you are selecting all columns of the table and such index would be a duplication of the whole table.

In such scenario, SQL server should never pick an index seek, unless it wrongly estimates that the percentage of rows satisfying the WHERE condition is much smaller. Having appropriate and updated statistics on Column4 and Column9 should avoid this.

But, if you are applying an ORDER BY and a TOP 1000 limit, then an index on Column4, Column9 and any further column specified in the ORDER BY could speed up the things by allowing to read just the first 1000 rows and then stop. This could be even faster if the index inlcuded all the columns needed in the select.

Also note that if you need even a single column not included in the index, sql server will have to do a lookup on the table for every row. So including 12 columns is useless if you select 15 columns and it just makes the index bigger and slower. Either include all the needed ones or don't include anything.

3
  • I really hoped you nailed it, it sounded so plausible that I had to log in from home to test it, unfortunately it did not quite go that way. Instead now it always stich to the "bad" plan. - However I think we have found out that the index it goes to is not good for the queries, I will post more details and plans tomorrow because an explanation would be nice.
    – Jens
    Jun 28, 2023 at 20:10
  • Actually, there may be more to it, perhaps you are correct, it's just that the Query I execute seems to only update some of the statistics, I did not check all of them, but the first one that was changed was one for the NullableIntCol, but if I execute the UPDATE STATISTICS it updates all of the objects and then things goes back to the original plan. Could that make sense?
    – Jens
    Jun 29, 2023 at 9:04
  • @Jens I added some more considerations after your updates.
    – Andrea B.
    Jun 29, 2023 at 10:43
2

Looks like it's mostly caused by the fact that both plans are essentially bad plans. One does a full scan, the other does many lookups. You are asking the server to choose between a rock and a hard place.

Change one of your indexes to support the query properly

CREATE NONCLUSTERED INDEX [Index2] ON [Schema1].[Object1]
    ( Column9, Column4 )
INCLUDE ([Column1],[Column2],[Column3],[Column5],[Column6],[Column7],[Column8],[Column10],[Column11],[Column12])
WITH (DROP_EXISTING = ON);

The two key columns can be in either order.

Changing your clustered index to be on these two columns might make sense, depending on what other queries you have. The primary key does not need to be the clustering key.

Index1 looks mostly useless, unless you are only querying on those two columns or you are doing a range lookup on whichever column is first in Index2, as Index2 now covers the same key columns. I suggest you drop it.

7
  • There is no doubt that the Indexes is a mess, it's also clear that updating the ones we specifically defined was forgotten as the table was changed with more resent features. And so the plan is to basically kill all indexes and build new ones. Having focus on and Column9, Column4 makes sense as they will be part of almost all queries - the only exception is export of data where Column4 is left out of the filter.
    – Jens
    Jun 29, 2023 at 11:06
  • When filtering on these two, another thing that is characteristic is that Column4 will ALWAYS be filtered against "is null", this it back to the export case where we are only ever interested in getting rows where this is not null for export, a feature rarely used. Column9 which is a FK will always be filtered for a single value, never a range, so it will always be = 1, = 2 = 42 etc...
    – Jens
    Jun 29, 2023 at 11:10
  • Some of the rest of the columns are also part of the filtering process, but here this is more user driven, e.g. if they wish to filter for time ranges, or text values. So they are not always included.
    – Jens
    Jun 29, 2023 at 11:12
  • Then perhaps you need a filtered index WHERE (Column4 IS NULL) Jun 29, 2023 at 11:20
  • That could be, I am also considering pulling that data out of the table, simply having a secondary table that stores that data to simplify things from a Query perspective, this does mean we have to move data instead of just updating that column for a row. But the nature of such an update is that it goes from null to a value only once in it´s lifetime, and then it never changes again.
    – Jens
    Jun 29, 2023 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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