none
SQL Server Native Client driver change RRS feed

  • Question

  • Hi,

    We have upgraded SQL Server 2008 to 2017 version. There are some application which are facing slowness after up-gradation. Application is using linked server. We found the linked server which are using below driver are facing slowness. 

    1) SQL Server Native Client 10.0

    2) Microsoft OLE DB Provider for SQL Server

    We want to change the linked server driver to following-

    3)  Microsoft OLE DB Provider for ODBC Driver

    Please let me know if there is any chance of data formatting change/value change/truncating after the driver change?

    Regards

    Arif


    Saturday, June 29, 2019 8:47 AM

All replies

  • Please let me know if there is any chance of data formatting change/value change/truncating after the driver change?

    The old Microsoft OLE DB Provider for ODBC Driver (SQLOLEDB) is unaware of the newer SQL Server data types so yes, there could be differences in the data types returned if you use these newer data types (returning strings instead of native types).

    Slowness isn't usually related to the driver but the query plan. I suggest you take a closer look at both the local and remote plan before switching drivers. A common cause of query performance regression query sensitivity to the new CE. See https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, June 29, 2019 12:44 PM
    Moderator
  • To add what Dan says, switching to MSDASQL, that is OLE DB over ODBC, adds one more component which rather means that things will be slower. Also, contacts I have at Microsoft points out that using MSDASQL + ODBC driver is not supported for linked servers to other SQL Server instances.

    I fully agree with Dan that it is unlikely that the OLE DB provider is the reason for the performance problem.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, June 29, 2019 8:32 PM
  • To add what Dan says, switching to MSDASQL, that is OLE DB over ODBC, adds one more component which rather means that things will be slower. Also, contacts I have at Microsoft points out that using MSDASQL + ODBC driver is not supported for linked servers to other SQL Server instances.

    I fully agree with Dan that it is unlikely that the OLE DB provider is the reason for the performance problem.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Hi Erland,

    Thanks for your feedback. Can you tell me which driver (provider) would be best for creating linked server -

    1) SQL Server Native Client 10.0/11.0

    2) Microsoft OLE DB Provider for SQL Server

    3)  Microsoft OLE DB Provider for ODBC Driver


    Sunday, June 30, 2019 5:00 AM
  • Thanks for your feedback. Can you tell me which driver (provider) would be best for creating linked server -

    1) SQL Server Native Client 10.0/11.0

    2) Microsoft OLE DB Provider for SQL Server

    3)  Microsoft OLE DB Provider for ODBC Driver

    Depends on how adventurous you are. #2 is the most recent provider, but the first versions of it, 18.0 and 18.1, were not usable for linked servers, because of a DTC issue. That one is resolved in 18.2, but I don't have any experience to talk about to say this provider is fantastic, where as SQL Server Native Client is tested and tried. Then again, the new provider has support for multi-subnet failover which SQL Server Native Client does not have.

    It maybe goes without saying, but #1 and #2 are really the same provider, just different versions and different names. I can't believe that #2 was written from the ground up.

    The only reason that you would use #3 is that you need a linked server to SQL 2000.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, June 30, 2019 9:43 AM