SQL Studio (2012) and remote DB performance question.

  • Hi,

    Sorry for posting such a trifle question, but couldn't find a sufficient answer.

    I am UK based and have a customer with SQL Server installed in Australia. There is network infrastructure in place, which lets me to connect

    AU server from my Studio - all is well.

    I would like to know how my SQL queries work in terms of bandwidth consumption and will be much obliged if one can point me to some relevant literature, please.

    For example, SELECT * FROM myTable returns 1000 records. I would like to know if those 1,000 records have been cashed first on AU server and then all in once brought to

    my SQL or there are patches of data going to and for all the time?

    Or in the following example for instance, will all the query performed on DB Server or partially? Will I get ten thousands record on my computer and then SELECT TOP 1

    will be performed locally on the cashed data?

    WITH resultSet AS (

    SELECT ten thousands records

    FROM myTable

    WHERE multiple conditions)

    SELECT TOP 1 * FROM resultSet

    Much obliged!

  • The query will return what the query returns. So, in your first example, all the 1,000 rows are loaded into memory in Australia and then they're sent over the wire as fast as they can to you. In your second example, the processing will occur in australia and you'll get one row sent across to you. Nothing will be cached locally on your machine except for the actual results sent. The bandwidth consumed will all be based on the query you send and the results returned. Processing all occurs in Australia.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thank you, Grant

    Is it correct to assume that SQL Studio will ALWAYS only send a request, e.g. EXEC stored procedure, and receive the result and all the queries and calculations will be

    performed on SQL Server?

    Regards

  • Yeah, the queries run on the server. Filtering, ordering, etc., that are defined within the query, will all be done on the server. Now, once you have the result set, if you do things with it from within Management Studio, that processing is local. But that's after the query is run.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (2/20/2015)


    Yeah, the queries run on the server. Filtering, ordering, etc., that are defined within the query, will all be done on the server. Now, once you have the result set, if you do things with it from within Management Studio, that processing is local. But that's after the query is run.

    Sorry, can you elaborate, what did you mean by "if you do things with it from within Management Studio, that processing is local", please? obviously if I copy / paste it to anywhere it will be local, as it is our of SQL scope, but anything within SQL scope?

    Kind Regards

  • BOR15K (2/20/2015)


    Grant Fritchey (2/20/2015)


    Yeah, the queries run on the server. Filtering, ordering, etc., that are defined within the query, will all be done on the server. Now, once you have the result set, if you do things with it from within Management Studio, that processing is local. But that's after the query is run.

    Sorry, can you elaborate, what did you mean by "if you do things with it from within Management Studio, that processing is local", please? obviously if I copy / paste it to anywhere it will be local, as it is our of SQL scope, but anything within SQL scope?

    Kind Regards

    Right. I mean copy & paste, or search within a result set in the grid, rearranging columns in the grid. All that sort of thing.

    Queries go to the server. That's how it works.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Regardless of where the remote server is located, so long as there is a LAN connection between, then latency is not an issue when pulling across hundreds or a few thousand rows.

    If you're doing large ETL batches between local and remote servers, then performance issues will present themselves, but it's manageable.

    However, network performance will be a serious problem when running distributed queries, meaning a SQL query that joins local tables and remote tables using 4 part naming convention.

    http://sqlbits.com/Sessions/Event10/Distributed_Query_Deep_Dive

    http://blogs.technet.com/b/pfelatam/archive/2011/09/07/linked-server-behavior-when-used-on-join-clauses.aspx

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Thank you, Eric

    The DB is in AU and only SQL Studio is on my machine, which is in the UK.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply