Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.

SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.

The new or enhanced DMV’s/DMF’s fall into the three categories:

• Database related
  • sys.dm_db_log_stats
  • sys.dm_db_log_info
  • sys.dm_db_stats_histogram
  • sys.dm_db_file_space_usage
  • sys.sys.dm_db_tuning_recommendations
• Transaction related
  • sys.dm_tran_version_store_space_usage
• SQL Server Operating System related
  • sys.dm_os_host_info
  • sys.dm_os_sys_info

Further Reading

SQL Server 2017 new(or Enhanced) DMVs and DMFs

Wrapping up

The overview of new dynamic management views and dynamic management functions that ship with SQL Server 2017 have been explained in detail. They can be put to use to get more insight into the status of the system. It can be informative and useful for the administrators to understand the metadata pertaining to the SQL Server instance. To me, the sys.dm_db_file_space_usage and sys.dm_db_tuning_recommendations turns out to be something I was looking for, for a long time. How about you? Please feel free to comment!