My statistics are up to date, histogram looks good, but still getting terrible execution plans. Estimated vs actual is always way off.

Joshua Guttman 2017-03-22 21:17:40

I don't necessarily agree with the design but it should be better than this. I do very aggressive statistics maintenance every night and every value is represented in the histogram with no range rows. They cluster on an incrementing bigint, but the primary key is the partitioning key and a guid. I hate that and can't change it. It keeps the clustered indexes unfragmented but the non-clustered indexes fragment every day. I fix them every night. Most of the joins are using non-clustered index seeks and one clustered index seeks but the row estimates and spilling are bad. It's a system-wide thing and I don't know why other than the design I described above and can't change. Any help would be appreciated. I'm tempted to stalk Kimberly Tripp to get some help 🙂

/storage/attachments/1890-plan2.pesession

Vlady Oselsky 2017-03-23 19:08:14

Run your query in Plan Explorer and upload new plan. When running query in Plan Explorer you get additional information that is not collected by SSMS and then opening plan through plug in.

Joshua Guttman 2017-03-23 19:15:11

Running my query in plan explorer is how I got here.

Joshua Guttman 2017-03-23 19:16:29

Oh, you "Run" the query. Got ya.

Joshua Guttman 2017-03-23 19:30:57

Ok, that was much nicer information but I still couldn't find anything wrong with the stats. I uploaded the new execution plan.

March 22, 2017

No Comments