How dos MS SQL Works sending and receiving data

Posted on 2012-09-21
Last Modified: 2012-09-23
We would like some insight on how exactly Ms SQL installed in a server send/receives data from a PC connected via LAN.
Question by:rayluvs

    Author Comment

    To be more specific, we have a Ms SQL 2005 server installed in a Windows 2008 server with 5 computers connect via LAN running a VB apps which permits them to access the SQL database and manipulate data remotely from thier PC (invoicing sales and purchases orders).  

    Since each invoice may have from 200 to 300 lines, there times that it gets kind of slow, but tolerable.  

    We had a visitor that recommended that the best way to have the Team working without any slowness is to place the actual application (the .EXE) on the same server where the SQL instance is installed and have the 5 users access the apps via a shared connection to it.  The visitor stated that by having the apps in the server, the sending/receiving of SQL instructions are quicker.

    We understand the when a PC connects to a SQL server via LAN, all the queries are sent to the server, then the server executes the queries and send back the results.  

    So, is the visitor correct?  Hence, the question “How dos MS SQL Works sending and receiving data”.

    Please advice.

    Thank you very much
    LVL 5

    Expert Comment

    Is this VB Application, a windows application and not a web one? I don't think your placement of application and sql server would be the cause of the slowness that you describe.

    Are you using Stored procedures in DB to help you access/update the database through the VB app or is it only using Sql queries?

    If you are able to share the above detail, I can help you optimize the same because usually, it is the bad sql that can slow down the system and not the Sql Server in itself.
    LVL 76

    Expert Comment

    The visitor might be suggesting that the users will either remote into the server,or have application published from the server to the workstations.

    There's network overhead especially when there are multiple queries that have to be sent back and forth from the client running app.

    Author Comment


                  The VB Application is a windows application

                  The apps uses maybe 1 or 2 Stored procedures to access/update the
                  database, but the rest are  Sql queries (and there a lot of Sql queries).


                 The visitor was explicit: place the application on server where
                 the SQL instance.  Then have all 5 PC call the apps from the server, instead
                 of calling the apps from their local PC.  

                 For example:
                 instead of running as:   C:\Program Files\VBwindowsApps.exe
                 he suggested:                Z:\sharedFolder\VBwindowsApps.exe
                                                       (where 'Z:' is \\MsSQLserver\\sharedFolder\)

    What we really want to know is when a query is run from a PC, is the query executed at the PC or the query is sent to the Server and executed at the server?

    We understand that MS SQL is Client/Server, and the principle is that the workload is done at the server level.

    Please clarify.
    LVL 76

    Accepted Solution

    There is little difference in running c:\application.exe versus mapping a drive letter and then executing z:\application.exe given the SQL traffic will continue taking the same path.
    Publishing an application which will make it appear as though it is running locally and using local workstation resources, in actuality it will be running on the server.
    Communication to SQL server has three paths, shared memory, named pipes and via tcp/ip.

    Posted the wrong link, search for windows 2008 application publishing.

    Author Comment

    Two related questions:

       1. But if a computer is running the apps from a shared location, would
           it be little like a bit more traffic through the LAN
           it has to read the apps via LAN in order
           to load it to memory?

       2. You mention "Communication to SQL server has three paths, shared
           memory, named pipes and via tcp/ip"; we understand that these 3 path
           are use to process the queries sent to the SQL Server.  In conclusion,
           what we want to be sure is if SQL works as follows:

                    - workstations sent the query to the SQL server.
                    - the SQL server receives the instruction and executes it processing
                      it's create/read/update/delete (this task is done at the server, not
                      the workstation).
                    - The SQL server then send the result back to the requesting PC.

    Please reply on these 2 questions in order to close this thread.

    LVL 76

    Assisted Solution

    The queries can only be executed on the SQL server.
    The delay when using/running on the workstation is for the following.
    The application issues a select for some data
    It receives a response.
    It then reviews what it received and sends another select to the server.
    It then again receives a response
    And repeats the above cycle until it has the information need to present.

    These do not change, the speed up if any will be seen by the access from using tcp/network traffic to shared memory access to the server or local direct access via Nic.

    Lets say you have a index/reference book. One option one is to have each office have its own copy or have a single copy or have a stack of them in the common area in front.
    Either setup still requires the user to lookup the index/reference and then place the call to get the data.

    The alternative is to have access to a tool where the content that the index/reference close by.

    Author Comment

    So we are correct on both points?
    LVL 76

    Expert Comment

    Yes, to the two most recent points.
    SQL comes with tools that you could use to analyze the performance of the server and possibly get suggestions on how performance can be improved.

    Author Comment


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now