0

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?

Update

Just some numbers:

Plan one (no MAXDOP): 6% less cost by execution plan

Plan two OPTION (MAXDOP 1): Per Stats IO, fewer scans, logical reads,WorkTables involved; less time by 2 seconds overall.

Improve this question
2
  • 2
    Plan cost is just a number. I would rely on statistics IO and time for any optimization that I do.
    – Kin Shah
    Commented Jun 30, 2016 at 13:11
  • @Kin Thanks for the feedback; that's generally what I do since that measures what the numerical hits are. Generally, these are similar (or very close like 50-50).
    – WhyAShortage
    Commented Jun 30, 2016 at 13:14

1 Answer 1

Reset to default
1

SQL server uses a cost based optimization model. The exact cost will depend on the row size and distribution info which will be context-sensitive and dynamic. Even in actual execution plans, the cost figure is based on estimates.

From : An Introduction to Cost Estimation

In recent versions of SQL Server this cost should no longer be interpreted as seconds, milliseconds, or any other unit.

Back to your question :

what's the best way to determine which is more effective?

I used to rely on statistics time and IO. Any change I make e.g. create index, rewrite the query, use query hint, update / create statistics etc. I would measure the CPU time, reads and writes.

Since I purchased SQL Sentry's Plan Explorer PRO, I just use it to compare the before and after plans.

Also, you can get Multiple Plans for an "Identical" Query - check the SET options, any cAsE and whitespace or schema references as well.

Great references :

Improve this answer
1
  • So without the tool, the IOPS are more accurate. With these queries the only difference is the MAXDOP 1. Good info @Kin.
    – WhyAShortage
    Commented Jun 30, 2016 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.