Monitoring Active Connections

I realize this may be an elementary question for this newsgroup, but is there any way to monitor/log the number of ACTIVE concurrent connections in SQL/MSDE? I would like to log a week's worth of activity on our database to see if MSDE will work or if we
need to upgrade to full-blown SQL Server. DB size is not a concern (well below 2GB), and I don't believe connections will be either but I need data to prove it.
The performance counters in perfmon show sleeping as well as active connections. Our app is such that connections wake up, retrieve a small amount of data, and go back to sleep.
hi Ken,
"Ken" <Ken@.discussions.microsoft.com> ha scritto nel messaggio
news:5C6A46AA-C5B3-4EEA-842B-B1324C7BEAC9@.microsoft.com...
> I realize this may be an elementary question for this newsgroup, but is
there any
>way to monitor/log the number of ACTIVE concurrent connections in SQL/MSDE?
> I would like to log a week's worth of activity on our database to see if
MSDE will
>work or if we need to upgrade to full-blown SQL Server. DB size is not a
concern
> (well below 2GB), and I don't believe connections will be either but I
need data to prove it.
> The performance counters in perfmon show sleeping as well as active
connections.
>Our app is such that connections wake up, retrieve a small amount of data,
and go
> back to sleep.
please have a look at
http://msdn.microsoft.com/library/de..._dbcc_0cdq.asp
http://msdn.microsoft.com/library/de...wa-wz_3v8v.asp
that can provide a hint on how to proceed..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks, that's great. But, is there any way to do something similar on SQL Server? Our highest volume customer purchased SQL Server and is running the software on top of that. It would be great to see if their load produces any concurrency violations.
"Andrea Montanari" wrote:

> hi Ken,
> "Ken" <Ken@.discussions.microsoft.com> ha scritto nel messaggio
> news:5C6A46AA-C5B3-4EEA-842B-B1324C7BEAC9@.microsoft.com...
> there any
> MSDE will
> concern
> need data to prove it.
> connections.
> and go
> please have a look at
> http://msdn.microsoft.com/library/de..._dbcc_0cdq.asp
> http://msdn.microsoft.com/library/de...wa-wz_3v8v.asp
> that can provide a hint on how to proceed..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ken,
"Ken" <Ken@.discussions.microsoft.com> ha scritto nel messaggio
news:32D983FA-0F35-40A5-8D5B-C4AA68E3791E@.microsoft.com...
> Thanks, that's great. But, is there any way to do something similar on SQL
Server?
>Our highest volume customer purchased SQL Server and is running the
software on
> top of that. It would be great to see if their load produces any
concurrency violations.
unfortunately [or not =;-D ], not... DBCC CONCURRENCYVIOLATION, even if
available and runnable on all SQL Server editions, only returns concurrency
violations for MSDE and SQL Server Desktop Edition...
you have to inspect master..sysprocesses your way to extract such info...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||MSDE is not limited by the number of connections. You're off on the wrong
foot.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:5C6A46AA-C5B3-4EEA-842B-B1324C7BEAC9@.microsoft.com...
> I realize this may be an elementary question for this newsgroup, but is
there any way to monitor/log the number of ACTIVE concurrent connections in
SQL/MSDE? I would like to log a week's worth of activity on our database to
see if MSDE will work or if we need to upgrade to full-blown SQL Server. DB
size is not a concern (well below 2GB), and I don't believe connections will
be either but I need data to prove it.
> The performance counters in perfmon show sleeping as well as active
connections. Our app is such that connections wake up, retrieve a small
amount of data, and go back to sleep.
|||I realize it is not limited by the number of connections. According to the documentation available from Microsoft the governor kicks in when there are more than 8 active operations. According to this page (http://msdn.microsoft.com/library/en-us/architec/
8_ar_sa2_0ciq.asp?frame=true), an "active operation" includes things such as opening a connection or processing a query. I was thinking of doing a "select * from sysprocesses where status = 'runnable'" as a rough estimate of the number of active operation
s.
Are you saying this information is incorrect? If so, what is correct and what are your sources? It would be great of you to share, especially if the Microsoft documentation is incorrect.
"William (Bill) Vaughn" wrote:

> MSDE is not limited by the number of connections. You're off on the wrong
> foot.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:5C6A46AA-C5B3-4EEA-842B-B1324C7BEAC9@.microsoft.com...
> there any way to monitor/log the number of ACTIVE concurrent connections in
> SQL/MSDE? I would like to log a week's worth of activity on our database to
> see if MSDE will work or if we need to upgrade to full-blown SQL Server. DB
> size is not a concern (well below 2GB), and I don't believe connections will
> be either but I need data to prove it.
> connections. Our app is such that connections wake up, retrieve a small
> amount of data, and go back to sleep.
>
>
|||I realize it is not limited by the number of connections. According to the documentation available from Microsoft the governor kicks in when there are more than 8 active operations. According to this page (http://msdn.microsoft.com/library/en-us/architec/
8_ar_sa2_0ciq.asp?frame=true), an "active operation" includes things such as opening a connection or processing a query. I was thinking of doing a "select * from sysprocesses where status = 'runnable'" as a rough estimate of the number of active operation
s.
Are you saying this information is incorrect? If so, what is correct and what are your sources? It would be great of you to share, especially if the Microsoft documentation is incorrect.
"William (Bill) Vaughn" wrote:

> MSDE is not limited by the number of connections. You're off on the wrong
> foot.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:5C6A46AA-C5B3-4EEA-842B-B1324C7BEAC9@.microsoft.com...
> there any way to monitor/log the number of ACTIVE concurrent connections in
> SQL/MSDE? I would like to log a week's worth of activity on our database to
> see if MSDE will work or if we need to upgrade to full-blown SQL Server. DB
> size is not a concern (well below 2GB), and I don't believe connections will
> be either but I need data to prove it.
> connections. Our app is such that connections wake up, retrieve a small
> amount of data, and go back to sleep.
>
>
|||The point I was trying to make is having an open connection does not count as an active operation. It's only "active" when the connection is actually processing something or opening or closing. Sleeping connections do not impact the governor.
We have seen any number of people describe systems that use MSDE with dozens of users without having the governor kick in while others have trouble with a couple of users. The difference?
a.. The way the connections are managed (are they held open or constantly opening and closing?
b.. How queries are written. How long does it take to execute each query. How long is the "thread" executed on the server?
c.. Are queries blocking other queries--are they fighting for resources?
You can monitor the Event log to see if the governor kicks in or hit sysprocesses (as sp_who does) and count sleeping vs active processes (as the article says).
My sources are the dev team members who implemented and support the code and 10 years experience with SQL Server.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Ken" <Ken@.discussions.microsoft.com> wrote in message news:3EFB2A8F-8F01-4850-8A06-118F2BC9F965@.microsoft.com...[vbcol=seagreen]
> I realize it is not limited by the number of connections. According to the documentation available from Microsoft the governor kicks in when there are more than 8 active operations. According to this page ( http://msdn.microsoft.com/library/en...asp?frame=true), an "active operation" includes things such as opening a connection or processing a query. I was thinking of doing a "select * from sysprocesses where status = 'runnable'" as a rough estimate of the number of active operations.
> Are you saying this information is incorrect? If so, what is correct and what are your sources? It would be great of you to share, especially if the Microsoft documentation is incorrect.
> "William (Bill) Vaughn" wrote: