1

Suppose I run the following query:

declare @i integer
set @i=1
while @i < 2000000
BEGIN
insert into t1 (ID,DESC,date) values (@i%200,concat(NEWID(),newid()),GETDATE())
set @i = @i +1
END

Is the loop actually running on the client machine, and calling insert each time over the network, or is the loop running on the server and making only local calls?

On the same lines, is it better to run such queries by connecting to the server using SSMS, or by using RDP to connect to the server and using the local SSMS instance?

Improve this question
0

2 Answers 2

Reset to default
3

The SQL Server Management Studio only sends the query (or T-SQL prodcedure) to the server and waits for the result.

There's a difference between running it from LAN or directly on the server ONLY because results has to be transferred on your PC (so the difference is only the time to transfer data on LAN). (Also, in the first case, your PC has to be turned on for the entire duration of the query.)

Improve this answer
2
  • So,just so I understand correctly, in a query such as the one I mentioned in the question, the loop is also running on the server, and 2 million insert calls are not being made over the network?
    – Akash
    Commented Nov 12, 2012 at 9:38
  • 1
    @Akash: The server processes the entire batch of instructions sent to it. Therefore, it would initiate all the inserts within the course of execution of the query. But there's also such a setting as NOCOUNT. It controls whether a DML statement sends a tiny report on the number of rows affected back to the client, and the setting is off by default, meaning the reports will be produced. Two million such reports will likely affect the overall execution time of your query considerably, so you might want to issue SET NOCOUNT ON somewhere before the loop (if looping is unavoidable, of course).
    – Andriy M
    Commented Nov 12, 2012 at 13:03
2

An easy way to check this is to run SQL Profiler as you execute your statement. If you just run with the default trace settings, you'll see a couple of results related to this statement: a SQL:BatchStarting and a SQL:BatchCompleted. Both of those have as TextData the actual query. This means that SSMS is not doing the loop; SQL Server is. SSMS is, as AndreaCi points out, simply passing along the query text and telling the server to perform whatever action it needs to.

Regarding how to execute queries, I would recommend not logging directly into the server and running SSMS there (actually, I would recommend not having SSMS installed on the servers at all, but that's another topic for another question). The reason is that RDP and SSMS use server resources, so the RAM and CPU cycles that are being dedicated to running your client software aren't going to SQL Server, making legitimate server processing marginally slower for everybody.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.