SQL Server – Finding the Culprit Blocking SQL

The only way to prove is not a memory issue is by prooving is something else. And this requires you to identify the root cause of your performance problems. I recommend you follow a methodology like Waits and Queues. The SQLCAT team has also published a Troubleshooting Flowchart poster that you can follow.

As a general side comment: if someone is offering you to buy more memory on the server, just say YES, and then go ahead and do the root cause analysis. It doesn't matter that if the root cause of the problems are table scans, SQL Server can always use more memory.

So, I was able to reproduce the error after realizing that the CAST was being done locally, not on the remote instance. I had previously recommended moving up to SP3 in the hopes of fixing this (partially due to not being able to reproduce the error on SP3, and partially due to it being a good idea regardless). However, now that I can reproduce the error, it is clear that moving up to SP3, while still probably a good idea, is not going to fix this. And I also reproduced the error in SQL Server 2008 R2 RTM and 2014 SP1 (using a "loop-back" local Linked Server in all three cases).

It seems that this problem has to do with where the query is executing, or at least where part(s) of it are executing. I say this because I was able to get the CAST operation to work, but only by including a reference to a local DB object:

SELECT rmt.*, CAST(NULL AS UNIQUEIDENTIFIER) AS [GUID]
FROM [Local].[database_name].[dbo].[table_name] rmt
CROSS JOIN (SELECT TOP (1) 1 FROM [sys].[data_spaces]) tmp(dummy);

That actually works. But the following gets the original error:

SELECT rmt.*, CAST(NULL AS UNIQUEIDENTIFIER) AS [GUID]
FROM [Local].[database_name].[dbo].[table_name] rmt
CROSS JOIN (VALUES (1)) tmp(dummy);

I am guessing that when there are no local references, the entire query is shipped off to the remote system to be executed, and for some reason NULLs cannot be converted to UNIQUEIDENTIFIER, or perhaps the NULL is getting translated by the OLE DB driver incorrectly.

---

Based on the testing that I have done, this would appear to be a bug, but I'm not sure if the bug is within SQL Server or the SQL Server Native Client / OLEDB driver. However, the conversion error occurs within the OLEDB driver, and so is not necessarily an issue of converting from INT to UNIQUEIDENTIFIER (a conversion which is not allowed in SQL Server) since the driver is not using SQL Server to do conversions (SQL Server also does not allow for converting INT to DATE, yet the OLEDB driver handles that successfully, as shown in one of the tests).

I ran three tests. For the two that succeeded, I looked at the XML execution plans which show the query that is being executed remotely. For all three, I captured any Exceptions or OLEDB events via SQL Profiler:

Events:

• Errors and Warnings
  • Attention
  • Exception
  • Execution Warnings
  • User Error Message
• OLEDB
  • all
• TSQL
  • all except:
    • SQL:StmtRecompile
    • XQuery Static Type

Column Filters:

• ApplicationName
  • NOT LIKE %Intellisense%
• SPID
  • Greater than or equal 50

THE TESTS

• Test 1

  • CAST(NULL AS UNIQUEIDENTIFIER) that works

  SELECT TOP (2) CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
               , (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
  FROM [Local].[TEMPTEST].[sys].[objects] rmt;
  

  Relevant portion of the XML execution plan:

            <DefinedValue>
              <ColumnReference Column="Expr1002" />
              <ScalarOperator ScalarString="NULL">
                <Const ConstValue="NULL" />
              </ScalarOperator>
            </DefinedValue>
    ...
  <RemoteQuery RemoteSource="Local" RemoteQuery=
   "SELECT 1 FROM &quot;TEMPTEST&quot;.&quot;sys&quot;.&quot;objects&quot; &quot;Tbl1001&quot;"
   />
  

• Test 2

  • CAST(NULL AS UNIQUEIDENTIFIER) that fails

  SELECT TOP (2) CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
           --  , (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
  FROM [Local].[TEMPTEST].[sys].[objects] rmt;
  

  (note: I kept the subquery in there, commented out, so that it would be one less difference when I compared the XML trace files)

• Test 3

  • CAST(NULL AS DATE) that works

  SELECT TOP (2) CAST(NULL AS DATE) AS [Something]
           --  , (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
  FROM [Local].[TEMPTEST].[sys].[objects] rmt;
  

  (note: I kept the subquery in there, commented out, so that it would be one less difference when I compared the XML trace files)

  Relevant portion of the XML execution plan:

            <DefinedValue>
              <ColumnReference Column="Expr1002" />
              <ScalarOperator ScalarString="[Expr1002]">
                <Identifier>
                  <ColumnReference Column="Expr1002" />
                </Identifier>
              </ScalarOperator>
            </DefinedValue>
   ...
  <RemoteQuery RemoteSource="Local" RemoteQuery=
   "SELECT TOP (2) NULL &quot;Expr1002&quot; FROM &quot;TEMPTEST&quot;.&quot;sys&quot;.&quot;objects&quot; &quot;Tbl1001&quot;" 
   />
  

If you look at Test #3, it is doing a SELECT TOP (2) NULL on the "remote" system. The SQL Profiler trace shows that the datatype of this remote field is in fact INT. The trace also shows that the field on the client side (i.e. where I am running the query from) is DATE, as expected. The conversion from INT to DATE, something which will get an error in SQL Server, works just fine within the OLEDB driver. The remote value is NULL, so it is returned directly, hence the <ColumnReference Column="Expr1002" />.

If you look at Test #1, it is doing a SELECT 1 on the "remote" system. The SQL Profiler trace shows that the datatype of this remote field is in fact INT. The trace also shows that the field on the client side (i.e. where I am running the query from) is GUID, as expected. The conversion from INT to GUID (remember, this is done within the driver, and OLEDB calls it "GUID"), something which will get an error in SQL Server, works just fine within the OLEDB driver. The remote value is not NULL, so it is replaced with a literal NULL, hence the <Const ConstValue="NULL" />.

Test #2 fails, so there is no execution plan. However, it does query the "remote" system successfully, but just can't pass back the result set. The query that SQL Profiler captured is:

SELECT TOP (2) NULL "Expr1002" FROM "TEMPTEST"."sys"."objects" "Tbl1001"

That is that exact same query that is being done in Test #1, yet here it is failing. There are other minor differences, but I cannot fully interpret the OLEDB communication. However, the remote field is still showing as INT (wType = 3 = adInteger / four-byte signed integer / DBTYPE_I4) while the "client" field is still showing as GUID (wType = 72 = adGUID / globally unique identifier / DBTYPE_GUID). The OLE DB documentation doesn't help much as GUID Data Type Conversions, DBDATE Data Type Conversions, and I4 Data Type Conversions show that converting from I4 to either GUID or DBDATE is unsupported, yet the DATE query works.

The Trace XML files for the three tests are located on PasteBin. If you want to see the details of where each test differs from the others, you can save them locally and then do a "diff" on them. The files are:

1. NullGuidSuccess.xml
2. NullGuidError.xml
3. NullDateSuccess.xml

ERGO?

What to do about it? Probably just the work-around I noted in the top section, given that the SQL Native Client -- SQLNCLI11 -- is deprecated as of SQL Server 2012. Most of the MSDN pages on the topic of SQL Server Native Client have the following notice at the top:

Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

For more info, please see:

• SQL Server Native Client
• Installing SQL Server Native Client

---

ODBC ??

I set up an ODBC Linked Server via:

EXEC master.dbo.sp_addlinkedserver
  @server = N'LocalODBC',
  @srvproduct=N'{my_server_name}',
  @provider=N'MSDASQL',
  @provstr=N'Driver={SQL Server};Server=(local);Trusted_Connection=Yes;';

EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname=N'LocalODBC',
  @useself=N'True',
  @locallogin=NULL,
  @rmtuser=NULL,
  @rmtpassword=NULL;

And then tried:

SELECT CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
FROM [LocalODBC].[tempdb].[sys].[objects] rmt;

and received the following error:

OLE DB provider "MSDASQL" for linked server "LocalODBC" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 53
Cannot get the current row value of column "(user generated expression).Expr1002" from OLE DB provider "MSDASQL" for linked server "LocalODBC".

---

P.S.

As it relates to transporting GUIDs between remote and local servers, non-NULL values are handled via a special syntax. I noticed the following OLE DB Event info in the SQL Profiler trace when I ran CAST(0x00 AS UNIQUEIDENTIFIER):

<RemoteQuery RemoteSource="Local" RemoteQuery=
 "SELECT {guid'00000000-0000-0000-0000-000000000000'} &quot;Expr1002&quot; FROM &quot;TEMPTEST&quot;.&quot;sys&quot;.&quot;objects&quot; &quot;Tbl1001&quot;" 
 />

P.P.S.

I also tested via OPENQUERY with the following query:

SELECT TOP (2) CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
     --, (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
FROM   OPENQUERY([Local], N'SELECT 705 AS [dummy] FROM [TEMPTEST].[sys].[objects];') rmt;

and it succeeded, even without the local object reference. The SQL Profiler trace XML file has been posted to PasteBin at:

NullGuidSuccessOPENQUERY.xml

The XML execution plan shows it using a NULL constant, same as in Test #1.