Governor limitations

In some places I read the Governor has 5 max concurrent
connections;
In some other places I read it has 8 max concurrent
connections;
What is the real limitation?
hi FP,
FP wrote:
> In some places I read the Governor has 5 max concurrent
> connections;
> In some other places I read it has 8 max concurrent
> connections;
> What is the real limitation?
the real limit is 8 concurrent workloads (and not connections) as documented
in
http://msdn.microsoft.com/library/?u...asp?frame=true
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I don't understand.
If you have SQL Server Standard with 5 CAL licenses configured.
Does that mean a max of 5 clients can login?
If you have MSDE installed, a max of 32000 clients can login?
regards
Baudewijn Vermeire
|||hi Baudewijn,
Baudewijn Vermeire wrote:
> I don't understand.
> If you have SQL Server Standard with 5 CAL licenses configured.
> Does that mean a max of 5 clients can login?
> If you have MSDE installed, a max of 32000 clients can login?
> regards
> Baudewijn Vermeire
theoretically yes, but you have to keep in mind the other MSDE limits...
that's to say the Governor itself, kicking in when more then 8 concurrent
batches are executing...
more, each connection, live or sleeping, will eat about 24kb of memory,
calculated as 12 KB + 3 times the Network Packet Size (default setting that
can be partially customized via sp_configure system sotred procedure
modifying the 'user connections' setting), used to store the data structures
holding the connection 's context, as long as for buffer used to send and
receive the relative associated network streams (default to 4KB network
packet setting), that can be stolen from the buffer pool memory region
and/or the MemToLeave memory area... as these memory regions are not
infinite (:D) you will go out of resources long before the 32737 connections
limits on the 2gb memory wall of MSDE..
as you know, loosely speaking, SQL Server organizes it's memory allocation
in two distinct regions, the "buffer pool" (BPool) and the "memory to leave"
(MemToLeave) regions. (I'm excluding use of AWE because not supported at all
for MSDE and in order to provide easy understanding)
so... the BPool is the primary region SQL Server uses for it's internal
matter, while MemToLeave consists of the virtual memory space within the 1gb
user mode address space and the memory not used by the BPool.
when SQL Server starts, it begins calculating the upper limit the BPool can
reach... if no MaxMemory is set, this value will be set to the amount of the
physical memory or the size of the user mode address space (1gb) , minus the
size of the MemToLeave, whichever is less..
by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
providers, in process COM objects space and memory requirements and so on..
when MaxMemory value is explicitally set, this upper limit will only address
BPool region needs...
so only the address space of BPool pages is limited by this configuration
value, while SQL Server memory requirememts outside BPool allocation are not
limited this way...
as regards MSDE successor, SQLExpress, it will increase the 2gb datafile
limit to 4gb, remove the Workload Governor, but limite memory use to 1gb
only (thus the theoretical connection limit will actually decrease) and
limit the usable CPUs to 1
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply