1

Recently the application I'm working on encountered performance issue on a particular table. When I check the queries executing on this table, I noticed a warning about CONVERT_IMPLICIT on the predicate of the query. Apparently the data type of the column is varchar, but the query is passing in nvarchar, hence the warning.

Out of curiosity, I try to see how much difference would passing the correct data type improves query performance by checking the execution plan. I got the following result:

I don't get it

To my surprise, when I'm using the matching data type varchar, the query cost is double compare to using nvarchar, even though it seems like the second query is not using the most efficient execution plan.

So is the relative query cost to be trusted, or is there something I missed?

Here's the execution plan.

If even the actual execution plan is not reliable, what is a better way to compare the execution cost of 2 queries?

Improve this question
3
  • 1
    Query costs are best-guess estimates, so sometimes not to be relied upon entirely (or occasionally at all). It would help us discuss this specific example if you were to include a bit more detail: the SQL Server version being used here, and the full query plan (brentozar.com/pastetheplan is a useful way to post that without cluttering up the question) so we can see the exact warning and other details.
    – David Spillett
    Jan 22, 2021 at 11:43
  • @DavidSpillett Thanks. I have included the execution plan.
    – Chan MT
    Jan 22, 2021 at 11:58
  • It looks like you are running SQL 2012 SP3. I cannot repo with SQL 2012 SP4, the supported patch level. On a side note, it seems you are using a Windows collation so a seek is used despite the mismatched types. The actual cost will be egregious with a legacy SQL collation, requiring a scan.
    – Dan Guzman
    Jan 22, 2021 at 12:09

2 Answers 2

Reset to default
2

This is due to a Cardinality Estimate issue. Even though your first execution plan (when filtering on a VARCHAR) estimates returning more rows (when you hover over the clustered index seek operation), it's the correct estimate that matches your actual number of rows returned.

The second execution plan (when filtering on NVARCHAR) under-estimates the number of rows it believes will be returned which results in a less optimal execution plan, but even worse, it'll request less server resources (memory and CPU) than it actually needs to be allocated to process the query. This will cause the query to run slower since effectively the actual number of rows being returned is the same in both cases (same amount of data) but the query optimizer estimates only half the number of rows are being returned than what actually is.

The under-estimation of the number of rows being returned is due to the IMPLICIT_CONVERSION Warning which you can see if you hover over the SELECT operator of the second execution plan. This generally happens when the data types in your JOIN, WHERE, or HAVING clauses are of different data types than the fields they're being compared against, forcing the SQL engine to perform a cast operation. This messes with the statistics the server stores about the cardinality of the fields being compared to in the predicates, and ultimately results in a cardinality estimate issue such as the one you're currently facing.

Improve this answer
4
  • 1
    And notice that this is a special case saved from a column-side conversion to the parameter type by the <ScalarOperator ScalarString="GetRangeThroughConvert([@1],[@1],(62))">, which translates the nvarchar predicate to a varchar predicate before the scan.
    – David Browne - Microsoft
    Jan 23, 2021 at 14:55
  • Thanks for the explanation. I was expecting Actual Execution Plan to be also returning Actual Query Cost, but apparently it is still just estimation.
    – Chan MT
    Jan 26, 2021 at 11:59
  • @ChanMT No problem! Query Cost and Cardinality Estimate are two different things. Query Cost is an arbitrary relative number the SQL Engine assigns each operation and uses to decide how to build the execution plan. In the Actual Execution Plan this number is the actual cost, but again is just relative and arbitrary to try to interpret. The Cardinality Estimate (which is your issue here) is always an estimate (though sometimes is exactly correct of an estimate) because it's a mathematical calculation based on statistics of the Table data that slowly lose accuracy as...
    – J.D.
    Jan 26, 2021 at 12:24
  • ...data changes in your Table until the statistics are recalculated (which routinely happens in the server, or triggered other ways such as a manual recalculation). But it's generally ok for the Cardinality Estimate to not be exactly correct (hence the name - estimate) as close enough is good enough for the SQL Engine to make the correct decisions. It's not until the Cardinality Estimate is off by multiples or magnitudes that you normally see issues (such as in your case where it's off by a factor of 2). The Cardinality Estimate is used to determine which index operations to use.
    – J.D.
    Jan 26, 2021 at 12:27
1

I think the difference in query cost is driven by SQL Server estimating a different number of rows returned by each clustered index seek. Hover over the clustered index seek operator and look at the Estimated Number of Rows metric.

  • In the first query where the data types match, it estimates 8,299 rows.
  • In the second query where the data types don't match, it estimates 4,150.5 rows.

The more rows returned in the first query is predicting higher I/O and CPU costs for having to process more rows. In the end, the actual number of rows returned is the same, but plan costs are estimated before the query is actually run. This seems to be a quirk of the query optimizer when faced with implicit type conversions.

I would still go ahead and make the types match to avoid the implicit type conversion if you can.

Improve this answer

Your Answer

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

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