Gain insight with SQL Server dynamic management views

With the release of SQL Server 2005 came a new way to access system and database information without having to create complex queries or access system tables directly.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at [email protected].

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in February 2011

By using SQL Server dynamic management views (DMVs), you can retrieve information about an instance of SQL Server, the system on which that instance runs and specific databases stored on the instance. 

For more on dynamic management views and other SQL Server features

Learn to use dynamic management views to monitor, tune SQL Server performance

Read about catalog views, dynamic management views in SQL Server 2005

Take an in-depth look at SQL Server catalog views and what they’re good for

You can call DMVs within Transact-SQL statements by referencing the name of the view as you would any other type of view. All DMVs exist in the sys schema, the predefined structure that contains system views. They begin with the characters dm_, as in sys.dm_os_hosts.

Like any type of view, SQL Server DMVs return a set of columns specific to the type of data they retrieve. However, a DMV’s schema can change from one version of SQL Server to another. As a result, whenever you write code that calls a DMV, you should specify the column names rather than use the SELECT * FROM view_name convention.

Views and functions

For the purposes of this article, I include dynamic management functions in my discussion of SQL Server DMVs. The primary difference between the two, from a user’s perspective, is that you must supply one or more arguments to a function, whereas a view requires no arguments. In addition, you’ll often find that when SQL Server documentation or database developers discuss DMVs, they are also referring to the functions.

SQL Server dynamic management views can be divided into two general categories -- those specific to one or more databases and those that operate at the server level. In this article, I’ll demonstrate how to use the former; the latter I’ll tackle in a subsequent article. The examples I provide were created on a local instance of SQL Server 2008. When a specific database was referenced, I used the AdventureWorks 2008 sample database, except in one instance in which I used the SQL Server Reporting Services database. For each example, I include the result set I received when I ran the statement. Your results will most likely be different, depending on the system on which you’re running SQL Server and how you might have modified the databases.

Working with database-related SQL Server DMVs

The first SQL Server DMV we’ll look at is sys.dm_db_partition_stats, which returns a row for each partition in the current database. (If an index or heap, an unindexed table, is not partitioned, it is considered a single partition.) The information returned by the view includes details such as the page and row counts used by the partition. In the following SELECT statement, I use the DMV to retrieve partition information about the Production.Product table in the AdventureWorks 2008 database:

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;

As the statement indicates, the sys.dm_db_partition_stats DMV returns the information based first on the identification of the index or heap and then on the partition number. In other words, the partition numbers are defined within the context of a specific index or a specific heap, so each index or heap can have one or more partitions. (The partition number is different from the unique partition ID assigned to each partition in a database.)

More on columns

Notice that in the first example, as well as in subsequent examples, I specify the name of the columns whose data I want to view. You can find a description of each column that a view or function returns by referring to its help topic in SQL Server Books Online. Where applicable, the topics also include details about the arguments that can be passed into functions.

Along with the index ID and partition number, the statement also returns the total number of pages and rows used by the partition, as shown in the following results:

IndexID

ParitionNum

UsedPageCount

TotalRows

1

1

15

504

2

1

4

504

3

1

5

504

4

1

4

504

 

As shown in the table, there is only one partition per index or heap in the product table, and each partition has 504 rows.

The next DMV that I’ll review is sys.dm_sql_referenced_entities. This view returns a row for each user-defined database object referenced within a specified object. For instance, if a stored procedure references user-defined tables, the DMV will return a row for each one of those tables.

Actually, sys.dm_sql_referenced_entities is not a view; it’s a function (see “Views and functions” above). In the following SELECT statement, I use the sys.dm_sql_referenced_entities function to retrieve a list of objects referenced by the iuPerson trigger, as shown here:

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;

The iuPerson trigger is part of the Person schema and is associated with the Person table in that schema. When I call the function, I include the name of the trigger and I specify the OBJECT argument. If it were a database trigger, I would specify the DATABASE_DDL_TRIGGER argument. If it were a server trigger, I would specify SERVER_DDL_TRIGGER. The following table shows the information returned by the SELECT statement:

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

Notice that I’ve retrieved the names of the schema, entity, and class type for each object referenced by the iuPerson trigger in the AdventureWorks 2008 database.

Another database management function you might find useful is sys.dm_sql_referencing_entities, which returns one row for each entity in the database that references another user-defined entity. For example, in the following statement I use the function to retrieve the names of the schema, entity and class type of each object referenced by the Production.ProductInventory table:

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;

As with the previous example, I supply the name of the referencing object (the ProductInventory table) and the OBJECT keyword as arguments to the function. The following results show that the table references four user-defined objects:

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

Another dynamic management function is sys.dm_db_index_physical_stats, which returns size and fragmentation information related to the indexes and data of a specified table or view. In the following SELECT statement, I supply the database and table as the first two arguments to the function:

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;

As you can see, the sys.dm_db_index_physical_stats takes three additional arguments, which I’ve specified as NULL. The first of these three is the index ID, the second is the partition number, and the third, and last, is the mode, which specifies the scan level used to obtain the statistics. Because I want information about all indexes and partitions on the table, I specified NULL in those two cases. With regard to the mode, a NULL indicates that only a limited scan level will be used, as opposed to detailed or sampled scan. The SELECT statement returns the following results:

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

As the results indicate, I retrieved the index ID, index type, fragment count and page count for each index associated with the Person table.

Now let’s return to SQL Server dynamic management views. The sys.dm_db_index_usage_stats DMV returns the counts of different types of index operations, such as seeks and scans, that have occurred since the last time the SQL Server service was started. In the following SELECT statement, I retrieve index usage statistics for recent operations on my local instance of SQL Server.

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

Notice that for each operation, I retrieve the object name, the applicable index ID for that object, and the number of user seeks and scans. The following results indicate that there have been relatively few seeks and scans since starting my instance of the SQL Server service (only a short time earlier):

ObjectName

IndexID

UserSeeks

UserScans

Document

1

1

0

JobCandidate

1

1

0

ProductReview

1

1

0

SalesOrderHeader

1

0

1

That’s all the database-related DMVs I’ll cover here, but remember, there are many more and they return a wide range of information. Be sure to check back soon for my article on server-related DMVs.

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at http://rhsheldon.com.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top