SQL Server – When Execution Plan Cost Differs From IOPs and Time

sql server

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.

Best Answer

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 :

Related Solutions

SQL Server – Using Parallel Plan for Cost Less Than 5

This can be confusing when you're first looking at parallel plans. The thing to keep in mind is that the cost based optimizer picks what it estimates will be the cheaper query to execute. It's not always right, but hey, it's not rocket science.

As a simple example, here are two count queries. One is allowed to go parallel, the other is forced to run serially.

SELECT COUNT(*)
FROM dbo.Posts AS p;

SELECT COUNT(*)
FROM dbo.Posts AS p
OPTION(MAXDOP 1);

Their query plans have the information we need:

The serial plan costs 121 query bucks, and the parallel plan costs 69.

SQL Server Optimization – Pre-staging Data Causes Execution Plan Cost to Skyrocket

Costs are based on estimates, even in "actual plans". You cannot compare two query plans side by side and conclude that one of them will require more CPU to execute based on operator or total plan costs alone. I can create a query with a cost in the millions that executes in one second. I can also create a query with a tiny cost that will effectively take forever to execute. For your case, the first query has a cost of only 22 optimizer units because of a poor cardinality estimate after the hash join:

The operators in red execute millions of times, but the query optimizer expects them to only execute a few thousand times. Costs, which are based on estimates, won't reflect that work. The operator in blue is a table spool for which the cardinality estimator expects to insert a single row. It instead inserts a few million. As a result, the operators in black (along with a few others not shown) are inefficient and spill to tempdb.

With the other plan, you put a significant number of rows into tempdb and as a result the cardinality estimate is more reasonable, though it's still not ideal:

The query optimizer expects to need to process many more rows and as a result the query plan has a higher cost. As a very general rule of thumb you may see improved performance with improved estimates, but it doesn't always work out how you'd like. Looking at the plan with a temp table I see a few areas of possible improvement:

Load the full CTE from the original query into the temp table. A query with multiple distinct aggregates can be tough to optimize. Sometimes you'll get a query plan where all of the data is loaded into a spool (into tempdb) and some of the aggregates are applied separately to the spool. All of that work is always done in a serial zone in my experience. If you eliminate all of the joins in the query I believe that you won't get that optimization. The aggregates will just be applied to the temp table. That will save you the work of writing out nearly the same data to tempdb and the entire plan should be eligible for parallelism.
Define the temp table as a heap and write to it with TABLOCK. It looks like right now you have a clustered index which means you aren't eligible for parallel insert.
Consider making the query eligible for batch mode by using one of these tricks. Batch mode aggregates can be significantly more efficient with multiple distinct aggregates.

I would expect some combination of those steps to significantly improve runtime. Please note that I did a quick analysis partially because anonymized plans are difficult to interpret.

Best Answer

Related Solutions

SQL Server – Using Parallel Plan for Cost Less Than 5

SQL Server Optimization – Pre-staging Data Causes Execution Plan Cost to Skyrocket

Related Question