Generally, the query execution plan's overall cost aligns with the statistical IOPs. However, I've run into a situation where this isn't the case; one plan, which uses no throttling of CPU, shows it's less expensive by 6%, yet the more expensive plan (which uses
OPTION (MAXDOP 1)) has significantly fewer reads and requires less time. I tested the order of the query which came first and last and I continue to obtain the same results (even when running them independently).
The no maxdop option query is 6% faster by query execution plan, but has significantly more reads and takes more seconds regardless of whether it is run first or last over the MAXDOP 1 query.
In a situation where the query execution plan shows that one plan is smaller than the other overall, but the statistics show a lot more reads on the more expensive one and it takes a lot less time, what's the best way to determine which is more effective?
Just some numbers:
Plan one (no MAXDOP): 6% less cost by execution plan
OPTION (MAXDOP 1): Per Stats IO, fewer scans, logical reads,WorkTables involved; less time by 2 seconds overall.