You’ll want to use Activity Monitor (within SQL) to view the current performance of your database.
Use Activity Monitor to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server.
The Activity Monitor page has the following sections:
Overview – Shows graphical displays of the percent of processor time, number of waiting tasks, database I/O (megabytes per second), and batch requests (number per second).
Active User Tasks – Shows information for active user connections to SQL Server Database Engine.
Resource Waits – Shows wait state information.
Data File I/O – Shows I/O information for database data and log files.
Recent Expensive Queries – Shows information about the most expensive queries.
To view the Activity Monitor in SQL Server 2005 and SQL Server 2008, a user must have VIEW SERVER STATE permission.
To view the Activity Monitor on a SQL Server 2000 server, a user must have SELECT permission to the sysprocesses and syslocks tables in the master database. Permission to view these tables is granted by default to the public database role.
To KILL a process, a user must be a member of the sysadmin or processadmin fixed server roles.
To open Activity Monitor in SQL Server Management Studio
On the SQL Server Management Studio standard toolbar, click Activity Monitor.
In the Connect to Server dialog box, select the server name and authentication mode, and then click Connect.
You can also open Activity Monitor at any time by pressing CTRL+ALT A.
To open Activity Monitor in Object Explorer
In Object Explorer, right-click the instance name, and then select Activity Monitor.
To open Activity Monitor when opening SQL Server Management Studio
On the Tools menu, click Options.
In the Options dialog box, expand Environment, and then select General.
In the At startup box, select Open Object Explorer and Activity Monitor.
To activate the changes, close and reopen SQL Server Management Studio.
Original Link: http://msdn.microsoft.com/en-us/library/ms175518.aspx