Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.
Give this article a Google +1!
Issue 5 Number 10 October 2013
A few months ago, I wrote an article for this newsletter about the effect of implicit conversions on SQL Server performance. I was disappointed by the lack of interest in this topic. No one sent comments or feedback. Only a few people clicked on any of the links.
Still I think that implicit conversion is important enough to try again. This time I will provide examples to hopefully make the article a more interesting read.
I had two great successes this month in solving critical performance issues quickly. In both cases, performance was so bad that the applications hitting the database were unusable. Both solutions involved identifying and solving implicit conversion problems. We will describe these successes later in the article but we will start with a common problem I often see in the course of my consulting work.
Almost every developer of database applications knows that they must guard against SQL injection in their code. They often choose to use prepared sql statements instead of ad hoc or dynamic sql. Often they use the procedure sp_executesql to execute prepared sql. This is a very good idea in terms of protecting your database from hackers, but it does have a dark side.
Sp_executesql takes parameters. The first parameter is the sql code to be executed with place markers for other parameters. The other parameters contain the values to be substituted for the markers, usually into the WHERE clause of the sql code.
The conversion problem arises because all string parameters sent to sp_executesql must be Unicode values. If the column referenced by the string parameter is a varchar then SQL Server has to perform an implicit conversion on the column data in order to compare the values. This makes it unlikely that an index on that column can be used.
When a substantial amount of your code is written in this way, it can add up to a performance disaster. Unfortunately, this problem is often discovered only after the application has been finished and released. Sometimes the only practical solution then is to convert your string data to Unicode. The side effect of that is that your database becomes about 80% larger with no additional data in it.
I will spare readers my usual rant about forcing the use of Unicode where it is not necessary. Also I will not mention how incredibly stupid Microsoft has been in not giving sp_executesql a switch that would allow developers to use varchar strings as well as unicode.
But enough discussion of stupidities, let’s talk about the successes this past month.
My client ported a Java\Oracle application to a SQL Server database. Performance was terrible so they gave me a call. I found huge amounts of blocking, deadlocking and query timeouts. After wandering around in the dark for a while, it dawned on me that the text columns in the database were all varchars. I looked around and found that the JDBC database driver was parameterizing code in much the same way that sp_executesql does. Also, like sp_executesql, it was sending the string parameters as Unicode and incurring the implicit conversion.
Fortunately, unlike sp_executesql, there is a connection parameter in Java called SendStringParametersAsUnicode which defaults to ‘true’. Flipping that switch to ‘false’ allows varchar characters to be sent as parameters without incurring the implicit conversion.
That changed everything. The application is running like a dream and that client is very happy. It only took one tiny change to solve a severe problem.
A longtime client of mine rolled out a new version of their SAAS application. Despite all the testing that was done, when the database was hit by the tremendous impact of thousands of concurrent users, the wheels came off. CPU was hovering around 100%, queries were timing out and my client’s biggest customer had issued an ultimatum. If it wasn’t fixed by Monday, he would cancel the contract.
They gave me a call. Coming off of one success, I was still thinking about implicit conversion as a possible cause, but it took some time to find it. It turned out that someone had erroneously created an ID column as a varchar instead of an integer. This was in the busiest table in the application.
A lot of the critical code in the application was written to reference this ID as it would an integer value. The resulting implicit conversion changed a simple one row retrieval into a full scan of a multi-million row table. This code was executing several times per second and that sent the database into a tailspin.
It proved easier to convert the ID column to an integer than to change all the code that referenced it. So these guys who had been going without sleep for a day or two stayed up all night once more doing the conversion on millions of rows. Later that night I got a call saying that after the changes the cpu was now averaging about 3%, down from 100% and everything was working well.
The examples show that the effect of implicit conversion can be severe. Anyone responsible for SQL Server performance needs to be aware of it. Both of the development teams involved in these episodes are very professional and are among the best teams that I work with. This illustrates the fact that the problem of implicit conversions is little known, even by the experts.Tweet This Article.
I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.
As always, if you have questions or comments give me a call at 503.914.6442 or send me email.
Kurt Survance, SQL Consulting, Inc.
On this page:
Elsewhere on this site: