What is DMV ?

“DMVs” are query structures built into SQL Server that deliver details about server and database health/performance. DMVs provide a common mechanism to extract “all things SQL” as well as Windows OS performance data

  • Dynamic management views and functions return internal, implementation-specific state data.
  • Their schema and the data they return may change in future releases of SQL Server.
  • Dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release. For example, in future releases of SQL Server, Microsoft may augment the definition of any dynamic management view by adding columns to the end of the column list.
  • Recommend against using the syntax SELECT * FROM dynamic_management_view_name in production code because the number of columns returned might change and break your application.

DMV02

Microsoft has introduce DMV’s from SQL Server 2012 to SQL Server 2014,  with the syntax “dm_xtp_xxxxxxxx” or “dm_db_xtp_xxxxxxxx”.All definitions for these views come from the Microsoft documentation or web site.

  • sys.dm_server_memory_dumps
    • The dump type may be a minidump, all-thread dump, or a full dump. The files have an extension of .mdmp.
  • dm_exec_compute_node_status
    • Give information about resources of PolyBase nodes like memory, cpu, time,
  • dm_exec_compute_nodes
    • Returns the list of type, logical name and IP adress of PolyBase nodes
  • dm_exec_distributed_request_steps
    • Give all steps that compose a PolyBase request
  • dm_exec_distributed_requests
    • Give the current status of actives queries\
  • dm_exec_distributed_sql_requests
    • This view shows the data for the last 1000 requests
  • dm_exec_dms_services
    • Give the status of the DMS (Data Movement Service) Service
  • dm_exec_dms_workers
    • Show all workers completing DMS steps for the last 1000 queries and active queries
  • dm_exec_external_operations
    • returns information of external PolyBase operations
  • dm_exec_external_work
    • gives information for the workload per node

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.
  • dm_exec_query_optimizer_memory_gateways
    • Returns the current status of resource semaphores used to throttle concurrent query optimization.
  • dm_exec_query_parallel_workers
    • Returns worker availability information per node
  • dm_exec_session_wait_stats
    • Returns information about all the waits encountered by threads that executed for each session

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
  • 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.
  • dm_db_column_store_row_group_physical_stats
    • Provides current rowgroup-level information about all of the columnstore indexes in the current database

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
  • dm_db_rda_migration_status
    • For the current database, list of state information of the remote data archive schema update task.