Manage Learn to apply best practices and optimize your operations.

Gain insight with SQL Server dynamic management views

Dynamic management views, a feature in SQL Server 2005 and newer, let users say goodbye to complex queries for retrieving database information. In the first of this two-part series, have a look at a sampling of SQL Server DMVs and the database info they call up.

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. 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. 

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.

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.)

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.

This was last published in February 2011

Dig Deeper on Microsoft SQL Server 2008

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close