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...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.
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.
Dig Deeper
PRO+
Content
Find more PRO+ content and other member only offers, here.
0 comments
Oldest Newest