A query plan join can appear even when the query has no join, because SQL Server joins a nonclustered index to the table itself. This post reads the plan of a one table query, removes the join with a covering index, and finds the row count where SQL Server stops using lookups.
Statistics Sample Percent and PERSIST_SAMPLE_PERCENT
The statistics sample percent decides how many rows SQL Server reads when it updates a statistic. A tested demo on 1,000,000 rows shows the default sample of 35.5 percent, a FULLSCAN of 100 percent, and the way PERSIST_SAMPLE_PERCENT keeps 100 percent for later updates, including an automatic one. It also shows that a sampled update can be slower than a full scan.
APPROX_COUNT_DISTINCT With GROUP BY: When Exact Wins
APPROX_COUNT_DISTINCT with GROUP BY returns an estimate for every group. A tested demo on 2,000,000 rows shows a memory grant of 207,616 KB for the exact count against 3,112 KB for the estimate, and a memory grant of 0 for the exact count once an index exists. It also shows the error on small groups, where 4,648 of 100,000 groups were off.
Row Mode Memory Grant Feedback: It Needs Level 150
Row mode memory grant feedback needs compatibility level 150 or higher, while batch mode needs only 140. A tested demo runs a row mode sort and a batch mode sort at levels 140, 150 and 170. The row sort keeps its grant of 11,888 KB at level 140 and drops to 1,536 KB at 150.
Database Scoped Configuration Options in SQL Server
Database scoped configuration options change how one database behaves, without touching the server. This post lists all 41 options on SQL Server 2025, finds the 7 ids that are missing, and changes and restores two settings in a demo database. It also answers the old question about configuration id 5.
Why Query Cost Percentages in a Plan Mislead You
Test why query cost percentages are estimates by comparing two statements with STATISTICS IO, TIME, and query DMV counters.
Memory Grant Feedback Events: Capture Them in T-SQL
Memory grant feedback events list every query whose memory grant keeps changing, and the moment SQL Server gives up on it. This post creates the Extended Events session in T-SQL, watches it live in SSMS and reads it with one query that names the procedure.