- Hosts: Steve Stedman / George Stedman
- Topic: Implicit Conversions
- Recording Date: April 2, 2026
- Sample Files: https://stedmansolutions.com/wp-content/uploads/2026/04/Sample-SQL-Files.zip
Implicit Converts The Silent Performance Killer in SQL Server Stedman SQL Podcast Sn 3 Ep 7
Steve Stedman and George discuss implicit conversions in SQL Server, highlighting their impact on performance. Steve explains that implicit conversions occur when data types don’t match, forcing SQL Server to convert columns, which can slow queries significantly. They demonstrate this with examples, showing how a simple cast can reduce query times from 23,700 reads to 4 reads. They also discuss using extended events and DMVs to detect implicit conversions and provide solutions like creating views with clustered indices. The episode emphasizes the importance of choosing the right data types from the start to avoid performance issues.
Podcast Transcript
Steve Stedman 00:16
So in the last two weeks, I’ve worked on so many queries, so much performance tuning, and I worked on one query that was able to go from over an hour of runtime to four seconds just by fixing one implicit convert. Now this is one thing that’s probably costing you more CPU and slower queries than you realize, and it’s hiding in plain sight. We’ll get to that in a minute. Welcome to the Stedman SQL podcast. This is season three, Episode Seven, and I’m your host, Steve Stedman, and today I’m joined by one of our team members, George. Hello. Welcome George. And we have Shannon Lindsay. Yeah. We have Shannon Lindsay in the control booth keeping the podcast on track. Yeah. This is our seventh episode of season three or our 47th episode since we started the podcast two and a half years ago. Thanks everyone for joining. If you’ve missed out on these previous episodes, please take a look at some of the most popular episodes from season one and season two.
First off, we had our indexing episode, which was one of the most popular, and you can get that at stedman.us/episode 2.4. Another popular one was the building your career episode a short URL for that is stedman.us/episode Two. Dot 22 our third most popular episode was join types, and you can get that at stedman.us/episode one. Dot 12. Hey, thanks for joining us, George. What is this week’s topic
George Stedman 01:47
Implicit conversions, called this the silent performance killer in SQL Server.
Steve Stedman 01:50
And we’re going to look at how you can understand them and how you can work to eliminate those implicit conversions. But before we jump into why that’s important, do you want to be a guest on our show? Are you interested in being a guest on the Stedman SQL podcast? If you are just reach out to us and we can talk about what topics, things you want to cover, or talk about anything relating to SQL server, database health monitor or about our company, you can reach out to us at HTTPS://Stedmansolutions.com/guests or go to the podcast page and click on the guest link, fill out the form and find out if you’re going to be a guest on one of our future episodes.
All right. So on to talking about implicit conversions. Why does this episode matter? Well, I look at implicit conversions as like a hidden tax, or it’s one of those things that’s like the one of the top three performance anti patterns that we come across in SQL Server. It’s one of those things that you may not realize what’s going on, but it might be chewing up a whole lot of CPU. So by the end of this episode, we hope that you’ll walk away with how to spot implicit conversions in seconds, how to fix many of them permanently. And you’re also going to get a handful of real scripts that you can run today. First off, all the demo scripts, as well as some other scripts to go and find implicit conversions happening on your own system. Those will be available for download on the podcast page at Stedmansolutions.com so if you’re viewing this on YouTube or Spotify, you’ll need to jump over to Stedmansolutions.com to download those. All right, George, on to the episode.
George Stedman 03:35
All right. So what is an implicit conversion?
Steve Stedman 03:39
That’s a great question. Since we named the podcast that it’d be great to explain what it is. What it is to begin with, wouldn’t it? So it’s one of those things that where data types don’t match in SQL Server, and you’re trying to do something that forces, like an entire column to be converted from one data type to another in order to compare it. So I don’t know, maybe an analogy to this would be like mixing oil and water. They just don’t work well together. In SQL Server, there are data type precedence rules, which there’s a chart that we have here. Let’s just add it. It’s Microsoft’s list. So with data types in SQL Server, there’s an order of precedence from the highest to the lowest, and this chart lists the highest would be user, divine data, JSON, SQL variant, XML, things like that, all the way down to the very lowest, and the precedence being char and bar binary and binary and things like that. So what this means is that when you’re comparing two data types that are not the same or not comparable, that one of them has to be converted to the other, and generally it’s going to be converted to the one with the higher precedence in the list. An example on that would be if you were had two variables that you were comparing. And one of them was of type VARCHAR, and the other one was of type N varchar. Well, if we look over on the right side, we can see that NVARCHAR is a higher precedence than varchar. So if you don’t specifically state how they should be converted, SQL Server is going to do the conversion, to convert your var chars to end var chars in order to be able to do that comparison. So I’m sure we’ve all seen in your SQL code where you’ve done a cast of a data type or convert to change it from one type to another, but when you don’t do that and you have mismatched data type, SQL Server has to figure out how to do that conversion, and that’s what comes across as the implicit conversion. So do we have George? Do you know some that are the most common offenders that come into this category?
George Stedman 05:49
I mean, you kind of pointed out one there. We have n varchar and VARCHAR, but from personal experience, I’ve seen people try to compare ints and var chars. Is as well as floats and ints, or pretty much any number type to any number type that isn’t the same.
Steve Stedman 06:11
Yep. And the other one that comes across too is Date Time types converting to VARCHARs, where it’s a date or a time and trying to convert that somehow. All right, so just keep this chart in mind, knowing that some of these will compare nicely, like Date Time and Date Time to work pretty well together, but if you’re trying to do like a date time to an end, varchar or even a varchar, they’re in very different positions on the chart, and they’re not going to work very smoothly. I think that’s kind of the overview on what it is. It’s basically, basically forcing some column to have to be converted when you’re doing comparisons.
George Stedman 06:47
Well, that doesn’t sound so bad, but you said this destroys performance. How does it do that?
Steve Stedman 06:54
Well, if you have a query that’s able to use an index, maybe you’re looking up ID or a phone number or name or something like that. Hopefully that works well by using index to be able to do an index seek. But what happens when the implicit conversion is being done? If the whole column that’s indexed has to be converted from one data type to another, it’s like running a function on a column where you can’t do an index seek. At that point, it turns into an index scan. And the difference between a seek is the seek gets you there quickly, going through the tree structure of the index, and maybe you’re able to get your results in like one to 10 page reads, whereas this index scan will give you N page reads, where n is the number of pages in the entire index. It has to go through every single row in that index and do that conversion and then compare it for what you’re looking for. Where that really causes trouble is that there’s a lot of work to do that. I mean, if you’ve got 2 million rows that you need to convert a bar chart on, even if it’s quick to do that bar chart converter. Do it 2 million times is going to add a lot to the CPU The other thing it does, it throws off the statistics and cardinality estimates, so that you’re not getting the best plan at that point. And realistically, what we’ve seen here is that when implicit convergence are happening, we’re getting 10 to 100 times slower queries or even more. So why is it important? Well, because if implicit converts are happening, SQL Server cannot get to your data quickly. So what it comes down to, though, is there are some times where implicit converts are no big deal, and there’s other times where it’s catastrophic. If you’re comparing it joining a table or searching on a table that requires an implicit convert, you’ve got 30 rows in the table, well, no big deal, but if you’ve got 30 million rows in the table, that might be enough to take down your entire SQL Server. Now, we were doing a load test with a client, actually, last week, where we ran into convert that completely used up 100% of all of the CPU on their SQL server when you had a few 100 users slamming the system at that point and using it. You that’s great to find out that in the load test environment, but you sure wouldn’t want to find that out on your production server.
George Stedman 09:20
I guess that kind of explains how it’s the silent killer. Because when you start, start building an application, and your database is real small, you wouldn’t notice it right away, right? And as it grows, you’re gonna slowly and slowly realize that the implicit convert has been there the entire time, and you might just drive yourself crazy trying to figure out why it’s so slow.
Steve Stedman 09:43
Yeah, and when you refer to that as the entire time, I mean, it might be five or six years that it took your database to grow big enough for it to become a problem, and once you get to that point, you’re not going to remember what was written on that query five or six years ago. You’re gonna have to figure out how to find it so.
George Stedman 10:00
Oh, that’s seems to be a decent segue way. How would I find these in my environment?
Steve Stedman 10:06
So what we’ve got here, and this is one of the sample files that you’re going to be able to download with the podcast. This is, it’s one of the Demo files in here. We’re using a database called Big stuff, and I’m just going to make sure we’re in the right database. You can replace that, if you’re running the script yourself, with your favorite test database that you’re using, but that just happened to be a database that was left over from some other testing. If you were running this in your own environment, what you would want to do is run all these create statements that I’m just going to quickly zoom through here, creating some indexes, creating some more tables and then filling it in with a bunch of information. Now some of these you can see, like transaction history here, I filled it in with 100,000 rows. Well, I ran this one a bunch of times, so it’s actually got more than 100,000 rows in there. And then it does some things to fragment some of the tables and things, and then it didn’t insert some more stuff. So you’ll want to run all that in your own environment for the but for the demo purposes today, I’ve probably, probably took 30 minutes of runtime to set up the script and fill it in with enough data. And what we have is we have five tables we’re looking at here, and let me just query how big each of those tables are. So birthdays is 3.6 million rows birthdays two similar table but different data types, is also 3.6 million rows, user logins, 1.2 million rows. Login history, 13 million rows, and transaction history, just under, just over half a million rows. So there’s some sizable tables there that we’re going to work with for demo purposes. And if we look at those, this is just all sample data, and everywhere that there’s a varchar or an end varchar column, we generally tried to fill it in with just some GUIDs where we’re using email addresses. It was just some randomly generated GUIDs, unique identifiers, in order to just fill it out and make it look like there’s some real data there. Okay, so what we’re going to do for the purpose of this demo is we’re going to make sure we have statistics IO on, and we’re going to make sure that we have execution plans on. That’s the Control M key, or you can go up here to the toolbar and click the actual execution plan button to turn it on. Okay, so now on to the fun stuff. So we’ve got two queries here, select User phone from user logins, where user email equals me, okay, George, what’s the difference?
George Stedman 12:34
What do we see here that’s happening that could be different between the two of these, and will they perform differently? Do you think, well, it looks like the second one is using a Unicode so it’s the end at the beginning. It’s gonna make it use an end varchar. But I guess if we want to know what the difference is, what is, what’s the data type of the what is the data type of the user email column?
Steve Stedman 13:03
Ooh, great question. So one of my favorite tricks, I’m just going to highlight user logins and hit alt f1 in order to bring up the table definition stuff. And we can see that user email here is of type, varchar 250, and we actually have a couple of different indexes. Well, maybe I over indexed it because I was messing around with things, but a couple of different indexes on the user email column as well, which is good to have column indexes on columns you’re querying. So then the data type is VARCHAR, 250, okay.
George Stedman 13:40
I mean, varchar and varchar aren’t that different, right?
Steve Stedman 13:47
Well, technically, they’re as different as oil and water are in the example that we talked about earlier, because they don’t mix. They don’t compare directly. I mean, sure they look the same, sure, the only difference is that there’s this n here and but they compare quite differently. And before we look at how they compare differently, I just want to talk about one thing. There’s a lot of samples out there where they show an N in front of the string, and I know there’s a lot of developers and more junior DBAs out there who get in the habit of always putting an N in front of their string, because n varchar has got to be better than VARCHAR, so they prefix all their strings with n varchar. Well, let’s see how these two compare when the only difference is that n that makes that that email address an N varchar instead of a varchar. Well, first off, when we run it, you get my phone number back on both of those. Now write that phone number down, because if you need help with your SQL Server, you can call me and we will figure out how to help you with it. But if we look at the execution plan, here’s what’s interesting. The first query that did not have the end to make it an end, varchar. Took 0% of the time compared to the second query. The first query was able to do an index seek and get the results very quickly. The second query had to do an index scan and then parallelism, and then, oops, don’t want to do those pop ups, and then a key lookup to look up. What’s it looking up? Let’s look there to bring in the user phone, and then bring that all together. And there’s a whole lot more work going down, going on there. How much more work is going on with that?
George Stedman 15:35
Well, let’s look at the let’s look at the messages, because you turned on IO.
Steve Stedman 15:40
So what I love about statistics IO is we can look at this and see exactly how much work had to be done to make that query happen. It took the first one which had an index seek was one scan with four logical reads, or four 8k pages were read, not very much. The second one took nine scans with 23,700 pages being read. Now I’m okay with math, but I can’t do 23,741 times 8k in my head. But either way, it’s a lot of space, so it’s a lot of reads that are happening. The difference here is, even if you had a table that was 10 times this size, the logical reads on doing the index seek are going to stay very small. They might not be four, they might be five or six or seven, but they’re never going to be anywhere near this bigger number. And as the table gets bigger and bigger over time, this number of the number of reads, is going to just keep growing and growing, thus taking a lot more work for that to happen. Now what’s happening here? If we go back to the plans, one of the key things that stands out as the difference is, what George what’s the giveaway here?
George Stedman 16:55
Well, one’s using the index and one’s not really using the index, because index seek is going straight to the item using there the index Tree View, and the index scan is going just one row by row through this index, not very efficiently.
Steve Stedman 17:16
the thing we’re looking for in the plan. That’s sort of a clue to help us understand this better, besides the massive amount of other operators that are happening in here is this little yellow triangle with the exclamation point at the top level. And what does that tell us?
George Stedman 17:32
Type? Conversion expression, oh, convert. Implicit, implicit conversion.
Steve Stedman 17:39
Yeah, this is where, because I’m a volunteer firefighter and EMT, and we always do different trainings on different medical issues. And if we do a training like on stroke assessment, well, then we work with a mocked up patient, well, they’re always having a stroke. Or if we do a training on fire or on heart attacks, well, the patient is always having heart attacks during the training on that one. Well, because we’re working with implicit converts on the training today. Well, implicit convert is sort of the giveaway here is what’s the problem? So what it’s saying, though it’s doing, is it’s doing an implicit convert, and it’s converting to type nvarchar, 250, the entire user, logins, user email column, and it’s saying, it may affect the seek plan, well may affect it says, well, it didn’t even seek. It’s scanned instead, and it’s, there’s a whole lot of extra work going on for that. So the answer at the end of this, if there was a quiz, a quiz to what do we look for? To see the implicit converts is simply the yellow triangle with the exclamation point in it. There’s other things you’ll get in there too. But then look at the warnings and look for the word convert, implicit. So all that was one character, 1n that threw off the difference between those two queries to be 1000s of times more work than one versus the other. Okay, so let’s, let’s move on here to another example of the same table, but in this time, this time we are, let’s leave this one down here for a minute. We have two different data types, so this would be if you were using a parameterized query coming from your application, or maybe you’re using a stored procedure, or you’re just using variables inside of your script to hold parameters, and in one case, we have an email parameter that’s defined as an NVARCHAR 2000 and another one that’s defined as a varchar, 250 what? What’s going to happen with this when we run these?
George Stedman 19:44
Well, it’s probably going to be quite similar to what happened in the last one, because you’re still comparing a varchar to an end VARCHAR, or what, I think it’s the other way around. You’re comparing a varchar to an end varchar.
Steve Stedman 19:57
Yep. So let’s take a look and find out we’re. I don’t know the answer until I look at the plan or the messages window. So the first one is going to take all the time. 23,741, you may remember that number from the previous example, and four reads here for the right data type. So by simply using the right data type, we’re able to get the result set 1000s of times faster. Now, if we go back to the plan, we can see, oh, look at that yellow triangle with the exclamation point, and convert implicit. It’s having to go convert that entire column over to that data type, and it’s doing a whole lot more work now because it can’t use the index for an index seek like it was doing originally. So while we’re training, practicing for this, George said, Well, what if we just, I think you said this, George, what if we just converted that parameter going in? So what we’re taking is an example of NVARCHAR 2000 and we’re saying, let’s just cast that and varchar 2000 to a varchar fifth, 250 so what do we think is going to happen when we do this one? Is that going to make any difference?
George Stedman 21:16
Well, I don’t know. Let’s run it.
Steve Stedman 21:19
Good answer. I like that. So let’s take a look. Oh, look at that. Four logical reads. That’s a whole lot less work than we were seeing before, and the execution plan. Yes, it’s able to do the index seek.
George Stedman 21:32
Is that because it’s converting the and varchar before it’s looked up in the query or in the table?
Steve Stedman 21:40
Yeah, exactly. So what’s happening without the cast is the email variable is coming through as an NVARCHAR, and then SQL server says, Oh, I can’t compare that. So let’s convert all of these user email fields to be var or to be and varchar to match it.
George Stedman 21:59
Instead, it’s doing this cast, or it’s doing a conversion that’s being specified by the person writing the query once on this variable, rather than once for every row in the result set. So to go back to your chart from earlier, that was the hidden chart everyone should know about. So if you were comparing, if you’re doing it the other way, if you’re comparing a VARCHAR to an end varchar, wait, is that the way? If you were doing it the other way? So it’s a VARCHAR on the table, if it was an end VARCHAR on the table and you were looking comparing it to a varchar, would it convert the other way and be just nice and fast.
Steve Stedman 22:42
Yes, if the user email column that we had here was an end varchar instead of a varchar, and it was trying to go the other way without this cast. Here just an example, like this one, for instance. No, if it was this one, it would take the user email because if it was an actual end VARCHAR, and because of the order of precedence, it would say, convert this email parameter over to an end VARCHAR, and it would run faster that way. Yes, good question. All right, so you can fix it up this way, by doing a cast sometimes on your parameter to get it to go against the order of precedence, to convert it to the type that you want to use on the query. But where you can’t do that easily, or as easily is when you start looking at joins. So let’s take a look at this table. A couple tables here. So first we have user logins, alt, f1, to bring up the parameter, and user email is of type, VARCHAR, 250, okay. Then we’ll look at login history and user email is of type and varchar 500 so how does this happen? Well, one reason that this kind of thing happens is that the login history table was maybe added two or three years later and by a different developer, and the original developer added it as type VARCHAR, and then the second developer, or varchar 250 then the second developer came along and said, Well, I’m going to be smarter about Unicode and be able to work in other environments with different languages and all that. So I’m going to make it an end varchar just to be safe, and I’m going to go with size 500 because, I mean, although email addresses could be longer than 500 they’re probably better chance of them being less than 500 than there are being less than 250 so, I mean, it was a logical decision making process that could do that. But let’s look at what happens when we do the join between these so. All we’re doing, trying to do is to look up the last login date for me using user logins and login history tables. And when we run that, we can see it created a work table as part of this, which is something in Temp DB that’s being used it did on user logins, nine scans and 23,738 page reads. So a whole lot of work being done there, when we look at the execution plan, well, well, the first thing that hopefully catches our eye is the yellow triangle with the exclamation point in it that says, convert implicit on user email, and when we look at the plan, we get an index scan on user logins and an index seek on login history. The index scan is why things are so slow. The index scan is returning almost 1.3 million rows that then need to be joined up against what we’re getting out of the login history table. So how do we fix this one?
George Stedman 26:13
Well, can we just do the Convert before it executes again? That seemed pretty simple,
Steve Stedman 26:24
like the cast, like this example, right here.
George Stedman 26:26
Yeah, the cast.
Steve Stedman 26:27
So really, what we’re doing is we’re saying, let’s take the one that is an end VARCHAR, and let’s convert it to a varchar, in this case, 250 so the types match. Now, technically, if that H dot user email coming from login history could have something longer than 250 characters, in which case it would be truncated or lost there. But let’s see what happens when we try and run this doing the cast. Now, keeping in mind I’m hitting control R to go back to the previous results before I run the new one. Previously, it took 23,007 38 plus seven logical reads overall, when we run this one, okay, 41 scans and 164 reads. That’s a whole lot better, plus the seven on login history. So yeah, that’s definitely better. Could we do even better? Okay, so let’s say you’re working in an environment where you control the schema on everything. It’s not a vendor product where they’ve set it up, where you can’t change it. One of the things we could do is we could change the user type on one of the columns so that they match. So what I’m going to do is I’m going to drop the index that’s there on user email, I’m going to change the data type, and then I’m going to add the index back in, and that also has a user email and the login date. So if we do that, yeah.
George Stedman 27:55
that seems a little risky, because isn’t the other one and varchar 500 so when you want to convert the varchar to an end VARCHAR.
Steve Stedman 28:05
yeah, good point.
George Stedman 28:07
Usually want to go to the most accepting data type if you’re going to do that permanently on a database.
Steve Stedman 28:13
yeah, really good point. However, that is not the demo I planned, but looking at it, that would be a much better option than what I’m doing here. So yes, that is correct. That would be better than doing it this way. But let’s just see what this does to fix it if we, if we do this change so we drop the index, we change the table. Oh, we need to drop there’s a view I was using down here that we’re going to drop. That’s keeping it from being able to change that column. Okay, we drop that view. Yeah, I don’t have time to rewrite this to the way you said to go, George, but you’re right. That would be a much better way to do it than to be converting it this way.
George Stedman 28:51
Well, I guess it really depends on the size constraints, constraints of your database, if you’re trying to shrink the data, then yeah, this would be the better way to go, I think.
Steve Stedman 28:59
Or if you know for sure there’s never going to be Unicode characters, which that’s kind of a tough thing to say, Yes, I absolutely know for sure that that’s never going to
Steve Stedman 29:07
be the case.
George Stedman 29:08
Well, you could write a quick check query.
Steve Stedman 29:11
How do we run a check query of all possible emails that might be put into the system in the future?
George Stedman 29:15
That’s true.
Steve Stedman 29:16
Yeah, that’s the trick. Okay, so this is running. It’s taking a minute to run. And keep in mind that by changing this and then adding the index back in, yes, it’s taking a minute or two to run here, but that’s a minute or two you have to do once, maybe during after hours or a downtime. So now to see if we can get this to be less than 23,000 logical reads here by changing the data type on that column. So we’re going to change it to varchar 250 instead of NVARCHAR. Let that run. That’ll take just a minute to run. We’re. And to do that, we have to drop the index that’s on it in order to be able to change the data type and then put the index back on when it’s done. So that’ll take just a moment to run. Okay, that finished. So let’s see how this compares. Was 23,000 something like that, reads before now we’re down to four logical reads and four logical reads, And actually for physical reads. Let’s run that again and see if the physical reads changes. Yeah, more stuff was cached in memory that time. So, yeah, basically four logical reads on each of those tables instead of the 23,000 that’s quite a bit faster. Okay, so let’s put that index, put that data type back to end, VARCHAR, and what we’re going to do is we’re going to pretend for a minute that we can’t change the table. Let’s say this is a vendor supplied product, or something like that, and if we break that, well, if we change that, it could cause problems for future support from that vendor. So one of the things we could do is we could create a view now, assuming it’s okay to create views, and what we do is create the view, and then create a clustered index on it, and the view is going to contain the converted oops. I had a typo in my demo here, not varchar 25 but varchar 250, and in order for the view to be indexed, we have to use the width schema binding parameter, which makes it so that you can’t the view. Keeps the table from being changed. So you have to drop and recreate, recreate the view if the table changes. But once that gets, once that gets back to the end of our chart data type here, in just a moment, we’ll be able to do this option of creating a view and then creating There we go. It finished. So first we’ll create a view, then we’ll create a clustered index on that view. And of course, this has got a lot of rows in this table, so this may take a minute, and then we’re going to take that original query, and instead of querying the login history table, we’re going to query the view of login history, which has a clustered index, which means the view has been materialized by creating a copy of that converted data into the clustered index, the indexed view, and let’s see how that changes things compared to the 23,000 logical reads We were getting originally. Now when we run this quick results and we’re back to four logical reads on either of them.
So by creating the view and changing the query to use the view instead of the table directly. That’s another way to get around some of those implicit conversions. If we I guess, if we run that again and we look at the plan, we can see that although there’s a convert going on, it’s not nearly as bad as the implicit convert that is happening when we do it the other way. So that’s an option. I’m going to drop that view just an example for now. Let’s look at a couple other things. So what I’m going to do is throw some values into this transaction history table that have today in them and just see how that goes. And then we’re going to do some similar things that we did before looking at dates, or instead of looking at VARCHAR, we’re looking at dates. So we have here transaction history. What is, what data type is Transaction Date? Well, if we look at that, Transaction Date is of type, varchar 10.
So one of them, we’re passing through a date, and then we’re cast, passing through a date, converted to a varchar 10. Let’s just take a look at those two as an example first. So only real difference is the cast of our chart 10, and when we run those we can see that one of them has 6600 reads, and the other one has four. And the execution plan is 100% with a clustered index scan versus 0% with a clustered index seek. That makes all the difference in the world and how fast your query is going to run and how much CPU is taken up as well. But if I was going to do the cast of a date, instead of just saying cast to varchar 10 using the default structure, I know that it was formatted in that column with the year, year, year, dash month, month, month, dash date, date format, what I would do is actually use the cast to convert it to the exact same format. So if somebody changed the settings on the server, that the date format would still work if it was in a different format, and that still comes through at four logical reads there. Okay, so then what we’re going to do, I’m just going to update the user logins column, or, sorry, the column C and the user logins table to be the same. Thing is a user ID. User ID is an int, but the column C is a varchar. So I’ve seen this with a lot of vendor supplied products we’ve worked with over the years, where they store like some kind of an ID inside of a varchar. And I’ve seen a query like this, where somebody says, Select from a table where instead of column C, it would be an ID equals a fixed number like this with no quotes around it. So that is an int data type, and it’s comparing it to a varchar. And when we run that, we end up with 42,000 logical reads on it. And when we look at the plan, we end up with the yellow exclamation and a convert implicit.
Okay, so how do we fix that? Well, it’s simple. You just have to do it as a string. And the question here is, do we need it as a and VARCHAR, or is a varchar string being passed in? So in one example, I’m going to quote it, and the other, I’m going to quote it with the N in front of it for NVARCHAR. And let’s see how those compare. In this example, 4300 which is better than what we were getting before on the number of reads. But if we do it with the right data type of a varchar, we get seven logical reads to scan that entire thing, rather than let’s go back and look at the very beginning. If we compare all three of these, converting an integer and having to go through all those others took 90% of the time, versus 10% versus 0% with the right data type, 42,000 reads versus 4300 reads versus seven and the difference here is those numbers get bigger and bigger, the bigger the table is. So that’s one of those you just shows the importance of having the right data type. And that wraps up this demo. Now on to looking at some ways to detect where your implicit conversions might all right. So that shows a bunch of ways to be able to see and find from the query plan what’s going on. But let’s look at some other ways that we can find implicit conversions.
George Stedman 37:07
What if I have 1000s of queries I’m running in my environment every day? How do I find which ones have implicit conversions?
Steve Stedman 37:15
So the way I do this, that to keep track of it, is I use extended events. And extended events is a great way to basically put a hook into SQL Server to say, catch this item and save it for me for later purposes. So we have a demo script here on this so let’s see how it works. First, we’re going to turn off any previous extended event sessions we had. Then we’re going to create a new event session, and this is looking specifically for plan affecting convert is the event, and it’s going to capture this handful of information with it, and then we’re going to go and turn on the tracking of it. Is what this does. And then we have this query that reads the results. And while this is running, it’s writing to a file somewhere to keep track of these extended events. And when we query it, let’s see what we can find there. Okay, so we had this on earlier while we’re doing the earlier demo, and we were able to catch some of the implicit conversions and things that were going on. Wow, that’s interesting. Copilot was doing all these things against the database in the background. Going to look at different things to understand the database, but we can go here and see, well, what’s something we actually ran right here select, here’s one of our queries that had an implicit convert on it, and we can copy that out and paste it to new window. And there you have it the given that you don’t know who was running this originally, you can go and pull it out of the history of what’s being saved by the extended events. Extended events is a pretty good way to track this kind of stuff, but you want to make sure you’re aware of how much is being tracked and how long it’s being kept around and those kind of things, so you don’t again, fill up your disk or anything like that.
Steve Stedman 39:19
Extended events is probably one of my favorite ways to go and capture these. Another way to do it is to use DMVs and go query like what’s running right now. And here’s one. And again, this is all sample code will include. It’s going to go and look at query, stats, SQL, text, query plan. And when we run this, we can go see that. Here are some things that are in memory that are specifically have an implicit convert on it. A lot of them are like system calls and things, but let’s click on one of these and just take a look.
At the plan. So when you look at the plan, it might not always be obvious where the implicit converter is, and they don’t always have the yellow exclamation point showing up quickly, but what you can do once you’re looking at the plan is right click and show the execution plan as XML. And once it’s in XML, go in and search on implicit convert. And we can see from that plan it’s happening here of an N char from a chart two on something in the DB, health history database. So you’re able to go in the plan, find out where that is, and then go back and look at what’s actually, where actually the problem is from that. So one, you can find it from your plan. Two, you can find it with extended events. And three, you can find it using this DMV here. Okay, I think that wraps up the demo. So that wraps up the demo. Hopefully you can see at this point that implicit converts can make a real big difference in your SQL Server, the overall load. George, any final thoughts before we move on from.
George Stedman 41:26
Yeah, I don’t have any
Steve Stedman 41:29
okay. So now a word from our sponsor is your SQL Server slowing down your business, slow queries, bottlenecks and unexpected issues, eating up your time introducing Database Health Monitor, the powerful tool built by our team at Stedman solutions, get real time insights into performance with over 100 built in reports, index analysis, weight stats tracking and proactive alerts, quickly diagnose and find problems before they impact your operations. With an easy to use interface for DBAs and developers, monitor unlimited servers, tune queries and keep your databases running healthy and fast. Download a free trial today at Databasehealth.com and take control of your SQL server performance. Yes. So what about any additional tips or best practices that you think we should follow here, George, that might make this.
George Stedman 42:32
Well, it seems like the easiest way to prevent these from happening is just choose the right data types from day one, like if it was and varchar in both tables from the get go, you wouldn’t have this issue.
Steve Stedman 42:47
The other thing I see to consider there is that safe conversions, and what safe means, in my opinion, is those that are affecting like one parameter, that aren’t being done on 1000s or hundreds of 1000s of rows. Yeah, you can use those without a performance penalty, because they’re really only happening once, rather than 2 million times. If you have 2 million rows in your table, another thing is using persisted columns. You can do those with creating a view like we showed in the demo, and then adding an index on there to eliminate some of the converts. And yeah, I guess, I mean, it’s one of those things that I over time, I am just really amazed at how much CPU load and waste and maybe bad performance on systems is caused by convert implicit and these implicit conversions can make the difference in your SQL server licensing. Like, I mean, maybe having to go buy more CPUs in order to keep things running.
George Stedman 43:50
yeah, and in that same thought, a lot of people might just think, oh, as my data grows, it’s going to get slower. That’s just how it works. And then they’ll just grow their system when it comes time to that. When you grow your system, if you’re virtualized in like Azure, Amazon, AWS, you’re gonna increase your costs, and then you’re also gonna increase your licensing costs. You know how to find these things? Oh, if you know how to find these things that are really, really easy to fix and can save you a lot of CPU. You can save a lot of money.
Steve Stedman 44:25
Yep, and you know, that’s one of those interesting things with our managed service customers that we work with over time and are continually doing performance tuning. Work with them. I mean, it’s quite common that as their system grows over time, that we actually end up cutting their number of cores and their SQL Servers back. I mean, we were working with a client just what was it just yesterday, George, we’re talking about that our recommendation as they move to a new environment was to cut the number of CPU cores in half, because they can save money that way.
George Stedman 44:54
And then we have another client where I think they’re cutting it by a quarter on the new system, because we haven’t run. So good that they don’t need as many cores anymore.
Steve Stedman 45:03
Yep. All right, so we’ll go into some listener questions in a minute, but please don’t forget to subscribe to our podcast. Hey, listeners, if you’re loving these deep dives and mind blowing insights on the Stedman sequel podcast, imagine getting even more exclusive episodes behind the scenes, bonus content and premium interviews you won’t hear anywhere else. Head over to our YouTube channel right now and hit that subscribe button turn on notifications so you never miss out on the content dropping every week. Join 1000s already unlocking the full experience, and don’t get left behind. Subscribe on YouTube today. Okay, George, do we have some lightning, lightning round questions to go through at this point?
George Stedman 45:46
Did these converts happen in Azure and Amazon, AWS, SQL the same way?
Steve Stedman 45:52
Well, back to the good old DBA response. Well, it depends. So I think that, I guess now to think about it, it really doesn’t depend, because if you’re running in AWS, on a VM or as or on Azure, as a Azure SQL database, or even as an Azure managed instance, or even as a VM in Azure, any of those options, yes, these are all going to happen exactly the same way in those environments. It’s going to because it’s the same core database engine that’s doing this, and it’s that same core database engine that’s having to compare types that don’t match. So yes, this will happen exactly the same way in Azure or AWS.
George Stedman 46:33
And then the other question was, should I just add option recompile everywhere?
Steve Stedman 46:39
Oh, that’s a great one. That’s one that, gosh, I used to do DBA interviews, and one of the questions I would ask about option recompile and somebody said, Well, yeah, we just put it everywhere and make everything run faster. Well, that may help you, if you have a problem with compiled plan cash being wrong, but it’s not going to help in any way with implicit conversions. It will not change what’s happening with your implicit conversions at all. Option recompile will actually probably make things worse, because you’ve got to compile your plan every time. So no option recompile will not help with this and But back to the general question, Should I just add it everywhere? No, never do that. That’s a bad idea. All right, so let’s basically at this point, the call to action is, if you see that yellow warning flag, go figure out why you’ve got it. Go figure out how do you fix it, and take care of those today. So if you found this interesting, I’d like to ask you to go check out our SQL server performance tuning class, where we cover all kinds of topics just like this, related to performance tuning and execution plans. And you can get that at stedman.us/performance-class and then available after this podcast, we will have all the scripts and demos that we used here to be downloadable and available on our podcast page at Stedman solutions.com What’s our next episode?
George Stedman 48:07
our next episode is going to be in two weeks, and we’re going to talk about SQL Server managed service offerings.
Steve Stedman 48:14
Yeah, and that is the core of our business, is our SQL Server managed services. So we help a lot of people that way, and we’ll talk about how we could possibly help you if you need help with your SQL Server. So at this point, I’d like to ask if you could follow us on Facebook, at facebook.com/stedmansolutions, and I’d like to thank everyone from for watching and be sure if you like the video, to give it a thumbs up and click the bell icon to subscribe. Thanks for watching our video. I’m Steve, and I hope you’ve enjoyed this, please click the thumbs up if you liked it. And if you want more information, more videos like this, click the subscribe button and hit the bell icon so you can get notified of future videos that we create.