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:
- All other queries through linked servers that are not to the iSeries are faster on the new server than the old.
- 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.
- 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).
- 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.