FIX: Non-yielding scheduler error when outer joining DMVs with a mismatched ON clause in SQL Server 2008 R2 or SQL Server 2012

Applies to: SQL Server 2008 R2 DatacenterSQL Server 2008 R2 DeveloperSQL Server 2008 R2 Enterprise

Symptoms


Consider the following scenario:
  • You execute a query against sys.dm_exec_query_stats and sys.dm_exec_procedure_stats by using an outer join in Microsoft SQL Server 2012.
  • You mismatch the join criteria like joining sql_handle to plan_handle.
  • You export a report to PDF with Unicode fonts, such as Arial Unicode MDS or Lucida Sans Unicode.
In this scenario, the query will never finish and consume an entire core worth of resources indefinitely, which causes many other processes to fail.

When you try to close the SPID, it results in a non-yielding scheduler error and no visible change to the state of the server. In this situation, SQL Server will freeze when you try to restart the instance and the only way that resolves the problem is to restart Windows.

Cause


The issue occurs because the query is an infinite loop when a cached plan based on a plan handle predicate is not found on the backend logic.

Resolution


Service pack information for SQL Server 2012

To resolve this problem, obtain the latest service pack for SQL Server 2012. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2674319 Bugs that are fixed in SQL Server 2012 Service Pack 1

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.