Azure SQL query performance degrades significantly when a WHERE clause returns a large fraction of rows

Question

I am trying to get to the bottom of the neck for a performance bottle in the web application that I am developing. I was able to identify the SQL query that is causing the problem, but I'm not sure how to resolve it. Basic request:

SELECT *
FROM Table
WHERE ColumnA = 0
ORDER BY AnotherColumn
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

Column A is of type BIT, NULL, and has no default value. Currently, each row (about 290,000 in the table) has a meaning 0. Currently, the request takes about 1 minute and 50 seconds.

What I consider to be odd is that by changing a small fraction of the ColumnA values ​​in the database to 1, performance is significantly increased.

Just by running:

UPDATE Table SET ColumnA = 1 WHERE ID % 100 = 0

1% , 7 - 90% .

, , , . WHERE ~ 7 , , .

AzureSQL EFCore, SSMS, .

Answer 1

. 1 , , . , , , , , .

, WHERE, ORDER, ON .., , . ? SQL Server , , .

A. A ( ID % 100), , . SQL Server " , A 0"? SQL Server , , INDEX, , , , .

, INDEX. , , . , ( ). SQL Server , As 0 (Index); , . A .

, , "" . , , . , , , , SLOW DOWN , INSERT. , , . -, ( SELECT). UPDATE , , .

, ; . , INDEX . , , SO.

Answer 2

(ColumnA, AnotherColumn) . 1 . , . - , , .