question

sakuraime avatar image
0 Votes"
sakuraime asked TomPhillips-1744 answered

TSQL predicate using inequality

I have the following query
select id from [dbo].[Posts] where id > 20797871 ( this is a stackoverflow database )

102152-results.jpg


102153-estimation.jpg





may I know why the estimation is 21499 .

The database is a Azure sql database with CL 150 .

sql-server-general
results.jpg (92.3 KiB)
estimation.jpg (26.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered sakuraime commented

This happens when statistics are outdated. You last updated statistics on March.

Please execute the following 2 statements.

 ALTER INDEX ALL ON  [Posts] REBUILD
 GO
 UPDATE STATISTICS [Posts]  WITH FULLSCAN, COLUMNS
 GO

Now execute the SELECT statement again, and include the actual execution time. All estimations should be ok.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

no... I didn't touch any records .

strange , after flush the cache plan . it reflect a more accurate estimate stats.

but do you know the calculation of estimated row when using inequality predicate ?

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.