Different answers on limits in MSDE

I've seen a number of reputable NG posts (including one from an MVP)
reffering to the 5 concurrent connection limit of MSDE.
However I found this MS site reffering to an 8 operations limit for
MSDE?
"The Microsoft=AE SQL Server=99 2000 workload governor is designed to
limit the performance of an instance of the database engine any time
more than eight operations are active at the same time."
http://msdn.microsoft.com/library/?u...ec/8_ar_sa2_0=
ciq.asp?frame=3Dtrue
I have also read in the NG's that you can use the Performance
Monitoring counter called SQLServer:General Statistics\User Connections
to see if you go over the threshold. I've been regularly jumping to 10
User Connections but when I run DBCC CONCURRENCYVIOLATION to check it
says the following . . .
Concurrency violations since 2005-09-21 07:16:11.217
1 2 3 4 5 6 7 8 9 10-100 >100
0 0 0 0 0 0 0 0 0 0 0
Concurrency violations will be written to the SQL Server error log.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
This indicates I've not went over any limits.
Can anyone give a definitive answer on this one?
Tostao
hi,
Tostao wrote:
> I've seen a number of reputable NG posts (including one from an MVP)
> reffering to the 5 concurrent connection limit of MSDE.
> However I found this MS site reffering to an 8 operations limit for
> MSDE?
> "The Microsoft SQL ServerT 2000 workload governor is designed to
> limit the performance of an instance of the database engine any time
> more than eight operations are active at the same time."
> http://msdn.microsoft.com/library/?u...asp?frame=true
>
> I have also read in the NG's that you can use the Performance
> Monitoring counter called SQLServer:General Statistics\User
> Connections
> to see if you go over the threshold. I've been regularly jumping to 10
> User Connections but when I run DBCC CONCURRENCYVIOLATION to check it
> says the following . . .
> Concurrency violations since 2005-09-21 07:16:11.217
> 1 2 3 4 5 6 7 8 9 10-100 >100
> 0 0 0 0 0 0 0 0 0 0 0
> Concurrency violations will be written to the SQL Server error log.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> This indicates I've not went over any limits.
> Can anyone give a definitive answer on this one?
you are right... the limit (not an actual limit, but a condition that makes
the built-in Query Governor kicks in) is not in 5 conncurrent connections
but 8 concurrent workloads, and actually only the ones listed in
http://msdn.microsoft.com/library/de...r_sa2_0ciq.asp )
you can stay in this number even with more active connections, as active
connections can be not all "working" at the same time but just sleeping in a
rounding way.. thus you do not exceed that "limit"...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks for the prompt response Andrea. I should add that the MVP who
gave the contradicitory info was from microsoft.public.access and not
this NG.
One final question. I'm a Sys Admin not a DBA, so rather than use the
DBCC CONCURRENCYVIOLATION function, is there a Performance Monitor
counter that I can use to see if I'm nearing the 8 concurrent
operations on my MSDE instance?
|||hi,
Tostao wrote:
> Thanks for the prompt response Andrea. I should add that the MVP who
> gave the contradicitory info was from microsoft.public.access and not
> this NG.
> One final question. I'm a Sys Admin not a DBA, so rather than use the
> DBCC CONCURRENCYVIOLATION function, is there a Performance Monitor
> counter that I can use to see if I'm nearing the 8 concurrent
> operations on my MSDE instance?
not that I'm aware of..
and I think becouse DBCC CONCURRENCYVIOLATION is not evaluated at all on
full blown SQL Server editions and oly returns
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
and not the kind of info reported on Personal Edition and MSDE 2000...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||There will be a message in the SQL Server error log when the throttle kicks
in. A warning is not all that valuable because the concurrent activity
limit is a transient thing. You might have 20 users logged on and never hit
it and in other cases you might hit it with 8 users if they are all doing
long running queries simultaneously. Hitting the limit isn't catastrophic -
it just injects a delay into the activities so other than poor performance,
your users won't notice.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:42ffrcF1iql40U1@.individual.net...
> hi,
> Tostao wrote:
> not that I'm aware of..
> and I think becouse DBCC CONCURRENCYVIOLATION is not evaluated at all on
> full blown SQL Server editions and oly returns
> --
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> and not the kind of info reported on Personal Edition and MSDE 2000...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Thanks Roger - the background behind my request is I'm working on a new
Credit Card payment system for my employer. The solution is coming from
a vendor who usually implement MSDE.
Although it's structurally the same as SQL 2000, I am not comfortable
using MSDE. We're going to install the Enterprise Manager tools even if
we are forced into MSDE. This means we're liable to pay for the full
SQL license.
So I'm basically looking at the MSDE limitations to put a case to the
vendor to make a special exception for us and support installing their
programme onto a full version MS-SQL