In some of these cases I’ve often wondered why SQL doesn’t simply return a NULL or an invalid result. And often I wish there was an option that could set that because while it’s true that all errors should be handled, in truth they are not.

Hopefully this list can save you a bit of heartache.

• divide by zero
• string or binary data would be truncated
• Invalid length parameter passed to the LEFT or SUBSTRING function

These three errors are very common and all of them at one time or another have bit me in the pants – most of the time after deploying code and even worse, hours/days/months after deploying the code. Not only that, they can occur on an intermittent basis which is always fun to troubleshoot.

Divide by Zero

This innocent division operation turns ugly when your divisor hits the dead even zero. This is because of the rules of math. You simply cannot divide by zero. Reproducing this issue is simple enough.

Divide by zero error encountered.

The workaround is to make the divisor NULL, which then returns a NULL

Why does it error out instead of returning NULL? While I’m not sure I’d imagine if paychecks were being calculated it’d be better to have the code error out so someone would catch it than have your “Pay Amount” equal NULL. Though this is the case there are times in the past I wish it did return a NULL. Luckily there is a workaround. Overcome Divide by Zero Using NULLIF

String or Binary Data Would be Truncated

Ah yes, the dreaded string or binary data would be truncated error. Even if you have been coding for a month or so I’m sure you’ve run into this one. When this error crops up in a SQL statement that has a large SELECT list, you have to painstakingly comment out half of the columns (in b-tree fashion) to end up narrowing down which column is causing this error.

In the majority of cases when I’ve come across this error, it’s because a predefined table did not have a datatype large enough to accommodate the unsuspecting data. This can very simply be reproduced using the following example.

Gives us:

String or binary data would be truncated.

In this particular case, maybe we didn’t know what size the data would be before we inserted it. If that’s the case we can select..into a temp table instead. More info here.

Invalid length parameter passed to the LEFT or SUBSTRING function

Ah yes. Once again we have a doozy. You’ve created your complex string parsing function only to find out you didn’t account for the string not being there (At least that’s where I run into this the most).

To reproduce this error we can do

Of course it makes sense we cannot return the negative one character from the string, and I sometimes wonder why it doesn’t return a NULL. But there’s probably a reason that I don’t know about.

For simplicity, the above example doesn’t reproduce the exact error mentioned. To reproduce that error, we can do the following:

Then to fix, we can implement the CASE statement:

So remember if you can, when running into these scenarios, make sure to account for the errors that can happen.

One thing we will not cover much in this article is architecture. Unfortunately this is a much more complex subject. Suffice it to say, either you have it or you don’t. I can’t so much give architecture principles in an optimization guide, but it is probably the single most important factor in determining throughput in a system. Without a good foundation you can only build a house so large. With that said, there are still luckily plenty of optimization techniques we can employ.

Missing Indexes

The biggest way to be a hero in your organization is to create the missing indexes. This task is simple ever since the introduction of DMV’s. When an index is missing it contributes directly to IO usage, and the higher the IO typically means higher CPU. Each time the SQL Server service is restarted the system tables begin getting populated and missing index information is collected. Needless to say, the longer the SQL Server service has been running, the more accurate this information will be. Once an index is determined as missing, the number of times it could have been used is counted. Fortunately we are able to query this data to help determine what which new indexes will have the most positive impact.

This query will show the missing indexes sorted by most impactful first. Implementing the top 10 typically has an enormous impact on a system that is not very optimized.

Many times the indexes shown in the DMV above can be combined into a single index

High IO Queries

After taking care of the indexes, we can begin to focus on the high IO queries. There are two different categories high IO queries fall into.

1. High Average IO
2. High Cumulative IO

The difference between these two is that a high average IO query will (hopefully) be run infrequently but when it does run, it uses an enormous amount of resources. This is typical behavior we see in reports or job related processes.

The High Cumulative IO procedure will typically be a procedure that is executed a lot. While the average amount of IO per execution is low when it is added up throughout the day we find it can also consume an enormous amount of resources.

The same DMV is used to determine both these types of High IO queries. The only difference is that we are sorting the results by average IO vs cumulative IO. The first two queries in the above link show how to find these. Once these are found you can begin to figure out what is causing the biggest issue using the execution plan. Also reference How to Write Optimized Stored Procedures.

High CPU Queries

If your biggest bottleneck is CPU, I actually consider this to be the best bottleneck you can have. You will know if your CPU is bottlnecked because you are able to hit (95%+) usage. If this is the case, then you need to tackle the queries that take higher CPU rather than higher IO first. First of course, make sure this is being caused by SQLServer.exe and not some other process. With that out of the way, we can run yet another DMV. This DMV is basically the same one we ran for the IO, however we are sorting by worker time instead. The same “average vs cumulative” case comes into play with the CPU queries just like the IO queries. We need to focus on both the intensive “job” type queries AND the “operational” queries that utilize a lot of CPU cumulatively. This DMV will show you both.

Find Queries using the most CPU

Don’t be surprised if after lowering the CPU usage, a disk bottleneck appears. The system is so interrelated you often times will go back and forth between the two.

Most Executed Queries

One of the least recognized optimization techniques focuses on the most executed queries. Often times in systems you will find that many procedures will depend on one particular query or procedure. If this single query can be sped up, then the entire system would see a big increase in throughput. A gross example of this was one system I worked on that had a calculated column defined as a user defined function. While architecturally this is a big mistake, after optimizing this function this system that previously hit max batch requests/sec of 400, now hit over 3500. Suffice it to say my yearly salary could have been justified with that one fix. To find the most executed queries run the following DMV:

Find Most Executed Queries

When modifying sensitive procedures for performance, make sure to not only test them in staging, but also find a way to do a test in production. Execution plans may differ between environments.

Keep an Eye on Your Server

While it’s possible management may think you’re wasting time, it is very important to keep an eye on your server to become familiar with the processes that are running. If you do not do this you won’t be able to recognize new & slow processes. By watching the server you can discover blocking, adhoc queries run by users, increases in connection count, queries run at a certain time of day. There are so many positive discoveries that can come about by watching your server I can’t begin to mention them all here. There are two tools I can recommend to manually watch your server.

• sp_who3 – created by me
• Who is Active – created by Adam Machanic

Parallelism

One of the least understood and improperly configured settings in SQL Server has to be parallelism. My take on parallelism for a customer facing OLTP system, is that any user initiated process should not qualify for parallelism. Long story short is the risk is not worth the reward. Parallelism is basically a multi-threaded call for a single query. If the cost threshold is too low, then you will get everything under the sun qualifying for parallelism.

You can read more about it here: What is parallelism.
Configuring it here: Configuring parallelism.
This will help you find queries qualifying for parallelism.
This can help you control a single query.

Blocking

Blocking can also be a hindrance to throughput. Blocking occurs because the default isolation level for SQL Server is read-committed. This basically means that while a record is being updated it is locked so that it cannot be read until the update is complete. At least that is one example. Most of the time blocking is a symptom of missing or non optimal indexes. With improper indexes, scans occur. When scans occur too many records are touched and locked. To help get around blocking you need to add indexes or change the default isolation level for your procedure. To discover the blocker you can run finding the lead blocker. Run this to find the queries that are most blocked.

I hope this helps. Additional resources you could use would be the troubleshooting guide and how to write optimized stored procedures.

Let’s look at examples of the two methods to illustrate:

Since these examples are straightforward, the optimizer realizes the end result we are looking for and gives us the same execution plan for both.

However we don’t always want to rely on the optimizer’s brains. This is because as the execution plan gets more complex (by adding additional tables), it’s less likely to make good suggestions. This brings up a separate point.

When possible it’s better to keep queries small. If needed we can insert results to intermediate temp tables then use temp tables for additional joins

Anatomy of LEFT JOIN WHERE NOT NULL

While there is a chance both methods can take the same execution path, it’s not always likely. When the optimizer sees tables being joined together (as in LEFT JOIN) it anticipates that columns will also need to be returned from these tables. To help facilitate this, it will often create a worktable to help with the join. This worktable is an intermediate step to help with further joins. This worktable may be a good choice in some cases we have to keep in mind it contributes to increased IO because it’s writing to TempDB. This is where EXISTS may present a better option.

Anatomy of Exists

When the optimizer sees an EXISTS, it does not anticipate a join of two different tables – so it will often not create a worktable. The reason for this is because the very nature of EXISTS is to return a true or false, therefore, it only needs to use the EXISTS table as a filter. Now this does not mean that EXISTS will always outperform the LEFT JOIN method. The times I’ve seen the LEFT JOIN NOT NULL method beat EXISTS have usually been when too many exists were used in a single statement. This makes sense from a logical standpoint because at a certain threshold a worktable provides the benefit of an intermediary step, rather than having a single complex filter.

The other benefit of EXISTS is that it may not need to scan the entire table in order to return it’s result. Let’s say we are looking for the existence of 1 product in our SalesOrderDetail table. In the best case scenario, this product could be in the first row. If it is, we only need to find the single case where it does exist in order to be finished with our query. Even if the product is listed thousands of time in our table, we only need the first instance. This is not true with the LEFT JOIN WHERE NOT NULL. In this case, we are telling the optimizer to return us back all the joined records. Thankfully it is sometimes smart enough to know that’s not what we really want.

Checking for No Existence (Anti Join)

This gets a bit trickier. If we do a NOT EXISTS, then do you think it’s still true that we need to find the first instance of a product to be finished with our query? Nope, we literally need to touch EVERY row in order to find the rows to return. This is sometimes where I find LEFT JOIN WHERE NULL a better option. This can often be a more efficient query so you’ll have to test it out on your own.

If both operations need to touch all the rows anyway, why use LEFT JOIN WHERE NULL instead of NOT EXISTS? Well, I’m going to give two reasons and you’ll have to take my word for one of them if you don’t already agree.

1. As the query grows in complexity, NOT EXISTS can end up confusing the optimizer. You’ll have to take my word for that because I don’t have an example.
2. Since you are checking every row in this table, we may as well join it because we might need a column from it later.

If you need a column with an EXISTS table you’ll end up needing to rewrite the query or add the same table being referenced in the exists as a JOIN against the outer table. One negative to note, is that if the two tables are not 1-1 then you will end up having to do a DISTINCT. That does suck, but if you can keep your result set small, it still may be worth it.

Here’s an example:

So here we are hitting plenty of data pages regardless of the method. Only when the plans get more complex have I seen issues occur.

Alternate Methods

There are alternate methods of performing these operations. The first is to use IN or NOT IN. In my experience, these are exactly the same (execution wise) as the EXISTS. The difference is using NOT IN with NULL values can yield some unexpected results. The other option is to use the SET based EXCEPT. This can be tricky however because it functions as an ANTI UNION. Both the result sets in each operation need to match, which is not always ideal.

Conclusion

The conclusion really is not black or white. You will have to test for yourself which is quicker for your own scenario. Since there are many factors including table size, relationship, carnality, result set size, I would suggest you investigate the pros and cons of each. It is generally true however that when checking for mere existence, exists is faster. And when performing an anti-join, the LEFT JOIN method can be safer.

The rest of this article will preface the word #temp tables by using the pound sign (#) and preface @table variables using the “at” (@) symbol

#Temp tables are just regular SQL tables that are defined and stored in TempDB. The only difference between them and a permanent table is they are not allowed to have foreign keys. You can view the temp tables currently defined via SSMS by going to TempDB and Expanding Temp Tables.

Let’s look at a matrix of specific differences then we’ll touch on a few of the key differences below.

Item	#Temp Tables	@Table Variables
Can participate in a transaction
Writes to Log File
Writes only to memory (not disk)
Can Qualify for Parallelism
Allows creation of statistics
Does not affect recompilation
Allows nonclustered indexes
Allows clustered indexes
Can perform SELECT INTO
Can access in nested stored procedures
Can define globally
Can use in user defined functions
Can insert from EXEC
Allows TRUNCATE
Allows ALTER TABLE

Scope

#Temp tables can be either #locally defined (within the scope of the session) or ##globally defined (within a database). When they are locally defined, the table can be used by whatever is executed within that session (or SPID). In other words, if you define a #temp table in procedure A, then procedure A calls procedure B, procedure B will also be able to access that same temp table. This of course needs to happen within the same SPID. Once the session is terminated, the #temp tables assigned to that session are automatically deallocated.

Globally defined ##temp tables are defined by using the pound sign twice ## then the temp table name. As the name indicates, they are available to Global ##temp tables are deallocated when the originating session terminates and all locks have been released.

@Table variables on the other hand, can only be defined locally yet their scope is limited to the procedure or batch they are called within. They cannot be referenced by other procedures from within the same session.

SELECT INTO.. Capability

This advantage the #temp table has over the @table variable is not often spoken about, but in my experience it’s a big one. Let me paint a picture of the first scenario. Let’s say you need to create a @table variable to accept the filtered results of one of your permanent tables, your Employees table. You end up creating 10 procedures and in each of them you explicitly define the columns to match the data types of the Employee table. Well, all is going fine, then one day HR says they need the firstname field to accept 50 characters instead of 25. Even worse, a new developer on your team doesn’t know that you have the firstname fields explicitly defined in @table variables and he goes ahead and changes firstname column to 50 characters in Employees. Now, all of your table variables are throwing errors because the long firstnames won’t fit. This is where the advantage of the #temp table comes in. Since you can capture your results using SELECT..INTO, the datatypes are determined at runtime.

Performance

In my experience of taking an average of all queries that have used #temp tables vs @table variables, the temp tables have come out on top. The reason is that the query optimizer will sometimes generate poor plans for @table vars. This can mostly be seen when there is a lot of data. For this reason a general rule of thumb I’ve seen many places in the SQL community is. If there are less than 1000 rows, use a @table var, otherwise use a #temp table. While that may be a good rule of thumb, it certainly doesn’t apply to every situation. I have also seen some very strange scanning happen on @table variables. The biggest point I can make is that @table variables are more likely to cause unpredictable execution plans when compared to the plans generated for #temp tables. #Temp tables on the other hand, will cause more recompilation.

@Table Variables Do Not Write to Disk – Myth

There’s a common misconception that @table variables do not write to disk. I can dispel that myth with the following example. Below, we will watch the physical disk performance object using the counter “Disk Writes/sec”. By watching this object, we can be sure that the results of this graph represent actual writes to disk. This is not memory IO, this counter can only represent actual disk writes.

This example used the AdventuresWorks2012 database. The graph below shows the disk writes for both executions. Both executions took 10 seconds to execute, and they both wrote to disk the same amount. This also dispels the myth that @table vars only write to disk if the threshold goes over a certain number of records.

Itzik Ben Gan also gives an example of how @table vars write to disk in chapter 2 of his book TSQL-Programming.

Parallelism

The example below shows @table vars not qualifying for parallelism. This fact helps the argument that @table vars are better suited for small amounts of data. While parallelism is typically a symptom of a poorly written query, overall it provides a performance gain and definitely has good uses in large data transfers. This is another +1 for the #temp table.

And here’s the execution plan:

Recompilation

This is a definite advantage for the @table var. When coding for sub second throughput, you can definitely be hindered by recompilation that occurs because the dataset size that was used to initially compile the statement has now changed enough to warrant a recompile. There is a full article I wrote on this subject already that walks you through an example of the recompilation.

Summary

Hopefully this provides enough information to make a decision on what to use. Long story short, when you know you’re dealing with small result sets and you know your source data definitions are not going to change then use @table vars. Otherwise I would go with #temp tables.

Here is a simple example detailing the difference. We’ll populate a single temp table with one record, then we’ll do both a UNION, and a UNION ALL to see the results.

From the results we see below, the UNION only returns one record.

UNION returning distinct values

UNION ALL returning all values

The execution plans are identical except for the distinct operation being performed as the last operation before output. From this example we see how much utilization distinct is consuming relational to the other operations. (It’s a lot, however this is a rather simple operation)

UNION’s execution plan performs an additional DISTINCT

Just a simple concatenation

Long story short, if you know you’re two separate result sets contain distinct values, it’s best to use UNION ALL. If for some reason you can’t guarantee the uniqueness, only then use UNION.

Example:

The nolock setting above is explicit for the table it is being set against. To set the value globally for the scope of the connection, see SET TRANSACTION ISOLATION LEVEL

While the “WITH” keyword may not be necessary in current versions of SQL Server, its use is strongly recommended for future version compatibility

Advantages:

• Deadlocks will not occur against other queries running against the same data
• Less memory is utilized due to the lack of row, page, or range level locking
• Typically allows for much higher concurrency due to lower footprint

Disadvantages:

• Uncommitted data can be read leading to dirty reads
• Explicit hints against a table are generally bad practice

Usage

In most places I have worked, with (nolock) has been a generally accepted practice in the specific areas of the system that are not sensitive to data being slightly out of sync. It is important to know where things could go wrong though. The biggest red flag I can think of for not using NOLOCK would be a system that uses explicit transactions (BEGIN TRAN ..END TRAN) or heavy use of triggers. Multiple statements executed within a transaction experience a time delay of their INSERT / UPDATE / DELETE operations however the changes are committed at once upon the COMMIT. Statements that query the changed data using the READ COMMITTED isolation level will be blocked from seeing these changes until commit, whereas READ UNCOMMITTED (NOLOCK) will see the changes immediately irregardless of when the commit occurs. The assumption here is that if your system uses explicit transactions or relies on triggers heavily, it may be plausible to assume nolock is not a good idea.

Do not use WITH (NOLOCK) without fully understanding the ramifications of a dirty read

Example of a Dirty Read

The following example will open a transaction in order to update the first_name column in our global temp table: ##my_name

Here we have left a transaction open on ##my_name so that row is exclusively locked and cannot be read by any transaction using isolation level read committed or higher.

Open a new connection and execute the following queries:

Here you will see the first query will show the updated value ‘derek’, whereas the query without the nolock will hang waiting for the transaction to release. The data that has been successfully read is considered dirty data. This is because there may be other tables that need to be updated which relate to the ‘derek’ record (id=1) in order to show a consistent view of all data related to ‘derek’.

Finally let’s commit our transaction within our original window and you’ll see that you are now able to query the data without using (nolock).

There are two major methods I’ve used to create a row valued hash key. The first is by using the CHECKSUM function. The other is to use the HASHBYTES function. Both of these function return back a single value representing a hash, however their parameters differ. With CHECKSUM you can pass in a list of columns to evaluate and it returns an integer value. Whereas HASHBYTES requires a single parameter to be passed in and returns back a 16-bit binary value. The trick to forcing HASHBYTES into accepting multiple column values is to use the FOR XML function which will generate a single value to pass in. The obvious difference between the two functions is the size and datatype of the hash being returned. To make a long story short, there are rare occasions (that I have witnessed more than once) where passing in different column values into CHECKSUM will return back the exact same value. Granted this is maybe 2 times for a billion rows, but it has happened. The chances of this happening with HASHBYTES is extremely rare.

When columns are added or removed from an existing hasbytes function, it is important to to regenerate all stored hash values for source and target tables. Otherwise all records will be duplicated on import.

So, for this example we will use HASHBYTES. I don’t plan to use CHECKSUM anymore, however if space is an option I won’t rule it out.

For this example we will perform a one-way synchronization of the employee_table to the employee_sync_table by joining their primary keys and comparing the hash. We’ll put a trigger on the employee table to automatically update or insert the hash for the employee values.

Let’s start by creating the DDL for our example tables.

Our trigger will fire on insert or update and modify the row_value accordingly. Now let’s insert some values into the employee_table to see the results

row_value is the result of HASHBYTES

Now we’ll simulate an employee name change and run our update process to capture changes. Also below is an insert process to capture any inserts that may have happened.

There, now we see the name Duffy, has been changed to Buffy in our target sync table.

Let’s start by creating a sample table and populating some values:

Now let’s run a simple sort so we can see the default behavior.

Here we see everything is only sorted by it’s leading character, not taking into consideration the value of the numbers.

Now let’s execute a revised version of this order by.

Here we check to see if the field is numeric first. If it is, we cast it to float to deal with potential decimals. If it’s not numeric we simply take the ASCII value of the leading character and sort by that. This allows us to also sort the alpha characters within the field.

Here is the new result:

If the value turns out to be not caught we use the maximum value for an integer.

To paint a better picture – in an ideal database each procedure that feeds the front-end would house a simple select statement from a single table. We know in real life this is not always possible, however we should think in these terms with every web proc we write. The reason is simple – complex logic is both IO and CPU intensive. We have no control over the web traffic, but we do have control over what logic we use to serve the web. Often times it is better to run jobs in the background that perform complicated logic on behalf of the web procs and dump the results into static tables.

This methodology basically creates a denormalized meta layer of data on top of the normalized data. The argument against this is that the data will not truly be real-time. However you need to ask yourself what’s more important, “real-time” data that is 5-10 times slower or preaggregated data that is potentially seconds old?

This leads us to the technique of Creating a Hot Swap table. You can use the AdventureWorks database for this example. Let’s start:

Here we see the complex logic is being loaded into a permanent temporary table (not a mistake) “product_rollup_load”. This is not the table the web procs will query, it’s just used to house the data prior to creating our necessary indexes. We create our indexes then within a transaction rename the table the front-end procedures use to an old name, then hot swap the new table to the actual production table. Many Props go out to Tim Collins for turning me on to this technique.

Now, instead of the web procs needing complex logic, a simple SELECT will work just fine. Make sure to test this in your environment. I have seen this used in highly concurrent systems and it works well. It should be noted the other option is to use partitioned tables instead of the rename trick, however we have not run into enough problems using this technique to force that discovery.

Use caution when using the substring function. Invalid parameters (negative numbers) will cause errors

In our example we’ll start with a domain name that contains the subdomain. We will extract only the top level domain from the results. Keep in mind for the purposes of this example all the URLs listed in the table need to have a subdomain.

Setup the Data

Let’s begin by creating a new table named URLs and insert some data.

Now that we have the data setup let’s start with the extraction. For the substring function, the first parameter is the string we want to parse from, the next parameter is the starting position to be extracted and the last parameter is the number of characters to extract.

From here we see that the thir parameter is the most difficult to understand. What we are doing is subtracting a single character’s length from the number of characters to extract. This is because we do not want to include the @second_char in our string. What we have to do however is use a case statement to determine if the @second_char exists. If it does not, then we end up subtracting 1 from 0, leaving us with -1 for the 3rd parameter which is invalid.

And the result:

litwareinc.com
treyresearch.net
adatum.com
northwindtraders.com
wideworldimporters.com
proseware.com

If we want to return just the domain name without the .com or .net, we need to alter this a bit:

And the result for this:

litwareinc
treyresearch
adatum
northwindtraders
wideworldimporters
proseware