If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Sybase > Sybase User Connection Monitoring?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Kansas City
Posts: 18
Sybase User Connection Monitoring?

Is there a location other than Sybase Central to monitor the number of user connections. We are trying to find out when exactly the connections are peaking and what processes are causing it.

Thanks,
Mike
Reply With Quote
  #2 (permalink)  
Old
Senior Member
 
Join Date: Nov 2002
Posts: 207
Using sp_who and shell scripting you can monitor the user connections.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Kansas City
Posts: 18
I have been using sp_who to monitor this but we are actually looking for something that will show exactly when and what connections caused the problem.

Thanks for your help,
Mike
__________________
Mike R.
Reply With Quote
  #4 (permalink)  
Old
Senior Member
 
Join Date: Nov 2002
Posts: 207
What kind of problem???
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jun 2003
Posts: 140
Re: Sybase User Connection Monitoring?

You can monitor the number of user connection through isql prompt
or sqladv by running queries to master..sysprocesses


to see no of connection occupied by particular user
SELECT suser_name(suid),count(*) from master..sysprocesses
group by suid

to see no of connection occupied by particular program
SELECT program_name,count(*) from master..sysprocesses
group by program_name

Some system process are always running so you will also get Null or blank output for system processes
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Location: Switzerland
Posts: 524
Use the MDA tables since ASE 12.5.0.3 (and more especially, the tables monProcess*)
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: California
Posts: 2
Re: Sybase User Connection Monitoring?

You can execute a SQL script (select * from sysprocesses) using ISQL and have it append to a database table. Or, if you're running 12.5.0.3 or higher, you can use a select statement to copy from monProcesses to your database table.

The only disadvantage to the above approaches is (i) you'll have to write some SQL code to analyze the data to identify peak times and then output the connections open at that time, (ii) unless you purge the table from time to time, it can grow very large, (iii) it won't help you identify connections that are short in duration because it will be running at specific intervals (every minute or every X seconds). You'll miss all connections that started/ended between intervals. Lastly, (iv) depending on how busy your database server is, this could translate into additional Engine and Disk utilization as it gathers and appends this information to your database table. One other thing you might add is the ability to have it get the SQL text and save that in your database table. This would require a bit more work and possibly incur additional Engine/Disk utilization.

If you don't mind investing in a 3rd party solution, we do have a product that will do just what you are looking for which is zero-impact on your database server, provides canned reports/analysis and captures all SQL text, including those that execute quickly. Our solution is based on network sniffing, so it can be run from a different machine on the same subnet.

Hope that helps.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On