Monitoring with Transact-SQL Statements
Microsoft SQL Server provides several Transact-SQL statements and system stored procedures that allow ad hoc monitoring of an instance of SQL Server. Use these statements when you want to quickly gather specific information about server performance and activity.
For example, you can use Transact-SQL statements and system stored procedures to view the following types of information about your server:
Current locks.
Current user activity.
Last command batch submitted by a user.
Data space used by a table or database.
Space used by a transaction log.
Oldest active transaction (including replicated transactions) in the database.
Performance information relating to I/O, memory, and network throughput.
Procedure cache usage.
General statistics about SQL Server activity and usage, such as the amount of time the CPU has been performing SQL Server operations or the amount of time SQL Server has spent performing I/O operations.
Most of this information can also be monitored using SQL Server Management Studio, SQL Server Management Objects (SMO), or System Monitor (Performance Monitor in Microsoft Windows NT 4.0).