How much work actually happens on SSMS and how much on the server itself? Suppose I run the following query:

declare @i integer
set @i=1
while @i &lt; 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?
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.)