• Redgate Monitor
• Database Performance Monitoring

When SQL Server Performance Goes Bad: Implicit Conversions

When you’re developing a database, the pressure is on to get something that works, using an efficient algorithm. When you are getting close to a release candidate, however, there are some programming habits that must be removed from the code, because they can cause unexpected performance problems. The reliance on implicit conversions, the plan_affecting_convert, is part of the technical debt that you must expunge before the release of the changed code. The use of implicit conversion in a query won’t necessarily cause performance problems unless it forces the query optimizer to use a scan where a seek would be faster, and you won’t get rid of all of the code that causes the warning to happen. However, all your production-quality code must ensure that data that is being compared in a join or a filter is of the same datatype. It’s not hard work, just unexciting.

What is an implicit conversion?

SQL is designed to be obliging. Although it is strictly typed, it tolerates a degree of carelessness in the SQL code it consumes. It allows such things as implicit conversion just so long as the database engine can work out what you want from your SQL Query. It doesn’t have to be good code; any reasonable RDBMS will execute it, but it will grumble quietly about it, if you know where to look. It will certainly raise its eyebrows at anything that causes it to have to select an inefficient query plan. There will be a warning in the query plan that you can see in SSMS or in suitable DMV code. Query performance is often affected, and if it is a commonly executed query, then it will degrade the performance of your application.

Implicit conversions generally happen when, in a WHERE or FROM clause filter of a query, you specify a column with a datatype that doesn’t match the datatype of the column in the table. When two items of data with different data types are being compared in SQL, all the values with the lower-precedence datatype must be converted up to the higher precedence type before the comparison can proceed, and the filter applied.

For example, if you had an EmployeeNumber column in your Employees table, stored as a string, and you decided to specify your filter as an integer, this will cause an implicit conversion on the ‘table-side’, meaning the optimizer must scan every value of that column for every row and convert it to an INT, the datatype of the filter predicate. Conversely, if the EmployeeNumber column was an integer, and the predicate supplied a string, it would just require a conversion of the single parameter value.

By specifying the wrong data type, you render the predicate “unusable” to the optimizer (often referred to as non-SARGable, meaning simply that the “search argument” can’t be used). In some cases, this will have negligible impact, but where it means that the optimizer cannot o use an index that would otherwise have allowed an efficient seek operation, it can cause surprising performance problems. This is most likely to happen if you are converting from string types to numeric types.

What is the performance impact?

Of course, it depends on size of the tables, the datatypes involved, but let’s take the old chestnut from Adventureworks where the NationalIDNumber is a string, an NVARCHAR, but our query supplies it as an integer.

Now that seemed to be OK. There was no noticeable pause when it executed, but then it is a very small table. Let’s just put some figures on this though, using my SQL Prompt snippet (How to record T-SQL execution times using a SQL Prompt snippet) to do simple timings. We’ll take the ‘implicit conversion’ query and run it in a very simple test harness alongside a version that supplies the correct NVARCHAR datatypes in the predicate.

Basically, the result, when the SQL was correct, was instantaneous, whereas it waxed slow when we supplied the list of employees as numbers. OK; 3334 microseconds isn’t enough time to eat a sandwich, but this is just a demo: your million-row will be thrashed, guaranteed. Remember that the second query is too fast to be measurable, and you’re comparing the two.

The query plan for the first was …

The warning on the SELECT operator is for the ‘plan affecting convert’, which you’ll also see if you have an Extended Events sessions running (we’ll cover that a little later):

The query plan for the second query shows the simple index seek that one would have expected.

Investigating the problem

The “non-SARGable predicate” is just one of many query-related mistakes that can spell trouble for SQL Server performance. In cases where it forces the optimizer to compile an execution plan containing scans of large clustered indexes, or tables, it degrades performance.

If the optimizer is forced to scan every row in a 500K-row table, just to return small number of them, then it will cause avoidable resource pressure. Affected queries will require significantly more CPU processing time, and the optimizer may choose to use parallel execution, speeding the execution of what should be simple and fast query across multiple cores. This will often cause blocking of other queries. Also, many more pages will need to be read in and out of memory, potentially causing both IO and memory ‘bottleneck’.

To detect whether implicit conversions are part of the problem, SQL Server provides two tools:

• The sys.dm_exec_cached_plans DMV, and other DMVs that provide query plan metadata
• The sqlserver.plan_affecting_convert event in Extended Events

Using the plan cache

If you have good performance-testers, all they must do is find the code that relies on implicit conversions, by running the database through a range of integration tests. You can then look in the plan cache for query plans from the current database where there has been an implicit conversion on the table-side of the query, as demonstrated by Jonathan Kehayias. This provides all the information you need about the offending queries and columns

This DMV query returns the statement from the batch that is causing the conversion, the schema, table name, and the name of the column being converted, as well as the original and converted datatypes. It isn’t a fast query, but it gets to the heart of the problem.

So, all those NVARCHARs had to be converted to INTs!

Extended Events

My preferred way to spot this problem is to run an extended events session that captures the sqlserver.plan_affecting_convert event. The great thing about running these is that those places where an implicit conversion has ruined a good execution plan instantly appear when you run the code.

To prevent any embarrassment on the part of the database developer, it is far better to do this in development, so this is one of the extended event sessions I like to have ready on the development server.

Here is the code that defines the extended events session. it is set to just filter for plan-affecting implicit conversions on AdventureWorks2016. You’ll want to change that, obviously, for your database.

This extended event occurs when a type convert issue affects the plan. It returns an expression value that shows the conversion that can cause inaccurate cardinality estimation, or that has prevented the query optimizer from using a seek-based query plan. If performance is affected, then you’ll need to rewrite the query to make any conversion explicit and to ensure that all your filter predicates are of the right datatype!

Now we can query it for all plan_affecting_convert events, and include the text of the SQL batch that caused the problem

Here’s the result:

Spotting signs of trouble quickly using SQL Monitor

The ‘art’ of SQL Server performance tuning is, as always, is in being able to spot the signs of trouble early, in the waits and queues that start to form in SQL Server, and correlate this with the processes and queries running on the server at that time.

SQL Server’s performance counters and wait statistics will tell you why requests are being forced to wait, and which SQL Server resource (CPU, IO, memory), if any, is currently limiting performance. The query execution details captured over that period, will then allow you to determine the source of the problem.

In the following screen, you can see the general pattern of CPU, IO and memory use, along with the wait profiles, for a SQL Server instance, over a period of time when a couple of “Long running query” alerts were raised (the blue circles on the timeline). Below the graph, you can see the details of our previous “NID” query, for SQL Monitor’s Top Queries list, with associated wait types:

You can view the query plan within the tool, where you’ll see the same type conversion warning on the SELECT operator as we saw earlier, and you can drill into the details using the previous plan cache queries or Extended Events session.

If you use SQL Monitor to keep an eye on your development and test servers, you’ll prevent most or all these problems from reaching your end users.

If you’re investigating a performance issues on a production SQL Server, you’ll be able to determine quickly, whether it’s a problem you can resolved by tuning the query, or if you can’t, then potentially increasing the capacity or speed of the performance-liming resource.

If you suspect implicit conversions are a strong contributing factor to performance problems, you might consider setting up a custom metric, using a query such as the following, which returns a count of the number of cached plans, for queries executed in the last 10 minutes that took more than 0.1 seconds to execute and which contain implicit conversion warnings.

Simply enter the query, and then the instances and databases for which you want to collect this metric. You can collect it for a database, or specific databases, or you could simply remove the filter on db_id, in the last line of the query, and collect it for all databases. You’ll also need to establish the collection frequency (every 5 mins might be a reasonable starting point).

If you see an upward trend or sudden rise in the value of this metric, during periods of server slowdown, you can the following query will list all the queries that contributed to the figure in the custom metric:

Alternatively, you might consider a custom metric based on a querying the output of the previous Extended Events session. I’ve described that process in previous articles, see for example: Monitoring TempDB Contention using Extended Events and SQL Monitor.

Summary

It pays to check warnings from the Query optimizer. The best time to deal with them is when you are tidying up code ready for release, when you have code that has an efficient algorithm and clear purpose. I’d hate to discourage database developers from bouts of wild experimentation and spontaneity, but the race to bring code up to production quality means that all those shortcuts and sketchy routines must be cleaned up. When you are working with development data, a poorly performing section of code may not be obvious, but that’s no excuse. You can see these warnings in the execution plans you see in SSMS, you can see them if you trawl through the cached execution plans via DMVs, and you get reports from an Extended events session. You can’t convince anyone by protesting that you didn’t know. When that hatchet-faced production DBA walks up to your workstation and gives you ‘that look’, you can look in vain for support from any experienced Developer. That sort of stuff mustn’t get out of development.

Tools in this post

Redgate Monitor

Real-time SQL Server and PostgreSQL performance monitoring, with alerts and diagnostics

Find out more