performance and #of users

If we have an SQL server with 20 concurrent connections, how does it fail as it runs out of RAM and starts swapping?

Does it slow down enough on its ability to service connections that you would appear to need to add more concurrent connections? If you add these connections, will the server as a whole start to slow down as the system starts using more and more dasd to emulate the RAM it needs?

:confused:Originally posted by ricka49
If we have an SQL server with 20 concurrent connections, how does it fail as it runs out of RAM and starts swapping?

Does it slow down enough on its ability to service connections that you would appear to need to add more concurrent connections? If you add these connections, will the server as a whole start to slow down as the system starts using more and more dasd to emulate the RAM it needs?

:confused:

SQL Server 7.0 and 2000 use approximately 50 Kb of server RAM per connection. Note that a user can have multiple connections, even with connection pooling. A good rule of thumb here is one connection per 100 records returned, so 50Kb of server memory per 100 records.

I cannot site an article on this. I got this information from a MS developer when I was looking at licensing options a couple of years back.|||You can check the number of users logged in from Enterprise Manager. You can also run sp_who and/or sp_who2. There is another place to check on user connections (and also connection memory)::

Look at the sysperfinfo table on master:

SELECT * FROM master..sysperfinfo
WHERE
(object_name = 'SQLServer:General Statistics' and counter_name = 'User Connections') OR
(object_name = 'SQLServer:Memory Manager' and counter_name = 'Connection Memory (KB)')

I make it a general habit to snapshot this data to a history table on an hourly basis. It can help me sometimes track usage and performance issues.

Regards,

Hugh Scott

Originally posted by ricka49
If we have an SQL server with 20 concurrent connections, how does it fail as it runs out of RAM and starts swapping?

Does it slow down enough on its ability to service connections that you would appear to need to add more concurrent connections? If you add these connections, will the server as a whole start to slow down as the system starts using more and more dasd to emulate the RAM it needs?

:confused:

perform aggregate function & group by

Hello, anyone can help?
below SQL works in sybase but fail in sql2000. sql2000 show error on the sum
of netweight (cannot perform aggregate function) and group by commodity_code
(invalid column name !!)
SELECT
(select HTS
from bur_inv_item_list
where item = material
and plantcode = plant) as commodity_code,
(select DESCRIPTION
from bur_inv_item_list
where item = material
and plantcode = plant) as description,
uom as uom,
(select Orig
from bur_inv_item_list
where item = material
and plantcode = plant) as coo,
netweight =
sum(case when plant = '0014' or plant = '0024' then
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant), 2)
else
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant) /2.20462, 2)
end),
sum(ext_cost) as cost
from bur_inv_cntr_list
group by
commodity_code,
description,
uom,
coo
order by
commodity_code,
description,
uom,
coo
;The logical evaluation order of a SELECT statement is (top to bottom):
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
TOP
As you see, the SELECT hasn't happened yet when the GROUP BY is performed. T
his mean that you cannot
refer to any column alias name in the GROUP BY clause. Some product diverts
from the ANSI SQL
standard behavior, SQL Server does not (in this regard). So push the express
ions in a derived table
and work against that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Hello, anyone can help?
>
> below SQL works in sybase but fail in sql2000. sql2000 show error on the s
um of netweight (cannot
> perform aggregate function) and group by commodity_code (invalid column na
me !!)
>
> SELECT
> (select HTS
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as commodity_code,
> (select DESCRIPTION
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as description,
> uom as uom,
> (select Orig
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as coo,
> netweight =
> sum(case when plant = '0014' or plant = '0024' then
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant), 2)
> else
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant) /2.20462, 2)
> end),
> sum(ext_cost) as cost
> from bur_inv_cntr_list
> group by
> commodity_code,
> description,
> uom,
> coo
> order by
> commodity_code,
> description,
> uom,
> coo
> ;
>|||hi tibor,
thanks for your info. do you have any sample/case. I want to see the sample
to modify below script.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
> The logical evaluation order of a SELECT statement is (top to bottom):
> FROM
> WHERE
> GROUP BY
> HAVING
> SELECT
> ORDER BY
> TOP
> As you see, the SELECT hasn't happened yet when the GROUP BY is performed.
> This mean that you cannot refer to any column alias name in the GROUP BY
> clause. Some product diverts from the ANSI SQL standard behavior, SQL
> Server does not (in this regard). So push the expressions in a derived
> table and work against that.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>|||Here's a very simple example where a derived table is used so you don't have
to repeat the DATEPART
expression:
USE pubs
SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
FROM titles
GROUP BY DATEPART(mm, pubdate)
SELECT pub_month, COUNT(*) AS titles
FROM
(
SELECT DATEPART(mm, pubdate) AS pub_month
FROM titles
) AS i
GROUP BY pub_month
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
> hi tibor,
> thanks for your info. do you have any sample/case. I want to see the sampl
e to modify below
> script.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
>|||Hi Tibor,
Many Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ebF1yHSaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Here's a very simple example where a derived table is used so you don't
> have to repeat the DATEPART expression:
> USE pubs
> SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
> FROM titles
> GROUP BY DATEPART(mm, pubdate)
> SELECT pub_month, COUNT(*) AS titles
> FROM
> (
> SELECT DATEPART(mm, pubdate) AS pub_month
> FROM titles
> ) AS i
> GROUP BY pub_month
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>