none
Slow cross db queries RRS feed

  • Question

  • From: Brian C @brianc22222 via Twitter

    Problem with cross-db queries in Azure PaaS using external tables. Usually only 1st query is slow, but today all were.

    @AzueSupport provided the follow document: aka.ms/d9312981

    That documentation is what I used to create the connections. The problem I am having is the same as described by Srinivasan Sundara R in the comments on the first link. I experience the same problem, but we experienced it again this morning, well after the first time we used the connection. Is there something I can do to keep these connections hot - I am guessing that the connection may have gotten cold and went away?

    Thanks,

    @AzureSupport

    Saturday, January 30, 2016 8:00 PM

Answers

  • Queries usually take longer when the remoting infrastructure components have not yet been loaded into the SQL engine process. For instance, this can happen after a failover or after we moved databases between different pieces of physical hardware. Once the remoting components have been loaded on the first query, any subsequent queries will be much faster.

    In general, it would be great to get some more detailed data on the frequency of this issue. For instance, if that happens multiple times within an hour or even a day, I'd like to know about it - ideally with time stamps and server names/database names. Feel free to share that information directly with me at torsteng(at)microsoft(dot)com.

    Thanks,
    Torsten


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, February 17, 2016 4:31 PM

All replies

  • I'm sure you can see this, but this is the table that took maybe 2 minutes to respond this morning.  We have seen this probelm when creating new external connections, but not after the connection has been up for over a month.  Is this a normal thing for a production database server.

    CREATE EXTERNAL DATA SOURCE DBCAIProfileDataSrc WITH 

        (TYPE = RDBMS, 
        LOCATION = 'servername', 
        DATABASE_NAME = 'databasename', 
        CREDENTIAL = DBCAIProfile, 
    ) ;
    CREATE EXTERNAL TABLE [dbo].[Profile](
    [ProfileID] [bigint]  NOT NULL,
    [ManagerID] [bigint] NULL,
    [FirstName] [varchar](100) NOT NULL,
    [LastName] [varchar](100) NOT NULL,
    ...

    [Created] [datetime] NOT NULL,

    [LastUpdate] [datetime] NULL,
    [Active] [bit] NOT NULL)
    WITH 
    ( DATA_SOURCE = DBCAIProfileDataSrc ) 

    Saturday, January 30, 2016 11:48 PM
  • Just re-read my original question, it wasn't all queries, but it is really happening often.  The way we built our application requires two databases and for them to be able to read from each other.  But having 2 minute blocking reads on the database is unacceptable.

    Should we abandon PaaS for IaaS for more stability?


    • Edited by BCinNV Sunday, January 31, 2016 2:30 AM
    Sunday, January 31, 2016 2:30 AM
  • Hi,

    We sincerely apologize for the inconvenience you are facing because of the performance issues.

    There can be multiple reasons for the performance issue and I would need more information from your side to debug the issue.

    Can you please send me the server name and database name you are facing this issue with, along with the exact queries you are trying to use (you can hide the passwords)? In addition to that, is there anything changed since you have started seeing this performance issue?

    Please send me the above information at SDoomra(at)Microsoft(dot)com, and I will loop in the correct folks to debug the issue for you.


    Thanks Silvia Doomra

    Monday, February 1, 2016 7:20 PM
  • Just sent the information you requested.  Thanks for the response.  Hope we can get this resolved.  Azure SQL PaaS external connections were extremely hit and miss on Saturday.  Seems to have cleared up, but really need to rely on a stable solution.

    Thanks,
    Brian

    Tuesday, February 2, 2016 6:24 PM
  • Queries usually take longer when the remoting infrastructure components have not yet been loaded into the SQL engine process. For instance, this can happen after a failover or after we moved databases between different pieces of physical hardware. Once the remoting components have been loaded on the first query, any subsequent queries will be much faster.

    In general, it would be great to get some more detailed data on the frequency of this issue. For instance, if that happens multiple times within an hour or even a day, I'd like to know about it - ideally with time stamps and server names/database names. Feel free to share that information directly with me at torsteng(at)microsoft(dot)com.

    Thanks,
    Torsten


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, February 17, 2016 4:31 PM