none
Managed Instance Poor Performance RRS feed

  • Question

  • Hi all,

    we are doing some test with managed instance but the performance are poor with both tier(general and business critical). We red these article and tried to improve size of log, data etc... and after change size of disk:

    https://medium.com/azure-sqldb-managed-instance

    On Azure we have a db server with sql developers 2016 in test environment with size ds2_v2(2core 7gb and Standard HDD, 1tb for data and 300gb for log). Following our test with Managed Instance:

    general Purpose 4 core 512gb -> Default size for datafile 300gb and log 2gb

    general Purpose 4 core 512gb -> size of log increased up to 200gb with datafile left 300gb

    general Purpose 4 core 1tb -> size of datafile increased up to 550gb and size of log increased up to 300gb 

    Business Critical 4 core 512 gb -> Default size for datafile 300gb and log 2gb

    Business Critical 4 core 512 gb -> size of log increased up to 200gb with datafile left 300gb

    in  both case, application is very slow compared with iaas dbserver:

    application with both managed instance need around 20-30 second to show home page and when we go to query something need 30 second to show something.

    With iaas server with this two page need around 2-3 second....

    Application is a bit old, to connect to managed instance need to create ODBC connection(with connection string xxxxxx.xxxxx.database.windows.net application didn't connect). Is possible that the cause of poor performance is ODBC?

    Managed instance is configured in redirect mode with public endpoint disabled. Subnet dedicated have service endpoint not configurated to microsoft.sql (on microsoft documentation specified to not enable it during creation of managed instance), it has to be enabled after configuration and creation?

    Thanks



    • Edited by Emanuele86 Wednesday, October 2, 2019 7:33 AM
    Tuesday, October 1, 2019 8:18 AM

All replies

  • Hi Emanuele86,

    Can you detail more information about where the application is hosted in all scenarios? When testing with IaaS SQL server instance, where is the application hosted and with Azure SQL Managed Instance...where is the application hosted? 

    Additionally, Azure SQL Managed Instance is essentially IaaS SQL Server running on a VM with a VNet. Did you not have to also configure ODBC driver when testing with IaaS SQL Server instance? My initial assumption is that the poor performance when using Azure SQL Managed Instance is associated with the VNet and the ODBC driver. Can you perform a tracert and a ping test from where the application is hosted to all databases used in your testing? This will highlight any network issues in any test that you run.

    ~Mike

    Wednesday, October 2, 2019 5:32 PM
    Moderator
  • Hi Emanuele86,

    Can you detail more information about where the application is hosted in all scenarios? When testing with IaaS SQL server instance, where is the application hosted and with Azure SQL Managed Instance...where is the application hosted? 

    Additionally, Azure SQL Managed Instance is essentially IaaS SQL Server running on a VM with a VNet. Did you not have to also configure ODBC driver when testing with IaaS SQL Server instance? My initial assumption is that the poor performance when using Azure SQL Managed Instance is associated with the VNet and the ODBC driver. Can you perform a tracert and a ping test from where the application is hosted to all databases used in your testing? This will highlight any network issues in any test that you run.

    ~Mike

    Hi,

    application is hosted on vm on azure in west europe region. Managed instance is hosted in same region and same vnet(obliviosly the subnet of managed instance is dedicated).

    I did followign test:

    From vm to managed instance trought connection troubleshoot:i see that latency is 1ms

    same test with iaas db server:

    latency is 2ms

    on application server driver are following version:

     Get-OdbcDriver | select name

    name
    ----
    Driver da Microsoft para arquivos texto (*.txt; *.csv)
    Driver do Microsoft Access (*.mdb)
    Driver do Microsoft dBase (*.dbf)
    Driver do Microsoft Excel(*.xls)
    Driver do Microsoft Paradox (*.db )
    Microsoft Access Driver (*.mdb)
    Microsoft Access-Treiber (*.mdb)
    Microsoft dBase Driver (*.dbf)
    Microsoft dBase-Treiber (*.dbf)
    Microsoft Excel Driver (*.xls)
    Microsoft Excel-Treiber (*.xls)
    Microsoft ODBC for Oracle
    Microsoft Paradox Driver (*.db )
    Microsoft Paradox-Treiber (*.db )
    Microsoft Text Driver (*.txt; *.csv)
    Microsoft Text-Treiber (*.txt; *.csv)
    SQL Server
    ODBC Driver 17 for SQL Server
    SQL Server Native Client 11.0
    SQL Server
    ODBC Driver 17 for SQL Server
    SQL Server Native Client 11.0

    To connect to managed instance with application, i have to create ODBC connection (i left all default)and after map the connection to config file of application. If i put directly connection string of managed instance, xxxx.xxxx.database.windows.net i got odbc error.

    Application run with php version 5.2 and sql driver 5.2 that is supported by managed instance


    • Edited by Emanuele86 Friday, October 4, 2019 10:12 AM
    Friday, October 4, 2019 9:29 AM
  • Thank you for this additional detail, Emanuele. The best course of action is to have a support request created to look at your specific deployment. This type of issue is virtually impossible to identify a root cause for without looking at the specific instance. If you have an Azure Support Plan, can you please file a support request to have this issue looked at more thoroughly. If you do not have an Azure Support Plan, can you please send me your Azure Subscription ID to AzCommunity and I can have a one-time support request created to have this specific issue investigated.

    Regards,

    Mike

    Friday, October 4, 2019 7:39 PM
    Moderator
  • Thank you for this additional detail, Emanuele. The best course of action is to have a support request created to look at your specific deployment. This type of issue is virtually impossible to identify a root cause for without looking at the specific instance. If you have an Azure Support Plan, can you please file a support request to have this issue looked at more thoroughly. If you do not have an Azure Support Plan, can you please send me your Azure Subscription ID to AzCommunity and I can have a one-time support request created to have this specific issue investigated.

    Regards,

    Mike

    Hi customer avoid to continue test. Application was not full compatible looking matrix compatibility on microsoft doc and performance were very poor.

    I have other question, but how i have to setup connection string on my application? Is mandatory to create an odbc connection(if yes with which driver?) that point to managed instance or i can put directly in my webconfig nameofmi.database.windows.net?

    For this application is had to create odbc connection(pointing directly to nameofmi.database.windows.net i got error, i think becouse app was not full compatible).

    THanks


    Wednesday, November 20, 2019 9:32 AM
  • Azure SQL Managed Instance has historically been published to a private service endpoint but now there is the ability to publish to a public service endpoint. If you would like to access the deployment via a FQDN, following these instructions:

    Configure public endpoint in Azure SQL Database managed instance

    If you would like to keep the private service endpoint, you need to set-up VNet peering. This document explains this in greater detail: Connectivity architecture for a managed instance in Azure SQL Database

    So, you have two options. The error you are experiencing is likely because you do not have the public service endpoint functionality enabled.

    Please let me know if you have additional questions.

    Regards,

    Mike

    Thursday, November 21, 2019 12:15 AM
    Moderator