深入了解SQL Server动态管理视图

　　随着SQL Server 2005版本的发布，带来了一种新的用于访问系统和数据库信息的方式，而无需创建复杂的查询或直接访问系统表。通过使用SQL Server动态管理视图（DMVs），你可以查看SQL Server的实例信息，比如实例运行在什么系统上、实例中有那些数据库。

　　你可以在Transact-SQL语句中引用你想要查看的视图名称来调用DMVs。所有的DMVs都存放在SYS架构中，这个预定义架构包含了系统视图。它们以字符dm_开头，如sys.dm_os_hosts。

　　与其他类型的视图一样，SQL Server DMVs返回一个指定数据类型的列集合。但是，一个DMV的架构会随着SQL Server版本的变化而变化。因此，当编写调用DMV的代码时，你应该指定列名而不是使用类似SELECT * FROM view_name之类的语法。

　　SQL Server动态管理视图可分为两大类：一类是描述数据库级别的信息，另一类是描述服务器级别的信息。在本文中，我将演示如何使用前者，后者我将会在以后的文章中加以阐述。本文例子所使用的系统环境是一个创建在本地的SQL Server 2008实例。例子中引用的数据库是AdventureWorks 2008示例数据库，除了一个例子使用了SQL Server Reporting Services数据库。对于每一个例子，我都会列出语句运行的结果集。很可能你的结果集会有所不同，这取决于你在哪种系统上运行SQL Server以及如何修改数据库的。

　　与数据库相关的SQL Server DMVs

　　我们查看的第一个SQL Server DMV是sys.dm_db_partition_stats，它返回当前数据库中的分区信息，每个分区一行。（如果一个索引、一个堆或者一个没有索引的表未被分区，它被认为是一个分区。）该视图返回的信息包括分区使用的页面数量和总行数。在下面的SELECT语句中，我使用这个DMV来获取AdventureWorks 2008数据库中Production.Product表的分区信息：

　　USE AdventureWorks2008; 　　GO 　　SELECT 　　index_id AS IndexID, 　　partition_number AS PartitionNum, 　　used_page_count AS UsedPageCount, 　　row_count AS TotalRows 　　FROM 　　sys.dm_db_partition_stats 　　WHERE 　　object_id = OBJECT_ID('Production.Product') 　　ORDER BY 　　IndexID, 　　PartitionNum;

　　如语句所示，sys.dm_db_partition_stats动态管理视图返回的信息首先是索引或堆的识别号然后是分区号。换句话说，定义的分区号与特定的索引或堆相关，所以每个索引或堆可以有一个或多个分区。（这个分区号不同于数据库分配给每个分区的独一无二的分区ID。）

　　紧接着索引ID和分区号，还将返回该分区的页面总数和使用的总行数，结果如下表所示：

IndexID

ParitionNum

UsedPageCount

TotalRows

1

1

15

504

2

1

4

504

3

1

5

504

4

1

4

504

　　如上表所示，在PRODUCT表中每个索引或堆都只有一个分区，每个分区有504行。

　　接下来的动态管理视图，我会介绍sys.dm_sql_referenced_entities。该视图返回指定对象所引用的用户定义的数据库对象集合，每个对象一行。例如，如果一个存储过程引用了用户定义的表，则动态管理视图将为每个表返回一行。

　　实际上，sys.dm_sql_referenced_entities并不是一个视图，它是一个函数。在下面的SELECT语句中，我使用sys.dm_sql_referenced_entities函数来检索触发器iuPerson所引用的对象列表，如下所示：

　　USE AdventureWorks2008; 　　GO SELECT referenced_schema_name AS SchemaName, 　　referenced_entity_name AS EntityName, 　　referenced_class_desc AS ClassName 　　FROM sys.dm_sql_referenced_entities 　　('Person.iuPerson', 'OBJECT') 　　ORDER BY 　　SchemaName, 　　EntityName;

　　触发器iuPerson是Person架构的一部分，与Person表相关。当我调用该函数时，需要指定触发器名称和OBJECT参数选项。如果它是一个数据库级触发器，则需要指定DATABASE_DDL_TRIGGER参数选项。如果它是一个服务器级触发器，则需要指定SERVER_DDL_TRIGGER参数选项。下表显示SELECT语句返回的信息：

SchemaName

EntityName

ClassName

NULL

Person

OBJECT_OR_COLUMN

NULL

Person

OBJECT_OR_COLUMN

Demographics

exist

OBJECT_OR_COLUMN

Person

Person

OBJECT_OR_COLUMN

Person

Person

OBJECT_OR_COLUMN

Person

Person

OBJECT_OR_COLUMN

　　请注意，返回的是AdventureWorks 2008数据库中iuPerson触发器所引用对象的架构名称、实体名称和类别。

　　另一个你可能会发现有用的数据库管理函数是sys.dm_sql_referencing_entities，它返回数据库中的某个实体引用另一个用户定义实体的信息，每个实体一行。例如，在下面的语句中使用该函数来检索Production.ProductInventory表所引用对象的架构、实体及类别：

　　USE AdventureWorks2008; 　　GO 　　SELECT 　　referencing_schema_name AS SchemaName, 　　referencing_entity_name AS EntityName, 　　referencing_class_desc AS ClassName 　　FROM 　　sys.dm_sql_referencing_entities 　　('Production.ProductInventory', 'OBJECT') 　　ORDER BY 　　SchemaName, 　　EntityName;

　　与前面的例子中，我提供的参考对象是ProductInventory表，并使用了OBJECT参数选项。下面的结果表明，该表引用了四个用户定义的对象：

SchemaName

EntityName

ClassName

dbo

fn_inventory

OBJECT_OR_COLUMN

dbo

ufnGetStock

OBJECT_OR_COLUMN

Production

CK_ProductInventory_Bin

OBJECT_OR_COLUMN

Production

CK_ProductInventory_Shelf

OBJECT_OR_COLUMN

　　还有一个动态管理函数是sys.dm_db_index_physical_stats，它返回指定表或视图的索引和数据的大小和碎片信息。在下面的SELECT语句中，我将数据库名称和表名称作为函数的前两个参数：

　　USE AdventureWorks2008; 　　GO 　　SELECT 　　index_id AS IndexID, 　　index_type_desc AS IndexType, 　　fragment_count AS FragCount, 　　page_count AS TotalPages 　　FROM 　　sys.dm_db_index_physical_stats 　　(DB_ID('AdventureWorks2008'), OBJECT_ID('Person.Person'), 　　NULL, NULL, NULL) 　　ORDER BY 　　IndexID;

　　正如你所看到的，sys.dm_db_index_physical_stats还需要三个额外的参数，在例子中已经被指定为NULL值。这三个参数分别代表：索引ID、分区号和用于获取统计信息的扫描模式。因为我想了解表上所有索引和分区的信息，我为前两个参数指定了NULL值。关于模式，NULL值表示将使用有限的扫描模式，而不是详尽或采样扫描模式。SELECT语句返回以下结果：

IndexID

IndexType

FragCount

TotalPages

1

CLUSTERED INDEX

5

3807

2

NONCLUSTERED INDEX

2

103

3

NONCLUSTERED INDEX

2

57

256000

PRIMARY XML INDEX

1

3

256001

PRIMARY XML INDEX

7

2152

256002

XML INDEX

33

1386

256003

XML INDEX

33

1385

256004

XML INDEX

35

1386

　　返回结果包括与Person表相关的索引ID、索引类型、碎片数量和页数。

　　现在，让我们回到SQL Server动态管理视图。动态管理视图sys.dm_db_index_usage_stats返回自上一次启动SQL Server服务启动以来不同的索引操作类型的统计信息，如检索和扫描。在下面的SELECT语句中，我查看本地SQL Server实例中最近的索引操作使用的统计信息。

　　USE AdventureWorks2008; 　　GO 　　SELECT 　　OBJECT_NAME(object_id) AS ObjectName, 　　index_id AS IndexID, 　　user_seeks AS UserSeeks,< user_scans AS UserScans 　　FROM 　　sys.dm_db_index_usage_stats 　　WHERE 　　database_id = DB_ID('AdventureWorks2008') 　　ORDER BY 　　ObjectName, 　　IndexID;

　　注意，对于每个操作可查询到对象的名称、该对象对应的索引ID以及用户检索和扫描的次数。下面的结果表明，自SQL Server服务实例启动以来只有较少数量的检索和扫描：

ObjectName

IndexID

UserSeeks

UserScans

Document

1

1

0

JobCandidate

1

1

0

ProductReview

1

1

0

SalesOrderHeader

1

0

1