jchang6
commented
2 years ago the origin of the reason for the 6 initial rows inserted/modified may date to a quirk of SQL version 7. Back then, the plan IO cost for a single page table scan was 0.0375 vs initial index seek or key lookup io cost of 0.00625 (1/160) on systems with up to 1GB memory (vs post 2005 version, all initial IO is 0.003125). The implication was that 6 rows was the first point in which a single page table scan is less expensive than an index seek + key lookup. This model was likely based on an error in calibration, in that small table scans were relatively expensive per page, while larger scans were dramatically less expensive per page. Hence a high cost for the initial page was assigned. The real cause was that a small scan does row lock, while a large scan defaults to table scan. Now we have an almost equally stupid model in which the base index seek, key lookup, and first page of a scan all have IO cost 0.03125 (1/320). which also not really a reflection of true cost. The reason for the 500 row recompile may be because that is around where a plan transition from a Loop Join (seek on join condition) could cross over to a Hash join, scanning the inner/outer source. A related factor: SQL Server statistics employ sample of all rows in a random selection of pages (1st and last page always included?)
KEEP PLAN Demystified - StraightforwardSQL
Have you ever wondered what the purpose of the KEEP PLAN hint is? The documentation doesn't help this time. Look no further than here to find out.
https://straightforwardsql.com/posts/keep-plan-demystified/