Hi
I have a problem with an aspnet app taking >30 seconds to
open a connection to a sql server.
Which tools could I use to monitor what connections are
open/available in the pool?
Are there any references/articles recommended using these?
Thanks
AdamIf you use OLE DB, I don't think there are any MS tools to
monitor the session pooling. If you use ODBC, you can enable
the counters for Perf Mon by following the steps in this
article:
How to Enable ODBC Connection Pooling Performance Counters
http://support.microsoft.com?id=216950
-Sue
On Wed, 13 Aug 2003 20:55:01 -0700, "adam" <adam@.twv.org>
wrote:
>Hi
>I have a problem with an aspnet app taking >30 seconds to
>open a connection to a sql server.
>Which tools could I use to monitor what connections are
>open/available in the pool?
>Are there any references/articles recommended using these?
>Thanks
>Adam|||Unfortunately I am using OLEDB from a C# web app
>--Original Message--
>If you use OLE DB, I don't think there are any MS tools
to
>monitor the session pooling. If you use ODBC, you can
enable
>the counters for Perf Mon by following the steps in this
>article:
>How to Enable ODBC Connection Pooling Performance
Counters
>http://support.microsoft.com?id=216950
>-Sue
>On Wed, 13 Aug 2003 20:55:01 -0700, "adam" <adam@.twv.org>
>wrote:
>>Hi
>>I have a problem with an aspnet app taking >30 seconds
to
>>open a connection to a sql server.
>>Which tools could I use to monitor what connections are
>>open/available in the pool?
>>Are there any references/articles recommended using
these?
>>Thanks
>>Adam
>.
>|||The problem seems to be that if a connection is request
within 2-3 minutes of the previous one, it is returned
instantly (presumably from the pool). But if it has been
more than this time, it takes 30 seconds to get one. I am
assured that the SqlServer cluster is not overloaded and
none of the other apps accessing it are having problems.
When run the same timing code on my development laptop it
obtains a connection (from a sqlserver on the same box)
in 0ms. When the same laptop tries to get one from the
production SqlServer, it takes about 32s, which leads me
to think it is a problem with the production server.
At the moment I have had to hack it so that the web app
makes a small request to the webserver every 2 mins, just
to keep a connection in the pool, so at least there is
always one connection available quickly, but this is
clearly not ideal.
How can I further elucidate the nature of this problem,
and why the server is taking so long to return a
connection.
Adam
>Hi
>I have a problem with an aspnet app taking >30 seconds
to
>open a connection to a sql server.
>
>Adam
>.
>
Showing posts with label connections. Show all posts
Showing posts with label connections. Show all posts
Friday, March 9, 2012
Monitoring connections
I have been ask to monito the connections on an MSSQL2000 box. If the
connections are greater than 50, this gentlemen would like an email. Any
suggestions on how I can accomplish this? Can I use an Alert? If so, could
you point me in the direction of how to implement it?
Thanks,
Jeff
Jeff wrote:
> I have been ask to monito the connections on an MSSQL2000 box. If the
> connections are greater than 50, this gentlemen would like an email.
> Any suggestions on how I can accomplish this? Can I use an Alert?
> If so, could you point me in the direction of how to implement it?
> Thanks,
> Jeff
You can easily define the alert from SQL EM (SQL Server Agent needs to
be running). Here's the SQL to do it manually. THe easiest way to do
this is from SQL EM and then script out the T-SQL and modify
accordingly.
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Over50'))
-- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
@.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
@.notification_message = N'SQL Server now has over 50 connections',
@.performance_condition = N'SQLServer:General Statistics|User
Connections||>|50', @.include_event_description_in = 5, @.category_name =
N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
@.operator_name = N'Operator Name', @.notification_method = 1
END
David Gugick
Imceda Software
www.imceda.com
|||First, thanks for the help. I do have another question that I just can't
seem to figure out. I get this error message when executing the code:
Server: Msg 14262, Level 16, State 1, Procedure
sp_verify_performance_condition, Line 46
The specified object_name ('SQLServer:General Statistics') does not exist.
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_notification, Line
26
The specified @.alert_name ('Over50') does not exist.
Any idea what is wrong?
Thanks,
Jeff
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ONEhUiDFFHA.2452@.TK2MSFTNGP09.phx.gbl...
> Jeff wrote:
> You can easily define the alert from SQL EM (SQL Server Agent needs to
> be running). Here's the SQL to do it manually. THe easiest way to do
> this is from SQL EM and then script out the T-SQL and modify
> accordingly.
> IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Over50'))
> -- Delete the alert with the same name.
> EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
> BEGIN
> EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
> @.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
> @.notification_message = N'SQL Server now has over 50 connections',
> @.performance_condition = N'SQLServer:General Statistics|User
> Connections||>|50', @.include_event_description_in = 5, @.category_name =
> N'[Uncategorized]'
> EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
> @.operator_name = N'Operator Name', @.notification_method = 1
> END
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Jeff wrote:[vbcol=seagreen]
> First, thanks for the help. I do have another question that I just
> can't seem to figure out. I get this error message when executing
> the code:
> Server: Msg 14262, Level 16, State 1, Procedure
> sp_verify_performance_condition, Line 46
> The specified object_name ('SQLServer:General Statistics') does not
> exist. Server: Msg 14262, Level 16, State 1, Procedure
> sp_verify_notification, Line 26
> The specified @.alert_name ('Over50') does not exist.
> Any idea what is wrong?
> Thanks,
> Jeff
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:ONEhUiDFFHA.2452@.TK2MSFTNGP09.phx.gbl...
Create the alert in SQL EM yourself and then script it out. The code I
sent may be somehow linked to my setup. Make sure the SQL Server Agent
is running. If your performance counters are missing, you'll probably
see this from SQL EM.
David Gugick
Imceda Software
www.imceda.com
connections are greater than 50, this gentlemen would like an email. Any
suggestions on how I can accomplish this? Can I use an Alert? If so, could
you point me in the direction of how to implement it?
Thanks,
Jeff
Jeff wrote:
> I have been ask to monito the connections on an MSSQL2000 box. If the
> connections are greater than 50, this gentlemen would like an email.
> Any suggestions on how I can accomplish this? Can I use an Alert?
> If so, could you point me in the direction of how to implement it?
> Thanks,
> Jeff
You can easily define the alert from SQL EM (SQL Server Agent needs to
be running). Here's the SQL to do it manually. THe easiest way to do
this is from SQL EM and then script out the T-SQL and modify
accordingly.
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Over50'))
-- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
@.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
@.notification_message = N'SQL Server now has over 50 connections',
@.performance_condition = N'SQLServer:General Statistics|User
Connections||>|50', @.include_event_description_in = 5, @.category_name =
N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
@.operator_name = N'Operator Name', @.notification_method = 1
END
David Gugick
Imceda Software
www.imceda.com
|||First, thanks for the help. I do have another question that I just can't
seem to figure out. I get this error message when executing the code:
Server: Msg 14262, Level 16, State 1, Procedure
sp_verify_performance_condition, Line 46
The specified object_name ('SQLServer:General Statistics') does not exist.
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_notification, Line
26
The specified @.alert_name ('Over50') does not exist.
Any idea what is wrong?
Thanks,
Jeff
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ONEhUiDFFHA.2452@.TK2MSFTNGP09.phx.gbl...
> Jeff wrote:
> You can easily define the alert from SQL EM (SQL Server Agent needs to
> be running). Here's the SQL to do it manually. THe easiest way to do
> this is from SQL EM and then script out the T-SQL and modify
> accordingly.
> IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Over50'))
> -- Delete the alert with the same name.
> EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
> BEGIN
> EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
> @.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
> @.notification_message = N'SQL Server now has over 50 connections',
> @.performance_condition = N'SQLServer:General Statistics|User
> Connections||>|50', @.include_event_description_in = 5, @.category_name =
> N'[Uncategorized]'
> EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
> @.operator_name = N'Operator Name', @.notification_method = 1
> END
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Jeff wrote:[vbcol=seagreen]
> First, thanks for the help. I do have another question that I just
> can't seem to figure out. I get this error message when executing
> the code:
> Server: Msg 14262, Level 16, State 1, Procedure
> sp_verify_performance_condition, Line 46
> The specified object_name ('SQLServer:General Statistics') does not
> exist. Server: Msg 14262, Level 16, State 1, Procedure
> sp_verify_notification, Line 26
> The specified @.alert_name ('Over50') does not exist.
> Any idea what is wrong?
> Thanks,
> Jeff
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:ONEhUiDFFHA.2452@.TK2MSFTNGP09.phx.gbl...
Create the alert in SQL EM yourself and then script it out. The code I
sent may be somehow linked to my setup. Make sure the SQL Server Agent
is running. If your performance counters are missing, you'll probably
see this from SQL EM.
David Gugick
Imceda Software
www.imceda.com
Monitoring connections
I have been ask to monito the connections on an MSSQL2000 box. If the
connections are greater than 50, this gentlemen would like an email. Any
suggestions on how I can accomplish this? Can I use an Alert? If so, could
you point me in the direction of how to implement it?
Thanks,
JeffJeff wrote:
> I have been ask to monito the connections on an MSSQL2000 box. If the
> connections are greater than 50, this gentlemen would like an email.
> Any suggestions on how I can accomplish this? Can I use an Alert?
> If so, could you point me in the direction of how to implement it?
> Thanks,
> Jeff
You can easily define the alert from SQL EM (SQL Server Agent needs to
be running). Here's the SQL to do it manually. THe easiest way to do
this is from SQL EM and then script out the T-SQL and modify
accordingly.
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Over50'))
-- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
@.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
@.notification_message = N'SQL Server now has over 50 connections',
@.performance_condition = N'SQLServer:General Statistics|User
Connections||>|50', @.include_event_description_in = 5, @.category_name =
N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
@.operator_name = N'Operator Name', @.notification_method = 1
END
David Gugick
Imceda Software
www.imceda.com|||First, thanks for the help. I do have another question that I just can't
seem to figure out. I get this error message when executing the code:
Server: Msg 14262, Level 16, State 1, Procedure
sp_verify_performance_condition, Line 46
The specified object_name ('SQLServer:General Statistics') does not exist.
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_notification, Line
26
The specified @.alert_name ('Over50') does not exist.
Any idea what is wrong?
Thanks,
Jeff
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ONEhUiDFFHA.2452@.TK2MSFTNGP09.phx.gbl...
> Jeff wrote:
> You can easily define the alert from SQL EM (SQL Server Agent needs to
> be running). Here's the SQL to do it manually. THe easiest way to do
> this is from SQL EM and then script out the T-SQL and modify
> accordingly.
> IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Over50'))
> -- Delete the alert with the same name.
> EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
> BEGIN
> EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
> @.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
> @.notification_message = N'SQL Server now has over 50 connections',
> @.performance_condition = N'SQLServer:General Statistics|User
> Connections||>|50', @.include_event_description_in = 5, @.category_name =
> N'[Uncategorized]'
> EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
> @.operator_name = N'Operator Name', @.notification_method = 1
> END
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Jeff wrote:[vbcol=seagreen]
> First, thanks for the help. I do have another question that I just
> can't seem to figure out. I get this error message when executing
> the code:
> Server: Msg 14262, Level 16, State 1, Procedure
> sp_verify_performance_condition, Line 46
> The specified object_name ('SQLServer:General Statistics') does not
> exist. Server: Msg 14262, Level 16, State 1, Procedure
> sp_verify_notification, Line 26
> The specified @.alert_name ('Over50') does not exist.
> Any idea what is wrong?
> Thanks,
> Jeff
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:ONEhUiDFFHA.2452@.TK2MSFTNGP09.phx.gbl...
Create the alert in SQL EM yourself and then script it out. The code I
sent may be somehow linked to my setup. Make sure the SQL Server Agent
is running. If your performance counters are missing, you'll probably
see this from SQL EM.
David Gugick
Imceda Software
www.imceda.com
connections are greater than 50, this gentlemen would like an email. Any
suggestions on how I can accomplish this? Can I use an Alert? If so, could
you point me in the direction of how to implement it?
Thanks,
JeffJeff wrote:
> I have been ask to monito the connections on an MSSQL2000 box. If the
> connections are greater than 50, this gentlemen would like an email.
> Any suggestions on how I can accomplish this? Can I use an Alert?
> If so, could you point me in the direction of how to implement it?
> Thanks,
> Jeff
You can easily define the alert from SQL EM (SQL Server Agent needs to
be running). Here's the SQL to do it manually. THe easiest way to do
this is from SQL EM and then script out the T-SQL and modify
accordingly.
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Over50'))
-- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
@.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
@.notification_message = N'SQL Server now has over 50 connections',
@.performance_condition = N'SQLServer:General Statistics|User
Connections||>|50', @.include_event_description_in = 5, @.category_name =
N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
@.operator_name = N'Operator Name', @.notification_method = 1
END
David Gugick
Imceda Software
www.imceda.com|||First, thanks for the help. I do have another question that I just can't
seem to figure out. I get this error message when executing the code:
Server: Msg 14262, Level 16, State 1, Procedure
sp_verify_performance_condition, Line 46
The specified object_name ('SQLServer:General Statistics') does not exist.
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_notification, Line
26
The specified @.alert_name ('Over50') does not exist.
Any idea what is wrong?
Thanks,
Jeff
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ONEhUiDFFHA.2452@.TK2MSFTNGP09.phx.gbl...
> Jeff wrote:
> You can easily define the alert from SQL EM (SQL Server Agent needs to
> be running). Here's the SQL to do it manually. THe easiest way to do
> this is from SQL EM and then script out the T-SQL and modify
> accordingly.
> IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Over50'))
> -- Delete the alert with the same name.
> EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
> BEGIN
> EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
> @.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
> @.notification_message = N'SQL Server now has over 50 connections',
> @.performance_condition = N'SQLServer:General Statistics|User
> Connections||>|50', @.include_event_description_in = 5, @.category_name =
> N'[Uncategorized]'
> EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
> @.operator_name = N'Operator Name', @.notification_method = 1
> END
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Jeff wrote:[vbcol=seagreen]
> First, thanks for the help. I do have another question that I just
> can't seem to figure out. I get this error message when executing
> the code:
> Server: Msg 14262, Level 16, State 1, Procedure
> sp_verify_performance_condition, Line 46
> The specified object_name ('SQLServer:General Statistics') does not
> exist. Server: Msg 14262, Level 16, State 1, Procedure
> sp_verify_notification, Line 26
> The specified @.alert_name ('Over50') does not exist.
> Any idea what is wrong?
> Thanks,
> Jeff
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:ONEhUiDFFHA.2452@.TK2MSFTNGP09.phx.gbl...
Create the alert in SQL EM yourself and then script it out. The code I
sent may be somehow linked to my setup. Make sure the SQL Server Agent
is running. If your performance counters are missing, you'll probably
see this from SQL EM.
David Gugick
Imceda Software
www.imceda.com
Monitoring connections
I have been ask to monito the connections on an MSSQL2000 box. If the
connections are greater than 50, this gentlemen would like an email. Any
suggestions on how I can accomplish this? Can I use an Alert? If so, could
you point me in the direction of how to implement it?
Thanks,
JeffJeff wrote:
> I have been ask to monito the connections on an MSSQL2000 box. If the
> connections are greater than 50, this gentlemen would like an email.
> Any suggestions on how I can accomplish this? Can I use an Alert?
> If so, could you point me in the direction of how to implement it?
> Thanks,
> Jeff
You can easily define the alert from SQL EM (SQL Server Agent needs to
be running). Here's the SQL to do it manually. THe easiest way to do
this is from SQL EM and then script out the T-SQL and modify
accordingly.
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Over50'))
-- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
@.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
@.notification_message = N'SQL Server now has over 50 connections',
@.performance_condition = N'SQLServer:General Statistics|User
Connections||>|50', @.include_event_description_in = 5, @.category_name =N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
@.operator_name = N'Operator Name', @.notification_method = 1
END
David Gugick
Imceda Software
www.imceda.com|||First, thanks for the help. I do have another question that I just can't
seem to figure out. I get this error message when executing the code:
Server: Msg 14262, Level 16, State 1, Procedure
sp_verify_performance_condition, Line 46
The specified object_name ('SQLServer:General Statistics') does not exist.
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_notification, Line
26
The specified @.alert_name ('Over50') does not exist.
Any idea what is wrong?
Thanks,
Jeff
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ONEhUiDFFHA.2452@.TK2MSFTNGP09.phx.gbl...
> Jeff wrote:
> > I have been ask to monito the connections on an MSSQL2000 box. If the
> > connections are greater than 50, this gentlemen would like an email.
> > Any suggestions on how I can accomplish this? Can I use an Alert?
> > If so, could you point me in the direction of how to implement it?
> >
> > Thanks,
> > Jeff
> You can easily define the alert from SQL EM (SQL Server Agent needs to
> be running). Here's the SQL to do it manually. THe easiest way to do
> this is from SQL EM and then script out the T-SQL and modify
> accordingly.
> IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Over50'))
> -- Delete the alert with the same name.
> EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
> BEGIN
> EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
> @.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
> @.notification_message = N'SQL Server now has over 50 connections',
> @.performance_condition = N'SQLServer:General Statistics|User
> Connections||>|50', @.include_event_description_in = 5, @.category_name => N'[Uncategorized]'
> EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
> @.operator_name = N'Operator Name', @.notification_method = 1
> END
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Jeff wrote:
> First, thanks for the help. I do have another question that I just
> can't seem to figure out. I get this error message when executing
> the code:
> Server: Msg 14262, Level 16, State 1, Procedure
> sp_verify_performance_condition, Line 46
> The specified object_name ('SQLServer:General Statistics') does not
> exist. Server: Msg 14262, Level 16, State 1, Procedure
> sp_verify_notification, Line 26
> The specified @.alert_name ('Over50') does not exist.
> Any idea what is wrong?
> Thanks,
> Jeff
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:ONEhUiDFFHA.2452@.TK2MSFTNGP09.phx.gbl...
>> Jeff wrote:
>> I have been ask to monito the connections on an MSSQL2000 box. If
>> the connections are greater than 50, this gentlemen would like an
>> email. Any suggestions on how I can accomplish this? Can I use an
>> Alert?
>> If so, could you point me in the direction of how to implement it?
>> Thanks,
>> Jeff
>> You can easily define the alert from SQL EM (SQL Server Agent needs
>> to be running). Here's the SQL to do it manually. THe easiest way to
>> do this is from SQL EM and then script out the T-SQL and modify
>> accordingly.
>> IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name =>> N'Over50')) -- Delete the alert with the same name.
>> EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
>> BEGIN
>> EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
>> @.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
>> @.notification_message = N'SQL Server now has over 50 connections',
>> @.performance_condition = N'SQLServer:General Statistics|User
>> Connections||>|50', @.include_event_description_in = 5,
>> @.category_name = N'[Uncategorized]'
>> EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
>> @.operator_name = N'Operator Name', @.notification_method = 1
>> END
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
Create the alert in SQL EM yourself and then script it out. The code I
sent may be somehow linked to my setup. Make sure the SQL Server Agent
is running. If your performance counters are missing, you'll probably
see this from SQL EM.
--
David Gugick
Imceda Software
www.imceda.com
connections are greater than 50, this gentlemen would like an email. Any
suggestions on how I can accomplish this? Can I use an Alert? If so, could
you point me in the direction of how to implement it?
Thanks,
JeffJeff wrote:
> I have been ask to monito the connections on an MSSQL2000 box. If the
> connections are greater than 50, this gentlemen would like an email.
> Any suggestions on how I can accomplish this? Can I use an Alert?
> If so, could you point me in the direction of how to implement it?
> Thanks,
> Jeff
You can easily define the alert from SQL EM (SQL Server Agent needs to
be running). Here's the SQL to do it manually. THe easiest way to do
this is from SQL EM and then script out the T-SQL and modify
accordingly.
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Over50'))
-- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
@.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
@.notification_message = N'SQL Server now has over 50 connections',
@.performance_condition = N'SQLServer:General Statistics|User
Connections||>|50', @.include_event_description_in = 5, @.category_name =N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
@.operator_name = N'Operator Name', @.notification_method = 1
END
David Gugick
Imceda Software
www.imceda.com|||First, thanks for the help. I do have another question that I just can't
seem to figure out. I get this error message when executing the code:
Server: Msg 14262, Level 16, State 1, Procedure
sp_verify_performance_condition, Line 46
The specified object_name ('SQLServer:General Statistics') does not exist.
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_notification, Line
26
The specified @.alert_name ('Over50') does not exist.
Any idea what is wrong?
Thanks,
Jeff
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ONEhUiDFFHA.2452@.TK2MSFTNGP09.phx.gbl...
> Jeff wrote:
> > I have been ask to monito the connections on an MSSQL2000 box. If the
> > connections are greater than 50, this gentlemen would like an email.
> > Any suggestions on how I can accomplish this? Can I use an Alert?
> > If so, could you point me in the direction of how to implement it?
> >
> > Thanks,
> > Jeff
> You can easily define the alert from SQL EM (SQL Server Agent needs to
> be running). Here's the SQL to do it manually. THe easiest way to do
> this is from SQL EM and then script out the T-SQL and modify
> accordingly.
> IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Over50'))
> -- Delete the alert with the same name.
> EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
> BEGIN
> EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
> @.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
> @.notification_message = N'SQL Server now has over 50 connections',
> @.performance_condition = N'SQLServer:General Statistics|User
> Connections||>|50', @.include_event_description_in = 5, @.category_name => N'[Uncategorized]'
> EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
> @.operator_name = N'Operator Name', @.notification_method = 1
> END
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Jeff wrote:
> First, thanks for the help. I do have another question that I just
> can't seem to figure out. I get this error message when executing
> the code:
> Server: Msg 14262, Level 16, State 1, Procedure
> sp_verify_performance_condition, Line 46
> The specified object_name ('SQLServer:General Statistics') does not
> exist. Server: Msg 14262, Level 16, State 1, Procedure
> sp_verify_notification, Line 26
> The specified @.alert_name ('Over50') does not exist.
> Any idea what is wrong?
> Thanks,
> Jeff
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:ONEhUiDFFHA.2452@.TK2MSFTNGP09.phx.gbl...
>> Jeff wrote:
>> I have been ask to monito the connections on an MSSQL2000 box. If
>> the connections are greater than 50, this gentlemen would like an
>> email. Any suggestions on how I can accomplish this? Can I use an
>> Alert?
>> If so, could you point me in the direction of how to implement it?
>> Thanks,
>> Jeff
>> You can easily define the alert from SQL EM (SQL Server Agent needs
>> to be running). Here's the SQL to do it manually. THe easiest way to
>> do this is from SQL EM and then script out the T-SQL and modify
>> accordingly.
>> IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name =>> N'Over50')) -- Delete the alert with the same name.
>> EXECUTE msdb.dbo.sp_delete_alert @.name = N'Over50'
>> BEGIN
>> EXECUTE msdb.dbo.sp_add_alert @.name = N'Over50', @.message_id = 0,
>> @.severity = 0, @.enabled = 1, @.delay_between_responses = 1800,
>> @.notification_message = N'SQL Server now has over 50 connections',
>> @.performance_condition = N'SQLServer:General Statistics|User
>> Connections||>|50', @.include_event_description_in = 5,
>> @.category_name = N'[Uncategorized]'
>> EXECUTE msdb.dbo.sp_add_notification @.alert_name = N'Over50',
>> @.operator_name = N'Operator Name', @.notification_method = 1
>> END
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
Create the alert in SQL EM yourself and then script it out. The code I
sent may be somehow linked to my setup. Make sure the SQL Server Agent
is running. If your performance counters are missing, you'll probably
see this from SQL EM.
--
David Gugick
Imceda Software
www.imceda.com
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.
Sysprocesses gives you informaton about all the connections on your sql
server at the moment. You can run this in a loop and direct it to an output
file. You can also choose the columns you want.
Something like --
while (1=1)
begin
select getdate()
select * from master..sysprocesses
waitfor delay '00:00:15'
end
-- save above to .sql file and run from cmd line as
osql -Usa -P<password> -i<filenameabove> -o<outputfilename>
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Sysprocesses gives you informaton about all the connections on your sql
server at the moment. You can run this in a loop and direct it to an output
file. You can also choose the columns you want.
Something like --
while (1=1)
begin
select getdate()
select * from master..sysprocesses
waitfor delay '00:00:15'
end
-- save above to .sql file and run from cmd line as
osql -Usa -P<password> -i<filenameabove> -o<outputfilename>
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
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.
Sysprocesses gives you informaton about all the connections on your sql
server at the moment. You can run this in a loop and direct it to an output
file. You can also choose the columns you want.
Something like --
while (1=1)
begin
select getdate()
select * from master..sysprocesses
waitfor delay '00:00:15'
end
-- save above to .sql file and run from cmd line as
osql -Usa -P<password> -i<filenameabove> -o<outputfilename>
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Sysprocesses gives you informaton about all the connections on your sql
server at the moment. You can run this in a loop and direct it to an output
file. You can also choose the columns you want.
Something like --
while (1=1)
begin
select getdate()
select * from master..sysprocesses
waitfor delay '00:00:15'
end
-- save above to .sql file and run from cmd line as
osql -Usa -P<password> -i<filenameabove> -o<outputfilename>
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
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:
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:
Saturday, February 25, 2012
Monitor Connection sessions etc.
Hey all!
i've got SQL 2000 on a server with 1GB ram. JSP & CF code is accessing this server & DB's. We are finding that a lot of connections are staying persistant for a long time (especially with the JAVA Apps).
I'm currently able to monitor this using sp_who2, performance monitor and the following SQL call:
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)')
My questions are:
Is there a better way to monitor this?
Is there a way to terminate a session and how can i tell it's idle time?
Any performance tweaks you can offer as well would be MOST appreciated.another note here. i came into work this morning and had 108 connections to the SQL server. and it was useing abou 300KB of mem. currently it's at 103 connections and 2920 KB of mem. I've got a dual processor system with 1 GB of ram and my applications are dragging.. it only gets this way when my connections get above 100. any ideas?
i've got SQL 2000 on a server with 1GB ram. JSP & CF code is accessing this server & DB's. We are finding that a lot of connections are staying persistant for a long time (especially with the JAVA Apps).
I'm currently able to monitor this using sp_who2, performance monitor and the following SQL call:
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)')
My questions are:
Is there a better way to monitor this?
Is there a way to terminate a session and how can i tell it's idle time?
Any performance tweaks you can offer as well would be MOST appreciated.another note here. i came into work this morning and had 108 connections to the SQL server. and it was useing abou 300KB of mem. currently it's at 103 connections and 2920 KB of mem. I've got a dual processor system with 1 GB of ram and my applications are dragging.. it only gets this way when my connections get above 100. any ideas?
Subscribe to:
Posts (Atom)