If you work with SQL Server 2000, then you know how painful it is to triage a server that has "gone astray". In many cases, the error codes are undecipherable and the root cause of the problem is not easily found, let alone remedied. In fact, more times than not, a SQL 2000 DBA will simply do the unthinkable, and reboot the server.
During the development cycle for SQL Server 2005, the team at Microsoft received significant feedback – basically everyone said, "I need more visibility into what is happening inside the database." Ta-da! The Dynamic Management Views (DMVs) were born.
Many DMVs are scoped to the database level, which in itself is a big step up from SQL 2000, where all system views were at the server level. Furthermore, the data stored in DMVs is much more user-friendly than was the case for the system views, which were pretty much unusable for many people.
The core DMVs
SQL Server 2005 provides more than 80 new DMVs. Special views are provided for checking on .NET assemblies, SQL Service Broker, security, and much, much more. All DMVs include current data, and some, in addition, use hidden tables which store historical data. For example, the transaction related DMV, sys.dm_tran_top_version_generators contains historical data.
DMVs are organized into five general categories according to the area on which they report:
- sys.dm_exec_* – provide information about execution of .NET CLR Modules and connections. All contained here are a number of views available to help you drill into issues related to execution of queries.
- sys.dm_os_* – report on memory, locks, and execution scheduling.
- sys.dm_trans_* – provide insight into transactions and isolation.
- sys.dm_io_*. – monitoring disk I/O
- Sys.dm_db_* – provide database-level data.