How to Get Better Estimates for Modification Queries

When you’re doing DUI operations against tables with millions of rows, you have to be really careful about SQL Server’s estimates. Ideally, you want your delete/update/insert (what – what’d you think I meant?) queries to be as simple as possible – even a simple join can cause SQL Server to do wildly, wildly incorrect estimates, which affects memory grants, parallelism, wide vs narrow plans, and more.

I’ll use the 50GB StackOverflow2013 database, where the Posts table has 17M rows and is 37GB in size. I’m going to put all of the Ids in a duplicate table called PostsStaging: