你可以在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 |