Explain TSQL running

All,
I found this script in one FIDONET conference and decided to test my servers
available around. The result is more than strange.
-- TSQL
DECLARE @.i INT,@.j INT
SET @.j=0
SET @.i=0
WHILE @.i <= 3000000
BEGIN
IF @.i > 100
SET @.j = @.j+100
IF @.i < 300
SET @.j = @.j-200
IF (@.i%6) = 3
SET @.j = 356
IF @.i%37 = 7
SET @.j = @.j + 343
SET @.i=@.i+1
END
SELECT @.j
I installed MSSQL Server 2000 on another machine on my home LAN, then logged
in from the Enterprise Manager, started the Query Analyzer for the LAN
machine from my local machine and finally started this script - 36 seconds,
the local CPU load was about 100% for one of two processors, totally 50%.
Then I closed the Query Analyzer and reopened it on my local machine - same
script - 5 minutes and 39 seconds, the CPU load was about 5-7%. That's very
strange how MSSQL Server works. If I start the script on my local machine it
looks like it works on the LAN machine, but when I start the same script on
the LAN machine it runs on my own local machine. It can't be a mistake.
Maybe I'm missing something, but I got an illusion if we run some script
it's not necessarily will be running on the machine where it should be.
Finally I started the same script against our production server through the
Internet - I suspect the the CPU load was about 1-2% for this task because
it was finished in 24:31, almost 25 minutes!
I'm not using a SQL Server cluster at home for sure, but I can't understand
that. I tested three times with the same result up to one second. The
machines on my LAN are having almost the same configuraion with only couple
differences - my local machine is a Sager (500P) laptop with 1 gig memory,
(LAN) remote (ASUSTeK P4C800 Deluxe motherboard) machine is a desktop with 2
gigs, the processors in both cases are IP 3.2HT, FBS 800, etc. The laptop
CPU has 512 kbytes internal cache, the LAN machine has 1 meg internal cache.
The other parameters are not significant for this case.
The MSSQL Server settings are same for both cases.
Maybe it's just a bug and I shouldn't pay an attention? But why the Query
Analyzer uses the wrong machine when I start the SQL Script? Configuration
problems? All servers are configured in the Enterprise Manager by their
IPs,SqlPort and it shouldn't be a mistake, my home IPs are static.
The funny thing that I saw the same reports in this FIDONET group, when the
script was started locally the CPU load was in some cases from 1-2 to 5-7%
and the duration of this script was very different, like in my case.
So, bug or feature?!
Just D.
Just D. wrote:

> All,
> I found this script in one FIDONET conference and decided to test my servers
> available around. The result is more than strange.
> -- TSQL
> DECLARE @.i INT,@.j INT
> SET @.j=0
> SET @.i=0
> WHILE @.i <= 3000000
> BEGIN
> IF @.i > 100
> SET @.j = @.j+100
> IF @.i < 300
> SET @.j = @.j-200
> IF (@.i%6) = 3
> SET @.j = 356
> IF @.i%37 = 7
> SET @.j = @.j + 343
> SET @.i=@.i+1
> END
> SELECT @.j
> I installed MSSQL Server 2000 on another machine on my home LAN, then logged
> in from the Enterprise Manager, started the Query Analyzer for the LAN
> machine from my local machine and finally started this script - 36 seconds,
> the local CPU load was about 100% for one of two processors, totally 50%.
> Then I closed the Query Analyzer and reopened it on my local machine - same
> script - 5 minutes and 39 seconds, the CPU load was about 5-7%. That's very
> strange how MSSQL Server works. If I start the script on my local machine it
> looks like it works on the LAN machine, but when I start the same script on
> the LAN machine it runs on my own local machine. It can't be a mistake.
> Maybe I'm missing something, but I got an illusion if we run some script
> it's not necessarily will be running on the machine where it should be.
> Finally I started the same script against our production server through the
> Internet - I suspect the the CPU load was about 1-2% for this task because
> it was finished in 24:31, almost 25 minutes!
> I'm not using a SQL Server cluster at home for sure, but I can't understand
> that. I tested three times with the same result up to one second. The
> machines on my LAN are having almost the same configuraion with only couple
> differences - my local machine is a Sager (500P) laptop with 1 gig memory,
> (LAN) remote (ASUSTeK P4C800 Deluxe motherboard) machine is a desktop with 2
> gigs, the processors in both cases are IP 3.2HT, FBS 800, etc. The laptop
> CPU has 512 kbytes internal cache, the LAN machine has 1 meg internal cache.
> The other parameters are not significant for this case.
> The MSSQL Server settings are same for both cases.
> Maybe it's just a bug and I shouldn't pay an attention? But why the Query
> Analyzer uses the wrong machine when I start the SQL Script? Configuration
> problems? All servers are configured in the Enterprise Manager by their
> IPs,SqlPort and it shouldn't be a mistake, my home IPs are static.
> The funny thing that I saw the same reports in this FIDONET group, when the
> script was started locally the CPU load was in some cases from 1-2 to 5-7%
> and the duration of this script was very different, like in my case.
> So, bug or feature?!
> Just D.
>
Well, can you explain what do you mean 'script runs on a wrong machine'?
It runs on a server to which QA or whatever client you are using, is connected...
I will check tomorrow how it works
|||I've had a try running local on a SQL Server and from a client PC connecting
to the SQL Server.
When running locally, the CPU usage hits 100%, with the SQL Server process
taking about 70% and the ISQLW taking up the rest.
When running from a client PC, the ISQLW process is working between 10% to
40% and the SQL Server process on the server is running between 40% to 60%.
The question is why is ISQLW so busy. What is it doing ? I thought it just
submitted the batch and sat back waiting got the result. And it doesn't
really improve when you put this into a Stored Procedure.
Al
"Andrey" wrote:

> Just D. wrote:
>
> Well, can you explain what do you mean 'script runs on a wrong machine'?
> It runs on a server to which QA or whatever client you are using, is connected...
> I will check tomorrow how it works
>
|||"Al" <Al@.discussions.microsoft.com> wrote in message
news:A47458DC-7634-4875-962F-2B4F72380D3B@.microsoft.com...
> I've had a try running local on a SQL Server and from a client PC
> connecting
> to the SQL Server.
> When running locally, the CPU usage hits 100%, with the SQL Server process
> taking about 70% and the ISQLW taking up the rest.
> When running from a client PC, the ISQLW process is working between 10% to
> 40% and the SQL Server process on the server is running between 40% to
> 60%.
> The question is why is ISQLW so busy. What is it doing ? I thought it just
> submitted the batch and sat back waiting got the result. And it doesn't
> really improve when you put this into a Stored Procedure.
It's really interesting. It must be something with DBNETLIB dlls.
RADl0PASlV
|||Tried with osql, using Named Pipes and TCP/IP and the same results for both
When running the SQL as a stored procedure or as a batch, osql is quite busy.
"RADl0PASlV" wrote:

> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:A47458DC-7634-4875-962F-2B4F72380D3B@.microsoft.com...
> It's really interesting. It must be something with DBNETLIB dlls.
> RADl0PASlV
>
>
|||Hi Al,
Are you having only one server registered in EM? Do you have a local and
remote one? It would be interesting to repeat the same that I did - one
local server, one serer on LAN and one server on the Internet.
When you were getting 70% did you try to evaluate the IP traffic? Maybe your
machine was pooling the remote server?
Just D.
" <Al@.discussions.microsoft.com> wrote in message
news:A47458DC-7634-4875-962F-2B4F72380D3B@.microsoft.com...
> I've had a try running local on a SQL Server and from a client PC
> connecting
> to the SQL Server.
> When running locally, the CPU usage hits 100%, with the SQL Server process
> taking about 70% and the ISQLW taking up the rest.
> When running from a client PC, the ISQLW process is working between 10% to
> 40% and the SQL Server process on the server is running between 40% to
> 60%.
> The question is why is ISQLW so busy. What is it doing ? I thought it just
> submitted the batch and sat back waiting got the result. And it doesn't
> really improve when you put this into a Stored Procedure.
|||The cause is the NoCount option.
If this is turned off e.g. you're getting the number of rows effect back,
then the client program (ISQLW or OSQL) is quite busy. When it is turned on,
then the client program isn't eating up any CPU cycles.
In the test across the LAN, the two execution times are 1 minute 40 seconds
for SET NOCOUNT OFF, and 30 seconds for SET NOCOUNT ON.
On face value, this is not suprising, except that the query in question only
returns the row count on the final select statement.
Al

> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:A47458DC-7634-4875-962F-2B4F72380D3B@.microsoft.com...
> It's really interesting. It must be something with DBNETLIB dlls.
> RADl0PASlV
>
|||Hi RADl0PASlV,

> It's really interesting. It must be something with DBNETLIB dlls.
Not only that is interesting. I stopped all computers and connections around
and started this test on my machine - the CPU load is 1-2% and that's all!
Why? If I want to do something, then why this MSSQLServer decides itself how
it should use my resources? I have 3.2 HT processor and I allow MSSQL Server
to use both sub-processors, but it doesn't.
Anyway, that's very strange. Placing the same code to the SP doesn't help,
same result, same duration, same CPU Load.
Just D.