Table statistic vs estimated rows query plan
You are checking the statistics for changes in the wrong place in your code. They won't be auto updated immediately after the delete. This will happen when a query is executed or compiled that needs them (and in fact your code contains no query that needs them anyway apart from the delete itself).

But SQL Server does maintain cardinality information for the partitions of the table/indexes as a whole after every insert/delete etc. 

You can see this with the below (only returns 1 row as the table is not partitioned and has no indexes)

SELECT row_count
FROM   sys.dm_db_partition_stats
WHERE  object_id = object_id('dbo.TestPerf'); 


This shows it that the current row count is 750. The query 

SELECT *
FROM   dbo.TestPerf


Doesn't benefit from column statistics. It can just use that directly and even if statistics are disabled will be accurate.

A query with WHERE  ID &gt; 0 would use the statistics and return the whole table. As it happens the statistics don't get updated in your case anyway when such a query is executed as you haven't hit the threshold for modifications for an auto update to occur. But the estimates are still accurate.

The statistics records that there were 1,000 rows at sampling time.



So it would be simple to just scale the estimates down to adjust for the new table cardinality by multiplying by 0.75.

The scaling down works fine for WHERE Id&gt;0 as 1,000 * 0.75 gives the correct result but if you try querying the 250 rows that you just deleted this will not show correct estimates. 



The statistics indicate that it will return 250 rows, multiplying that by 0.75 gives 187.5.