One definition of leverage is "the use of a small initial investment, credit, or borrowed funds to gain a very high return in relation to one's investment, to control a much larger investment, or to reduce one's own liability for any loss." (Courtesy Dictionary.Com).

In programming, the smallest change we can make is a single bit. The next smallest change and the one we can make most effectively in human-readable source code is a single character. This story is how changing one character in a 300 line stored procedure removed 90% of the impact of the worst single query on the entire server.

This process began with a fairly normal analysis of a poorly performing server. As usual, the server was found to be I/O bound. Routine analysis indicated a particular query within a stored procedure to be the worst performing query. Comparing this query to the second worst query on the top ten list revealed it consumed 15 times the CPU and 20 times the IO cycles of the next worst offender. Calling Captain Obvious, please report to Performance Tuning.

I did an index analysis. No joy there. Everything looked correct and the query plans were fine. The query took a lookup string, hit a master lookup table, did key lookups across 4 other tables (three joins deep at most) and returned the extended attributes of the master key. The master table had about 400K rows and was constantly growing with new key values. This lookup was executed hundreds of thousands of times a day, so any improvement would be significant.

Since the schema and data was from an external data provider, I couldn't make any significant changes, nor was there any support for de-normalizing the data for a flat lookup.

My brilliant idea was to create an indexed view and let SQL Enterprise Edition do an under-the-covers substitution. Like most brilliant ideas, this did not work as expected. I could not get SQL to substitute the view for the underlying tables. ARRGHHH!

Finally I realized the problem was not in the query, nor was it in the index. It was something we usually take for granted. An implicit data type conversion was killing me. This was the culprit:

WHERE MasterKey = @LookupKey

Well, it was sort of the culprit. You see, Master_key was defined as varchar(10) by the external data provider and @LookupKey was defined by a wannabe senior developer. Naturally, a real developer uses forward-compatible data types like nvarchar(10) and not nearly obsolete types like varchar(10).

Most of us know not to write something like this:

WHERE Column_Value / 2 = 4

We write something like:

WHERE Column_Value = 8

The idea is to NOT force SQL to scan the column, run every value through a function, and then compare it to a constant. The correct way is to pre-calculate the constant and compare it to the column. Turns scans into seeks, cuts IO, promotes world peace, you get the idea.

So what does this have to do with the first WHERE clause. One rule on implicit conversions to larger data types is that the lower type always gets promoted. Thus the WHERE clause actually became:

WHERE convert(nvarchar(10), MasterKey) = @LookupKey)

Now that is pretty obviously bad.

By now, you have likely figured out the one character fix. Remove the 'N' from the declare function and use the old, bad, non-portable data type. Given the data definition for the real-world data type in this example only uses A-Z and 0-9 characters and the fix was at least a full order of magnitude faster, I can live with that.

Leverage. One character change fixes an entire problem server.