SQL Server includes built-in objects that you can query to monitor SQL Server called Dynamic Management Views or DMV.
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
There are two types of dynamic management views and functions:
Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
You issue normal select statements against these objects.
COMMON queries:
NOTE: you can often get equivalent information using a SQL Profiler trace. If performance is an issue, querying a DMV is less resource intensive that running a SQL Profiler trace.