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.

  • 2
    Plan cost is just a number. I would rely on statistics IO and time for any optimization that I do. – Kin Shah Jun 30 '16 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 Jun 30 '16 at 13:14
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 :

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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