How to list active session on sql server?

[Sina]

Hello everyone,

couple of questions here for sql server 7.0 and 2000.

1. How to list session on sql server?
2. How to list connected computer (clients) on sql server?
3. How can I list total sql processes?
4. How can I list system sql processes?
5. How can I list user sql processes?
7. How can I report on the memory the sql server is utilizing?
(total memory?, Max Memory?, Buffer Cache?, Procedure Cache?)

8. Size of the file groups? and or devices?

9. Size of the log files and its growth over time?

How can I find bottle necks? i.e what are the things I can monitor and gather data from to improve my SQL Server.

Thank you all

 

[TomKane]

The sysprocesses table has a lot of what you want. The table is in the master database and you can do a simple select * from sysprocesses or you can do a sp_who. You can also see the information from the activity monitor within the enterprise manager.

I think what you get out of that should cover points 1-5.

For point 7 you can use the NT or the SQL Server performance monitor it shows graphs of one type of utilisation against another.

8-9 can be viewed through enterprise manager also.

As for bottle necks you can look at the performance monitor. You to monitor sql staements and how long they take to process. Query analyser has a utility that will allow you to see if there are table scans in your queries (which tend to slow things down) and may be able to point you in the direction of whether or not you need to use clustered indexes or something like that.

 

[Sina]

Thanks much for the hint.

As for the other points, I like to get the data from sql statemetn to play with it and desing a web page with graph so that I can monitor it easily.

Thanks