In SQL Server 2016, you will discover a lot of new Dynamic Management Views(DMVs).
In this article, I will just give you a little overview of these useful views for us as DBA.
SQL Server 2012 has 145 DMVs and SQL Server 2014 has 166 DMVs.
Now, SQL Server 2016 has 185 DMVs.
How to see it?
It is very easy to have a look using the sys.all_objects view:
SELECT * FROM sys.all_objects WHERE TYPE=’V’ AND NAME LIKE ‘dm_%’ order by name ASC
From SQL Server 2012 to SQL Server 2014, we can notice that a lot of new DMVs comes with the In-Memory technology with the syntax “dm_xtp_xxxxxxxx” or “dm_db_xtp_xxxxxxxx”
In SQL Server 2016, a lot of new “dm_exec_xxxxxxxx” is present.
All definitions for these views come from the Microsoft documentation or web site.
To begin, you will see 10 DMVs for the PolyBase technology:
- dm_exec_compute_node_errors
- Returns errors that occur on PolyBase compute nodes
- Microsoft Reference here
- dm_exec_compute_node_status
- Give information about resources of PolyBase nodes like memory, cpu, time,…
- Microsoft Reference here
- dm_exec_compute_nodes
- Returns the list of type, logical name and IP adress of PolyBase nodes
- Microsoft Reference here
- dm_exec_distributed_request_steps
- Give all steps that compose a PolyBase request
- Microsoft Reference here
- dm_exec_distributed_requests
- Give the current status of actives queries
- Microsoft Reference here
- dm_exec_distributed_sql_requests
- This view shows the data for the last 1000 requests
- Microsoft Reference here
- dm_exec_dms_services
- Give the status of the DMS (Data Movement Service) Service
- Microsoft Reference here
- dm_exec_dms_workers
- Show all workers completing DMS steps for the last 1000 queries and active queries
- Microsoft Reference here
- dm_exec_external_operations
- returns information of external PolyBase operations
- Microsoft Reference here
- dm_exec_external_work
- gives information for the workload per node
- Microsoft Reference here
A useful msdn page resumes all DMVs for these new views here
Other dm_exec_xxx views are basically usefull like:
- dm_exec_function_stats
- Returns aggregate performance statistics for cached functions.
- Microsoft Reference here
- dm_exec_query_optimizer_memory_gateways
- Returns the current status of resource semaphores used to throttle concurrent query optimization.
- Microsoft Reference here
- dm_exec_query_parallel_workers
- Returns worker availability information per node
- Microsoft Reference here
- dm_exec_session_wait_stats
- Returns information about all the waits encountered by threads that executed for each session
- Microsoft Reference here
3 new DMVs for the Columstore technology:
- dm_column_store_object_pool
- Returns counts of different types of object memory pool usage for columnstore index objects
- Microsoft Reference here
- dm_db_column_store_row_group_operational_stats
- Returns current row-level I/O, locking, and access method activity for compressed rowgroups in a columnstore index.
- Microsoft Reference here
- dm_db_column_store_row_group_physical_stats
- Provides current rowgroup-level information about all of the columnstore indexes in the current database
- Microsoft Reference here
2 new DMVs for Stretch Databases in the database context and with rda(remote database archive):
- dm_db_rda_migration_status
- To list the migration batch of the table
- See Nathan Courtine Article here
- Microsoft Reference here
- dm_db_rda_migration_status
- For the current database, list of state information of the remote data archive schema update task.
- Microsoft Reference here
This list can change if a Service Pack is applied.
It is just for you to have a little reference view about these useful views! 😎