question

To capture this type of information in real time, you can use Dynamic Management Views (DMV) such as sys.dm_exec_requests, which will show you who is currently connected to your database. You can combine that with other DMVs to get more detailed information. To monitor this information over a period of time you would want to set up a server side trace to capture login events.

To monitor the load is a much more involved process. Unless you're interested in picking up a third party tool such as Confio Ignite or Idera Diagnostic Manager, then your best bet is to use Performance Monitor (Perfmon) and capture performance counters to a file which you can then go back and review.

For better information on how to set up server side trace or perfmon, I'd suggest getting a book. There are several available that can walk you through the basics.

To follow on from Grant's response, a very quick & dirty view of the server load over a period of time can be had by using the sp_monitor stored procedure.

Something I've done in the past is create a table to store the results of this proc and a scheduled job to run the proc every few minutes. This can then be queried to find the average load over the preceding period.

Be aware that the data available here is limited.