Search results matching tags 'Performance' and 'Conversions'

Why Doesn’t Partition Elimination Work?

Paul White — Tue, 11 Sep 2012 20:52:00 GMT

Given a partitioned table and a simple SELECT query that compares the partitioning column to a single literal value, why does SQL Server read all the partitions when it seems obvious that only one partition needs to be examined?

Sample Data

The following script creates a table, partitioned on the char(3) column ‘Div’, and populates it with 100,000 rows of data:

     USE Sandpit;

    GO

    CREATE PARTITION FUNCTION PF (char(3))

    AS RANGE RIGHT FOR VALUES

    (

        '1', '2', '3', '4', '5', '6', '7', '8', '9',

        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',

        'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',

        'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'

    );

    GO

    CREATE PARTITION SCHEME PS

    AS PARTITION PF

    ALL TO ([PRIMARY]);

    GO

    CREATE TABLE dbo.Test

    (

        Div     char(3) NOT NULL,

        Data    char(50) NOT NULL,

    )

    ON PS(Div);

    GO

    INSERT dbo.Test WITH (TABLOCKX)

        (Div, Data)

    SELECT TOP (100000)

        CONVERT(char(3), CONVERT(char(36), NEWID())),

        CONVERT(char(50), REPLICATE('X', 50))

    FROM sys.columns AS c

    CROSS JOIN sys.columns AS c2

    CROSS JOIN sys.columns AS c3;

Sample data:

The ‘Div’ column is pseudo-random so your results will be slightly different, but this is the distribution across partitions I saw:

    SELECT

        PartitionID = F.PartitionID,

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t WITH (TABLOCK)

    CROSS APPLY (VALUES($PARTITION.PF(t.Div))) AS F (PartitionID)

    GROUP BY

        F.PartitionID

    ORDER BY

        F.PartitionID;

(The partition function defines a total of 36 partitions but only 16 are used by the sample data.)

The Test Query

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = 'ABF';

Since ‘Div’ is the partitioning column, the expectation is that only one partition will be scanned to count the rows that match the WHERE clause predicate. However, the execution plan shows that query execution scans all 36 partitions (a full table scan):

Why did SQL Server not just look at the one partition it knows the value ‘ABF’ must lie in?

Here’s Why:

The answer lies in the predicate:

The value ‘ABF’ is nowhere to be seen; it has been replaced by [@1]. This means the query has been considered for simple parameterization by SQL Server to promote query plan reuse. The presence of the [@1] does not mean that simple parameterization was successful (considered safe for all possible values by the optimizer). We have to check the plan cache to see if an associated prepared plan exists and is reused for different ‘parameter’ values. It turns out that this time the simple parameterization attempt is considered safe, so a prepared plan is created and is reused for different values. The full parameterized text is:

(@1 varchar(8000))SELECT COUNT_BIG(*) [RowCnt] FROM [dbo].[Test] [t] WHERE [t].[Div]=@1

Static Partition Elimination

The reason SQL Server cannot apply static partition elimination (determining the partition number at compile time) is that the plan now contains a parameter. Subsequent executions that reuse the plan might specify a different value for the parameter, so static elimination would not be safe. It is not possible to disable simple parameterization, but we can prevent it applying to this query in a number of ways. One way is to incorporate an inequality predicate of the form expression <> constant:

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = 'ABF'

        AND 1 <> 2;

The redundant 1 <> 2 predicate is completely removed by the optimizer, and the query still qualifies for TRIVIAL optimization as it did previously:

There are a number of interesting things to notice here:

The query is no longer parameterized (the literal value ‘ABF’ appears)
Only one partition is touched (partition 11)
This unindexed heap table has a seek predicate
The ‘ordered’ property has changed from False to True

The problem with this plan is that it cannot be reused for different predicate values – so you could end up with a separate cached plan for each possible literal value.

Adding OPTION (RECOMPILE) also defeats simple parameterization and therefore achieves static elimination. In an ad-hoc SQL batch, OPTION (RECOMPILE) also means the query plan will not be cached (stored procedures are not so lucky). The downside is a fresh trip through the optimizer on each call. This query does qualify for trivial plan, so optimization time is minimal, but more complex queries with the same desire for partition elimination might well require full optimization.

Dynamic Partition Elimination

Ideally, we would like a query plan that is reusable but still only touches one partition for any given string literal value. What we need is dynamic partition elimination. In case the concept is new to you, the optimizer can produce plans that determine the correct partition at execution time (rather than at compile-time, as for static elimination). To achieve this, the optimizer builds a plan that uses an internal function called RangePartitionNew(). One might think the original simple-parameterized plan ought to have included this feature; the reason it didn’t is quite interesting.

When SQL Server parameterizes a query using simple or forced parameterization, it has to decide on a data type for the parameter. For string literals, it always chooses varchar(8000) for non-Unicode strings and nvarchar(4000) for Unicode strings. If it chose a specific type like varchar(3), we could end up with the same plan cache bloating and non-reuse problem as for the non-parameterized case – and there are potentially 8000 different lengths for varchar. Also, prior to SQL Server 2005 SP2, the parameter-declaration part of the query text was not included in the hash value used to decide in which cache bucket to store the plan. This could result in hundreds of same-text plans (with different parameters) occupying the same hash bucket (this does not work well).

Anyway, faced with the parameterized predicate Div = [@1], where Div is char(3) and [@1] is varchar(8000), dynamic partition elimination does not occur because the value of [@1] might be truncated. One way to address this is to explicitly convert the to-be-parameterized string literal to the same type as the target column (char(3)):

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = CONVERT(char(3), 'CDC');

Now we have a parameterized query plan that uses dynamic partition elimination:

This plan has all the interesting features of the static elimination plan, but the heap seek no longer selects a hard-coded partition ID. The single partition searched is the one returned at runtime by the call to RangePartitionNew(). The explicit convert prevents data truncation when the parameterized value is compared with the partition boundary values (defined as char(3) in the partition function). The extra CONVERT_IMPLICIT to char(3) is required for internal reasons, and only appears with non-Unicode partition function types.

If we explicitly convert to char(4) instead of char(3), the possibility of truncation arises again and we are back to scanning all 36 partitions:

Using varchar(3) instead of char(3) also results in dynamic elimination, which surprises some people:

Implicit Conversions and Precedence

An alternative explanation I have seen for the original behaviour is that the string literal ‘ABF’ is implicitly typed as a varchar(3) by SQL Server and data type precedence means the char(3) ‘Div’ column has to be implicitly converted to varchar(3) to perform the comparison. This is incorrect, but it is interesting to look at why that is so:

Literal Types

We can see the implicit type of the string literal ‘ABF’ using a query:

    SELECT

        BaseType = SQL_VARIANT_PROPERTY(V.v, 'BaseType'),

        MaxLength = SQL_VARIANT_PROPERTY(V.v, 'MaxLength')

    FROM (VALUES(CONVERT(sql_variant, 'ABF'))) AS V (v);

The implied type of ‘ABF’ does seem to be varchar(3), so that checks out.

Type Precedence

The data type precedence table in Books Online shows that varchar does indeed have a higher precedence than char:

Also, from the Books Online topic Data Types (Transact-SQL):

The example below uses a UNION ALL over char(5) and varchar(3) columns:

    DECLARE @T1 AS TABLE (col1 char(5) NOT NULL);

    DECLARE @T2 AS TABLE (col1 varchar(3) NOT NULL);

    INSERT @T1 VALUES ('12345');

    INSERT @T2 VALUES ('123');

    SELECT

        BaseType = SQL_VARIANT_PROPERTY(CONVERT(sql_variant, t.col1), 'BaseType'),

        MaxLength = SQL_VARIANT_PROPERTY(CONVERT(sql_variant, t.col1), 'MaxLength'),

        t.col1

    FROM

    (

        SELECT col1 FROM  @T1 

        UNION ALL

        SELECT col1 FROM @T2

    ) AS t;

The result column ‘col1’ has to have a well-defined type. As expected, the result is varchar(5) since varchar has a higher precedence than char and 5 is the maximum length of the result. All good so far.

Strings Are Special

We get used to seeing (and preferably avoiding) implicit conversions in query plans where two mismatched types are compared:

    DECLARE @T1 AS TABLE (col1 integer NULL);

    DECLARE @v tinyint;

    SELECT col1 FROM @T1 AS t WHERE col1 = @v;

The plan shows the tinyint variable being implicitly converted to the higher-precedence integer data type, as expected. Now let’s do the same thing with varchar and char (perhaps expecting char to be converted to the higher-precedence varchar):

    DECLARE @T1 AS TABLE (col1 varchar(5) NULL);

    DECLARE @v char(3);

    SELECT col1 FROM @T1 AS t WHERE col1 = @v;

Note the lack of an implicit conversion in the predicate.

Important

SQL Server can compare char with varchar without performing a conversion. They are fundamentally the same type, it’s just that one is fixed-length and the other is variable-length. The SQL standard requires padding for the character strings used in comparisons so that their lengths match before comparing, thereby removing the only distinction between char and varchar. The same optimization can apply to a comparison between nchar and nvarchar (though not to Unicode versus non-Unicode comparisons, since they are genuinely different types of course).

The present case involves a predicate “WHERE Div = ‘ABF’”. This is a comparison that can use this optimization, so there is no implicit conversion. This is the reason the explicit conversion to varchar(3) works in the main example – the precedence rules do not need to be invoked, and the data column is not converted from char(3) to varchar(3).

Why Use a Partitioned Heap?

In case you were wondering, the only reason I chose a partitioned heap for this post is just because I could (it’s more interesting in many ways). All the demonstrations work just as well for a partitioned clustered table. A complete script using a clustered partitioned table is below:

    USE Sandpit;

    GO

    CREATE PARTITION FUNCTION PF (char(3))

    AS RANGE RIGHT FOR VALUES

    (

        '1', '2', '3', '4', '5', '6', '7', '8', '9',

        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',

        'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',

        'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'

    );

    GO

    CREATE PARTITION SCHEME PS

    AS PARTITION PF

    ALL TO ([PRIMARY]);

    GO

    CREATE TABLE dbo.Test

    (

        ID      integer IDENTITY NOT NULL,

        Div     char(3) NOT NULL,

        Data    char(50) NOT NULL,

        CONSTRAINT PK__dbo_Test_Div_ID

        PRIMARY KEY CLUSTERED (Div, ID)

        ON PS(Div)

    );

    GO

    INSERT dbo.Test WITH (TABLOCKX)

        (Div, Data)

    SELECT TOP (100000)

        CONVERT(char(3), CONVERT(char(36), NEWID())),

        CONVERT(char(50), REPLICATE('X', 50))

    FROM sys.columns AS c

    CROSS JOIN sys.columns AS c2

    CROSS JOIN sys.columns AS c3;

    GO

    -- Show the distribution of data

    SELECT

        PartitionID = F.PartitionID,

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t WITH (TABLOCK)

    CROSS APPLY (VALUES($PARTITION.PF(t.Div))) AS F (PartitionID)

    GROUP BY

        F.PartitionID

    ORDER BY

        F.PartitionID;

    GO

    -- Seek on all 36 partitions

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = 'ABF';

    GO

    -- Static elimination = 1 hard-coded partition ID

    -- Cached plan unlikely to be reused

    SELECT

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = 'ABF'

        AND 1 <> 2;

    GO

    -- Static elimination

    -- Compilation overhead

    SELECT

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = 'ABF'

    OPTION (RECOMPILE);

    GO

    -- Dynamic elimination with convert to char(3)

    -- Reusable plan

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = CONVERT(char(3), 'ABF');

    GO

    -- Dynamic elimination with convert to varchar(3)

    -- Data type precedence not applied:

    -- varchar(3) convert does not cause the char(3)

    -- column Div to be converted (would prevent a seek)

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = CONVERT(varchar(3), 'ABF');

    GO

    -- char(4) means truncation could occur when

    -- comparing with the char(3) partition function

    -- No partition elimination, seek on 36 partitions

    SELECT 

        RowCnt = COUNT_BIG(*)

    FROM dbo.Test AS t 

    WHERE 

        t.Div = CONVERT(char(4), 'ABF');

    GO

    -- Clean up

    DROP TABLE dbo.Test;

    DROP PARTITION SCHEME PS;

    DROP PARTITION FUNCTION PF;

This post applies to SQL Server 2008, 2008 R2 and 2012 only (partitioned table query execution was very different in 2005). As far as I can tell, SQL Server 2005 did not attempt simple parameterization on a partitioned table. I’m in two minds whether the SQL Server 2008+ behaviour is a bug, an oversight, or an undesirable consequence of fixing something else…so I opened a Connect item for it. Update: fixing this is under consideration for the next release.

Sean Broadley raises an important practical point in the comments below – the issue highlighted in this post only occurs when a literal value is used. By correctly parameterizing your queries (including application code and dynamic SQL) you will avoid simple parameterization, achieve dynamic partition elimination, and cache a more reusable query plan.

Acknowledgement

Thanks to Jonathan Kehayias (blog | twitter) who first altered me to the MSDN forum question that prompted this post.

Paul White
Twitter: @SQL_Kiwi
Email: SQLkiwi@gmail.com

Dynamic Seeks and Hidden Implicit Conversions

Paul White — Tue, 17 Jan 2012 13:37:00 GMT

Most people know that a LIKE predicate with only a trailing wildcard can usually use an index seek:

     SELECT 

        p.Name 

    FROM Production.Product AS p

    WHERE 

        p.Name LIKE N'D%';

As the execution plan shows, SQL Server determines a covering range (which depends on the collation), seeks the string index using the range as the start and end points of a partial scan, and applies the original LIKE condition as a residual predicate to just the rows that match the initial seek operation. Specifically, the Storage Engine seeks the index to locate rows in the covering range, and the Query Processor applies the residual predicate (the LIKE) to the rows it receives.

Dynamic Seeks

But what if the LIKE search term is in a variable?

    DECLARE @Like NVARCHAR(50) = N'D%'

    SELECT 

        p.Name 

    FROM Production.Product AS p

    WHERE 

        p.Name LIKE @Like;

SQL Server can still perform a seek here, but it needs to determine the covering seek range for the search term at execution time, not at compilation time:

The plan now contains an extra Constant Scan, a Compute Scalar and a Nested Loops Join. These operators are interesting because they have zero cost estimates: no CPU, no I/O, nothing. That’s because they are purely architectural: a workaround for the fact that SQL Server cannot currently perform a dynamic seek within the Index Seek operator itself. To avoid affecting plan choices, this extra machinery is costed at zero.

The Constant Scan produces a single in-memory row with no columns. The Compute Scalar defines expressions to describe the covering seek range (using the runtime value of the @Like variable). Finally, the Nested Loops Join drives the seek using the computed range information as correlated values.

The upper tooltip shows that the Compute Scalar uses three internal functions, LikeRangeStart, LikeRangeEnd, and LikeRangeInfo. The first two functions describe the range as an open interval. The third function returns a set of flags encoded in an integer, that are used internally to define certain seek properties for the Storage Engine. The lower tooltip shows the seek on the open interval described by the result of LikeRangeStart and LikeRangeEnd, and the application of the residual predicate ‘LIKE @Like’.

More Dynamic Seeks

Something very similar occurs in plans that use IN or OR with variables:

    DECLARE 

        @1 INTEGER = 320,

        @2 INTEGER = 325,

        @3 INTEGER = 330

    SELECT

        p.Name

    FROM Production.Product AS p

    WHERE 

        p.ProductID IN (@1,@2,@3);

Now we have three ranges: one for each of the variables in the original query. The Compute Scalar operators again define three columns containing the start and end of the range, and the associated informational flags (previously seen as a result of the LikeRangeInfo function). This time, we see the decimal representation of these flags, which happens to be 62 for an equality comparison. The IN expands to (ProductID = @1 OR ProductID = @2 OR ProductID = @3), so each of the ‘ranges’ here is in fact a single value, so the start and end range values are the same in each Compute Scalar.

The three dynamic ranges are concatenated, sorted (so any overlapping ranges appear next to each other in the stream) and the Merge Interval collapses these intervals into one or more disjoint (non-overlapping) ranges. This is important, because the three variables might, for example, all contain the same value, and it would be incorrect to return that value three times. Anyway, for each disjoint range produced, the Nested Loops Join drives a new seek of the Clustered Index. The overall effect is that an arbitrary number of possibly overlapping ranges are computed, merged, and then used to drive one or more seek operations. The final result of the query will be the combination of all the seek results, as you would expect.

Hidden Conversions

The following example contains a table with DATETIME2 values, and a query with a expression that at first sight seems unlikely to be able to seek on an index (the variable is typed as DATE, and there is a CONVERT function applied to the DATETIME2 column):

    DECLARE @Example TABLE (date_time DATETIME2 PRIMARY KEY);

    INSERT @Example (date_time) 

    VALUES ('20110101 12:34:56');

    DECLARE @date DATE = '2011-01-01';

    SELECT * 

    FROM @Example AS e 

    WHERE 

        @date = CONVERT(DATE, e.date_time);

Nevertheless, a query plan that uses a seek can be produced:

In this case, neither SSMS or Plan Explorer will show the contents of the Compute Scalar (this is probably just an oversight, rather than deliberate concealment!). We have to open the XML form of the execution plan to see the three familiar expressions, wrapped in a Value Vector (just a fancy container for multiple expressions).

Another internal function, GetRangeThroughConvert, is responsible for determining the the range of DATETIME2 values covered by the DATE variable @date, and the informational flags needed. In the same way the engine works out covering ranges for some LIKE predicates, this function determines ranges where certain problematic type conversions are required. Otherwise, the machinery is the same: a range description is defined by the Compute Scalar, and the Nested Loops Join driving a seek using those values.

More Hidden Conversions

There is another related internal function used when the Query Processor needs to determine a range for a comparison between different data types. This example returns rows based on a greater-than-or-equal comparison between DATE column values and the DATETIME return value of the GETDATE() intrinsic function:

    DECLARE @Example TABLE (col1 date PRIMARY KEY)

    SELECT * FROM @Example AS e 

    WHERE e.col1 >= DATEADD(DAY, -7, GETDATE());

Again, the SSMS graphical plan and Plan Explorer cannot display the contents of the Value Vector, so we have to dig into the XML again. The function evaluates the DATEADD(GETDATE()) expression, computes the open-interval start point of a DATE range accounting for the conversion from DATETIME to DATE, and specifies NULL as the end of the range (since this is a >= comparison, there is no end value). The flags value in this case is 22 (the flags for a >= seek operation).

Everything All At Once

This last example features all sorts type sloppiness, resulting in an execution plan that uses GetRangeThroughConvert on the string expression and GetRangeThroughConvert on the result of GetRangeWithMismatchedTypes applied to the result of the GETDATE function. The whole thing is then wrapped in a dynamic seek with the Merge Interval enforcing the (annoying) BETWEEN requirement that the first parameter must be less than or equal to the second. See if you can work out all the conversions necessary for this query, using the rules of data type precedence. It is really quite impressive that this example of lazy T-SQL coding results in an index seek, don’t you think?

    DECLARE @Example TABLE (col1 DATETIME PRIMARY KEY)

    SELECT * FROM @Example AS e

    WHERE CONVERT(DATE, e.col1) BETWEEN '20000101' AND GETDATE();

Conclusion

SQL Server works quite hard sometimes to produce index seeks where they might seem unlikely. This is a good thing, and it would be great to see this capability extended further in future. The downside is that this extra effort means you are less likely to see an Index Scan when you have done something daft with data types.

Why is this a bad thing if you get a seek anyway? The problem is that these hidden implicit conversions can result in inaccurate cardinality and distribution estimations at any stage of the plan. So, even if you get a seek, the plan might be way off overall. If that isn’t persuasive enough, consider this: will having hidden nested range calculations improve your chances of getting a good query plan? Probably not, no. Be very aware of types, and in particular of the types returned by functions and expressions. If in doubt, use SELECT INTO to materialize the results of an expression or query, and check the types of the columns produced.

Note: if you have any scripts that trawl the plan cache looking for implicit conversions (CONVERT_IMPLICIT), you might want to look into updating them to check for these conversions too. Not all conversions are bad ones, of course :)

Join Performance, Implicit Conversions, and Residuals

Paul White — Mon, 18 Jul 2011 11:17:21 GMT

You probably already know that it’s important to be aware of data types when writing queries, and that implicit conversions between types can lead to poor query performance. Some people have gone so far as to write scripts to search the plan cache for CONVERT_IMPLICIT elements, and others routinely inspect plans for that type of thing when tuning.

Now, that’s all good, as far as it goes. It may surprise you to learn that not all implicit conversions are visible in query plans, and there are other important factors to consider too. To demonstrate, I’m going to use two single-column heap tables, containing a selection of pseudo-random numbers, as shown in the diagram below (there’s a script to create the test tables and data at the end of this post):

The first table contains 5,000 rows and the second table contains 5,000,000 rows. The basic test query is to count the number of rows returned by a simple inner join between the two tables. We’ll run a number of tests to explore the performance issues, using a number of tables using different data types to store the data.

Test One – INTEGER NOT NULL

The first test features two tables where both columns are typed as INTEGER, and constrained to be NOT NULL:

Since one table is significantly smaller than the other, the query optimizer chooses a hash join. To avoid the (very useful) bitmap filter optimization featured in my last post, each query features a MAXDOP 1 query hint to guarantee a serial plan. The test query gives this query plan:

This returns its result in 900ms.

Test Two – INTEGER to REAL

Now let’s try the same query, but where the larger table’s column uses the REAL data type (the smaller table is INTEGER as before):

There are a couple of new things here. First, SQL Server needs to convert the data type of one of the columns to match the other before it can perform the join. The rules for data type precedence show that REAL has a higher precedence than INTEGER, so the integer data is converted to REAL using the CONVERT_IMPLICIT operation shown in the Compute Scalar iterator.

The join then probes the hash table (built on the converted REAL values) for matches. The possibility of hash collisions means that a residual predicate is applied to check that the values indicated as a possible match actually do match. Nothing much new or surprising here then. The query performs slightly less well than before, completing in 1000ms.

Test Three – INTEGER to DECIMAL

Same query again, but this time the larger table’s column is defined as a DECIMAL(7, 0):

The first surprise is that this query takes 1600ms.

The second surprise is that the query plan does not contain a Compute Scalar or a CONVERT_IMPLICIT anywhere, despite the fact that we are joining columns with different data types. In fact there is no indication anywhere that an implicit conversion is occurring – not in the graphical plan, not even in the more detailed XML show plan representation.

This is very bad news. Whether you are tuning the query by hand, or using a script to scan the plan cache for implicit conversions, there really is no way to see the invisible type conversion here. Even the new implicit type conversion warnings built into Denali CTP3 do not detect the problem.

Test Four – DECIMAL to INTEGER

Now let’s reverse the situation, so the smaller table’s column is DECIMAL(7,0) and the larger table is INTEGER:

Now the query runs for 1900ms.

The data precedence rules mean each INTEGER value from the large table has to be converted to DECIMAL to compute a hash value, and converted again to check the residual. Again, there is no Compute Scalar, no CONVERT_IMPLICIT…absolutely no visible indication anywhere why this query should run over twice as long as the original.

Adding an Explicit Conversion

Let’s see what happens if we introduce an explicit conversion here, choosing to convert the DECIMAL values to INTEGER in order to perform the conversion on the smaller table:

Ok, so now we have a Compute Scalar, an explicit CONVERT, and a Probe Residual that references the converted expression. This query completes in 1000ms. Compare this query plan with the one shown immediately above it – would you pick that the plan with the extra Compute Scalar would be nearly twice as fast?

Conversion Families

So what’s going on here? Why is SQL Server hiding conversion details from us that have such a profound effect on execution speed? It turns out that certain implicit conversions can be performed inside the join, and so do not require a separate Compute Scalar. This is the case if the join columns have different types, but both types come from the same ‘family’. The five families are:

In our example, DECIMAL and INTEGER are from family [1] so the implicit conversion can be performed inside the join, without a CONVERT_IMPLICIT or Compute Scalar. Stop for a moment and think about how many joins you have in production code where the columns involved are INTEGER to BIGINT, or SMALLINT to INTEGER, to take just two possible examples. Are those joins running at half speed?

The NULL Issue

There is one more subtlety to consider. Let’s run our test query one last time, using tables with INTEGER columns, but this time the columns are declared as allowing NULLs (there are no NULLs in the test data, by the way).

This query runs in around 1000ms. Remember that the first query we ran (where both columns were defined as INTEGER NOT NULL) completed in 900ms. Let’s look at the first query plan again:

Notice the lack of a Probe Residual. If the join is on a single column typed as TINYINT, SMALLINT or INTEGER and if both columns are constrained to be NOT NULL, the hash function is ‘perfect’ – meaning there is no chance of a hash collision, and the query processor does not have to check the values again to ensure they really match.

This optimization is the reason that the NOT NULL query performs 11% faster than when either or both join columns allow NULLs. Also notice that this optimization does not apply to BIGINT columns.

Summary

Here’s my general advice for best join performance:

Be aware that conversions may occur without a CONVERT_IMPLCIT or Compute Scalar
Join on a single column
Ensure join column types match exactly
Constrain potential join columns to be NOT NULL
Use TINYINT, SMALLINT, or INTEGER types to avoid residual predicates
Keep reading my blog for further reasons to prefer INTEGER NOT NULL :)
Introduce explicit conversions where necessary, as a workaround

email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi