Skip to main content
Database Administrators

Return to Question

Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Incorporated comment; edited tags
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

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

I have set the linked server on the SQL20122012 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 SQL2008R22008 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. puttingPutting 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. theThe 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.

I have a seen a serious performance dive when moving from SQL2008R2 to SQL2012 on my linked server queries.

I have set the linked server on the SQL2012 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 SQL2008R2 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 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.

Improved formatting added tag
Source Link
Erik
Erik
  • 4.8k
  • 4
  • 28
  • 57

I have a seen a serious performance dive when moving from SQL2008R2SQL2008R2 to SQL2012SQL2012 on my linked server queries. 

I have set the linked server on the SQL2012 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.00IBM/iSeries driver version 13.64.12.00 (I have also tried the link as provider IBM DB2 for IBMDASQL OLE DB Provider). The SQL2008R2 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.00IBM/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.

  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 a seen a serious performance dive when moving from SQL2008R2 to SQL2012 on my linked server queries. I have set the linked server on the SQL2012 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 SQL2008R2 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 a seen a serious performance dive when moving from SQL2008R2 to SQL2012 on my linked server queries. 

I have set the linked server on the SQL2012 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 SQL2008R2 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?

Source Link
kerrilk
kerrilk
  • 21
  • 2

iSeries Linked-Server SQL 2012 performance issue

I have a seen a serious performance dive when moving from SQL2008R2 to SQL2012 on my linked server queries. I have set the linked server on the SQL2012 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 SQL2008R2 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?