4

I am a bit confused about how SQL Server processes multiple queries while I run a query in SQL Server Express but the data is located in the Business / Enterprise SQL Server.

I have an Enterprise version of SQL Server installed on the server which has multiple TB of data. I do not have physical access to the server hence I have two approach. Use RDP to connect to the server and run the query, or connect to SQL Server instance via SQL Server Express which is installed on my local machine (obviously we should be in the same domain). I do not have any question about RDP, my concern is about the second approach.

When I run multiple queries on my local machine (not on the server), I noticed that the performance is very slow.

From here and here it look likes that my SQL Server Express is running the query and because of the # CPU limitation, it is very slow.

But I would like to be sure about how SQL Server processes my queries. To be specific, I would like to know more about step 3 and 4 as shown in here. Specially, I would like to know how Express and Enterprise talk to each other over tcp/ip. What does SQL Server Express send to Enterprise and what does Enterprise send to Express?

I found this article, but this one does not look like the thing that I am looking for.

Community
  • 1
  • 1
Mohsen Sichani
  • 1,002
  • 12
  • 33
  • How does your local sql express connect to the server? If the SQL Express can connect to the Business server, why can't you connect? Are you mixing up SQL Server with SSMS? – Greg Nov 29 '16 at 00:05
  • Thanks Greg, I am connecting to the server via SSMS, The connection works fine. No issue in terms of connection, just about the details of communication between Express and Bussiness. – Mohsen Sichani Nov 29 '16 at 00:10
  • 2
    Well, **either** you're connected to your local SQL Server Express and the query is run there (locally - with all the limitations), **OR** you're connected (using SSMS) to the SQL Server Enterprise database, and then the query is run **on that server** (not on your local machine - you just use a GUI on your local machine to see what's going on). Unless you've setup some kind of replication or a "linked server", there's no "talking" between your local instance and the remote server instance..... – marc_s Nov 29 '16 at 06:09
  • Thanks Marc, Are you sure if I use SSMS on my local machine to connect to the Enterprise server, the query will run on the server? It is too slow to be run on the server, Is there a way to be sure about the execution machine? I will try this (http://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/) tomorrow. In addition, regarding to this: "you're connected to your local SQL Server Express and the query is run there (locally - with all the limitations)", could you please explain more? what is the data flow? – Mohsen Sichani Nov 29 '16 at 06:22
  • 1
    @Marc_s thanks, yes you are right. The queries were running on the Enterprise server when I used my local SSMS to connect to the server.So there should not be any limitation here in term of the Express edition? – Mohsen Sichani Nov 29 '16 at 18:23
  • @mohsenhs: no, if those queries are being run on the **Enterprise"" edition server, then there are no "Express" limitations being applied .... – marc_s Nov 29 '16 at 19:14

2 Answers2

2

Well, either

  • you are connected to your local SQL Server Express and the query is run there (locally - with all the limitations)

OR

  • you are connected (using SSMS from your local machine) to the SQL Server Enterprise database, and then the query is run on that server (not on your local machine - you just use a GUI on your local machine to see what's going on). Since those queries are run on the server, no limitations from your local Express instance will affect your queries

Unless you've setup some kind of replication or a "linked server", there's no "talking" between your local instance and the remote server instance.....

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
2

SQL Server is the database engine, and SSMS (SQL Server Management Studio) is just an IDE (Integrated Development Environment).

All queries are ALWAYS executed on the server regardless if it is Express or Enterprise editions.

SSMS does not execute a query ever, it does not do any work. It only passes the query to the server and display the resulting record set. The only speed difference that can be observed would be due to transferring the data set over the network from the server to the client.

Please read: What's the difference between SQL Server Management Studio and the Express edition?

Community
  • 1
  • 1
Ricardo C
  • 2,205
  • 20
  • 24