Learn about SQL Server 20,055 useful dynamic management Objects

The new features of SQL Server 2005 are dynamic management objects, which are database views or functions that return special state information for a database instance at a specified time. These objects allow database administrators or developers to efficiently monitor the database server. Dynamic management Objects Replace many of the system tables in previous versions of SQL Server, but also provide more observational tools for database administrators.

The content that needs to be monitored on the server, almost all have corresponding dynamic management object. Since there are so many dynamic management objects, it is impractical to discuss one by one in this article. For example, there are 12 types of dynamic management objects, but here I'll focus on the five objects that I think are pretty important, from the classes that are related to the SQL Server operating system, directory, and execution.

These objects are grouped into two categories: database level and server level. The security architecture of SQL Server 2005 is somewhat different, so you must take some steps to ensure that you can use the views and functions discussed in this article. You must make sure that users who use these examples can view the object's server state and database state. You can do this by applying the GRANT statement.

Before I begin to introduce dynamic management objects, you should create a table that can be used in all examples. List A is the code that creates the table.

List A

IF OBJECT_ID('DMVTest') IS NOT NULL
DROP TABLE DMVTest
GO
CREATE TABLE DMVTest
(
C1 INT IDENTITY PRIMARY KEY CLUSTERED,
C2 INT,
C3 CHAR(5)
)

SQL Server operating system-related dynamic management objects

Sys.dm_os_performance_counters: This view returns information that enumerates performance statistics that are directly related to SQL Server. The returned information represents the external performance counters that are displayed through the Windows Performance Monitor. In SQL Server 2000, this data is represented by a system table called Sysperfinfo, which is also available in SQL Server 2005, but only for backward compatibility. Future versions may delete this table.