Sql-server – iSeries Linked-Server SQL 2012 performance issue

linked-serverperformancesql serversql-server-2012

I have a seen a serious performance dive when moving from SQL Server 2008 R2 to SQL Server 2012 on my linked server queries.

I have set the linked server on the 2012 server as a Microsoft OLE DB Provider for ODBC Drivers that links through a DSN which uses the IBM/iSeries driver version 13.64.12.00 (I have also tried the link as provider IBM DB2 for IBMDASQL OLE DB Provider). The 2008 R2 server was setup with a linked server as Microsoft OLE DB Provider for ODBC Drivers that links through a DSN which uses the IBM/iSeries driver version 13.00.05.00.

I have worked with both Microsoft and IBM support for months and they are pointing fingers at each other and the issue is still not resolved. I have done more traces than I care to count. What I do know from all of these:

  1. All other queries through linked servers that are not to the iSeries are faster on the new server than the old.
  2. The iSeries drivers are significantly slower on the new server, within and outside of the SQL environment. The SQL environment does add additional slowness, but even using a separate tool to run the queries it is twice as slow on the SQL 2012 server.
  3. Putting the updated IBM iSeries drivers on did not help (used to have the same version as the SQL2008R2 server but updated in attempt to find speeed).
  4. The OLE DB linked server is slower than the ODBC linked server.

Has anyone else had this issue and what did you do to resolve it?

I have the OLE DB for DB2 provider linked (uses DB2OLEDB) server setup, but this is also much slower than the IBM ODBC connection. I already have the Allow In Process and Dynamic Parameter options set to 1. I ran the query as an OPENQUERY and as 4 part name linked server query.

Best Answer

Community Wiki answer originally left as a comment by the question author

Resolution: I unchecked “Automatically set processor affinity mask for all processors” and checked the individual CPUs. I had also already changed the MaxDOP = 0 (it was even faster than 1 or 3).

Results are blazing fast! So the bug must be that the "Automatically set processor affinity mask for all processors" box does not actually use all the CPUs, it must only see one of them. Manually selecting all the CPUs is required. This was true for both the 2008 R2 and 2012 servers.

Related Question