Troubleshooting problems with joins in SQL Server 2014
If you have problems with joins in SQL Server 2014, review this troubleshooting
checklist to find potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because many join bugs were fixed in SQL Server 2014 service packs, you should
install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2014 service pack was
service pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694
2. You may receive an incorrect result when you run a query that uses a parallel
execution plan with Merge Semi join.
This is SQL Server 2014 bug. To work around this problem, you can include this
option in the query: OPTION (MAXDOP 1).
3. You may receive the error message “Non-yielding Scheduler” when you run a
query that uses a join condition and fires a trigger.
This problem occurs when the query uses the Nested Loops join. To work around this
problem, you can use the MERGE or HASH join hints. To resolve this problem, install
the latest SQL Server 2014 service pack.
4. When you delete a join filter from a merge replication publication and then
run DML statements against a table that is published for merge replication,
you can get error message “Could not find stored procedure”.
This problem occurs when the merge replication publication is configured to use
precomputed partitions and when a deleted join filter is nested under another
join filter. To work around this problem, avoid using nested join filters or
precomputed partitions.
5. When you run a SSIS package that contains a Merge Join transformation, the
package may stop responding.
In this case, the CPU Usage counter for the DTEXEC process may be zero. To work
around this problem, try to avoid using a Merge Join transformation in your
SSIS packages.
6. Access violation may occur when trigger query joins large dataset in
deleted/inserted table.
This problem occurs when parallelism is enabled and SQL Server 2014 tries to
use a parallel query execution plan to execute this trigger query. This bug
was first fixed in Cumulative Update package 1 for SQL Server 2014. You can
download the Cumulative Update package 1 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693
7. The error occurs when you access both a memory-optimized table and a
memory-optimized table variable in the same statement outside the context
of a user transaction.
This problem occurs when a database has Read Committed Snapshot Isolation (RCSI)
enabled. This bug was first fixed in Cumulative Update package 1 for SQL Server 2014.
You can download the Cumulative Update package 1 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693
To work around the problem, you can use the table hint WITH (SNAPSHOT) with
the table variable or set the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT
to ON.
8. When you run query to select data from Oracle linked server by using merge
join, the query executes successfully, but some rows are missing in the result.
This bug was first fixed in Cumulative Update package 4 for SQL Server 2014.
You can download the Cumulative Update package 4 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2999197
To work around this problem, you can use a nested loops join or a hash join by
using the “Option” command instead of the merge join.
9. Incorrect choice of a nested loops join strategy causes poor query performance.
This problem occurs because the query optimizer chooses an incorrect nested loops
join strategy. This bug was first fixed in Cumulative Update package 7 for
SQL Server 2014. You can download the Cumulative Update package 7 for
SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3046038
10. You may experience poor query performance when a query contains table joins.
This problem occurs when the related table has a Clustered Columnstore Index (CCI).
This bug was first fixed in Cumulative Update package 8 for SQL Server 2014.
You can download the Cumulative Update package 8 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3067836
11. The error 1205 occurs when you execute parallel query that contains outer
join operators in SQL Server 2014.
This error indicates that intra-query deadlock occurs. This problem only occurs
when the max degree of parallelism (MAXDOP) option is set to greater than 1.
This bug was first fixed in Cumulative Update package 8 for SQL Server 2014.
You can download the Cumulative Update package 8 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3067836
12. Incorrect results occur when you run a query that contains a nested loop
join and SQL Server add a spool to the inner side of the nested loop
during query optimization for performance reasons.
This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update
package 9 for SQL Server 2014. You can download the Cumulative Update package 9
for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3075949
13. Hash or merge join hints may be ignored when you execute a query in
SQL Server 2014.
This bug was first fixed in Cumulative Update package 9 for SQL Server 2014.
You can download the Cumulative Update package 9 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3075949
Note. After installing Cumulative Update package 9 or later you should also
turn on trace flag 4199.