What are Dynamic Management Views in SQL Server? Part - 5

Outline of the Article:

1. Initialization

2. What exactly are dm_os_buffer_descriptors?

3. Dm_os_buffer_descriptors' benefits

4. dm_os_buffer_descriptor use

5. Dm_os_buffer_descriptors Must Be Run With Permission

6. dm_os_buffer_descriptor examples

7. The Verdict

8. FAQs

Introduction:

Optimizing data storage and retrieval is essential for preserving system performance and efficacy in the field of database administration. Managing the buffer pool, which is in charge of memory caching data pages, is a crucial component of this optimization. A dynamic management view offered by SQL Server called dm_os_buffer_descriptors gives information on the buffer pool and the data pages linked to it. We will examine the benefits, usage, and permission specifications, offer real-world examples, and dive into the relevance of dm_os_buffer_descriptors in database administration in this article.

What exactly are dm_OS_Buffer_Descriptors?

The dynamic management view (DMV) dm_os_buffer_descriptors in SQL Server offers comprehensive details on the buffer pool and its related data pages. By using information like page type, database ID, file ID, page ID, and more, it enables database administrators and developers to learn more about the present situation of data pages cached in memory.

Benefits of using sys.dm_os_buffer_descriptors: 

Performance Improvement

Using dm_os_buffer_descriptors, administrators may examine the buffer pool's current condition and spot any possible performance hiccups. This DMV gives details on memory's data pages, such as how frequently they are used, which helps optimize memory usage, speed up data retrieval, and improve system performance as a whole.

Permission Required to Run dm_os_buffer_descriptors

Users who have the VIEW SERVER STATE access must have the dm_os_buffer_descriptors permission. Members of the sysadmin fixed server position or others with the necessary rights are often given this permission. To protect the security and integrity of the server, it is essential to make sure that access to this DMV is only given to reputable organizations.

What are Dynamic Management Views in SQL Server? Part - 4

16. DMVs for the query store:

The prefix "sys.dm_exec_query_plan_forcing_" identifies it.

sys.dm_exec_query_store and sys.dm_exec_query_store_text are two examples.

a) Sys.query_store_query:

Introduction:

The [sys].[query_store_query] system view is a potent tool for improving query speed in the context of SQL Server. It enables database administrators and developers to fine-tune their queries, pinpoint performance bottlenecks, and make data-driven choices by giving them access to crucial information about query execution plans and statistics. In this article, we'll examine the benefits of utilizing sys.query_store_query, dig into its query syntax, and present a number of examples to show how it may be used in real-world scenarios.

Benefits of using Sys.query_store_query:

There are various benefits to using Sys.query_store_query in SQL Server, making it a useful tool for query optimization:

Insights into query performance: Sys.query_store_query enables access to detailed information on query performance. You may identify inefficiencies and make specific changes by looking at the execution plans, resource utilization, and query data.

Finding performance bottlenecks: By using the data supplied by Sys.query store_query, you may locate queries that use up too many resources or execute slowly. This knowledge helps us to concentrate our optimization efforts on key areas and improve system performance as a whole.

Analyzing Historical Query: Analyzing historical query data enables us to follow the evolution of query behavior. Sys.query_store_query saves historical data regarding query performance. This historical study aids in the discovery of trends, the planning of potential improvements, and the comparison of performance before and after modifications.

The SQL stores runtime and performance-related details in a specific DMV, that is sys.dm_exec_query_stats.

SELECT @@SERVERNAME AS ServerName,DB_NAME(DB_ID()) AS DatabaseName,

  Query_ID [QueryID],

  QSQT.Query_SQL_Text AS [SQL Statement],

  [Text] AS [Query Batch Text]

FROM sys.Query_Store_Query QSQ

CROSS APPLY sys.DM_Exec_SQL_Text(last_compile_batch_sql_handle)

INNER JOIN sys.Query_Store_Query_Text QSQT

  ON QSQ.query_text_id = QSQT.query_text_id;

Conclusion:

Sys.query_store_query is a powerful feature in SQL Server for optimizing query performance. By providing access to query execution details, statistics, and historical data, it empowers users to analyze, troubleshoot, and optimize queries effectively. Leveraging the capabilities of Sys.query_store_query allows us to gain valuable insights, identify performance bottlenecks, and make informed decisions to enhance the overall efficiency and performance of our SQL Server environment.

Regularly analyzing query performance and optimizing queries using Sys.query_store_query ensures that our database operates optimally and delivers optimal performance to users.

b) Sys.query_store_query_text:

Introduction:

The Sys.query_store_query_text system view for SQL Server is a potent tool for getting an in-depth analysis of query performance. It enables database administrators and developers to optimize their queries, find performance bottlenecks, and make data-driven choices by giving access to query text, execution statistics, and historical data. In this article, we'll examine the benefits of utilizing sys.query_store_query_text, examine its query syntax, and present a number of examples to show how it may be used in real-world scenarios.

Sys.query_store_query_text's benefits:

Utilizing Sys.query_store_query_text in SQL Server has the following benefits, which make it an important tool for query optimization:

Analysis and optimization of queries: The text of queries actually run by the system is provided by Sys.query_store_query_text. This gives programmers and database managers the ability to examine query logic, spot possible problems, and improve queries for increased efficiency.

Performance troubleshooting: Sys.query_store_query_text aids in performance debugging by reviewing query text and associated execution statistics. In order to facilitate focused performance-tuning efforts, it enables users to discover queries with excessive resource utilization, lengthy execution durations, or inefficient execution plans.

Analyzing Historical Query: Sys.query_store_query_text holds historical information about query execution for historical query analysis. In doing so, users are able to analyze query behavior over time, analyze performance changes, and compare query performance before and after optimizations.

SELECT @@SERVERNAME AS ServerName,DB_NAME(DB_ID()) AS DatabaseName, 

    qsqt.Query_Text_ID AS [QueryTextID],

    qsqt.Query_SQL_Text AS [SQL Query]

FROM 

    sys.query_store_query_text AS qsqt;

Conclusion:

Sys.query_store_query_text is a powerful tool for optimizing query performance in SQL Server. Providing access to query text, execution statistics, and historical data, it allows us to analyze, troubleshoot, and optimize queries effectively. Leveraging the capabilities of Sys.query_store_query_text empowers we to identify performance bottlenecks, uncover query patterns, and make data-driven decisions to enhance the overall efficiency and performance of our SQL Server environment.

Remember to regularly analyze and optimize our queries using Sys.query_store_query_text to ensure that our database performs optimally and delivers a seamless user experience.

17. DMVs for in-memory OLTP:

The prefix "sys.dm_db_xtp_" serves as an identifier.

Examples are sys.dm_db_xtp_checkpoint_files and sys.dm_db_xtp_gc_cycle_stats.

sys.dm_db_xtp_checkpoint_files:

Outline

1. Introduction of sys.dm_db_xtp_checkpoint_files

2. What exactly is sys.dm_db_xtp_checkpoint_files

3. Benefits of using sys.dm_db_xtp_checkpoint_files

4. Making use of sys.dm_db_xtp_checkpoint_files

5. Required access to run sys.dm_db_xtp_checkpoint_files 

6. Illustrative sys.dm_db_xtp_checkpoint_files

7. Summary

8. FAQs

Introduction:

In the area of database administration, processing data effectively and efficiently is essential for preserving system performance and dependability. Sys.dm_db_xtp_checkpoint_files is one such utility that assists in controlling and monitoring the condition of a SQL Server database. The benefits, usage, and permission needed to use this potent feature will all be covered in this article along with several useful examples.

Sys.dm_db_xtp_checkpoint_files: What do they do?

A dynamic management view (DMV) in SQL Server called sys.dm_db_xtp_checkpoint_files gives details on the files related to the In-Memory OLTP capability. It enables checkpoint files for memory-optimized tables and indexes to be better understood by database administrators and developers.

Benefits of sys.dm_db_xtp_checkpoint_files

 

Better Performance Monitoring:

Administrators may better track the performance of memory-optimized tables and indexes with the aid of sys.dm_db_xtp_checkpoint_files. The information in this DMV about the checkpoint files, including the file path, size, and condition, enables administrators to see possible performance bottlenecks and take the necessary countermeasures.

Improved Troubleshooting Tools:

Sys.dm_db_xtp_checkpoint_files appears to be a useful tool in troubleshooting circumstances. Administrators may learn vital details about the checkpoint files, including the quantity of pending IOs, IO failures, and more, by accessing this DMV. With the use of this data, memory-optimized tables and indexes-related problems may be diagnosed more quickly, resulting in less downtime.

Making use of the sys.dm_db_xtp_checkpoint_files:

One may do a quick T-SQL query against this DMV to take advantage of the capabilities of sys.dm_db_xtp_checkpoint_files. In a given SQL Server database, the query gets details about checkpoint files related to memory-optimized tables and indexes. Here's an illustration:

SELECT * FROM sys.dm_db_xtp_checkpoint_files;

A complete listing of the checkpoint files' file IDs, locations, sizes, statuses, and other details will be included in the result set.

Required Permission to Launch sys.dm_db_xtp_checkpoint_files:

Users need the VIEW DATABASE STATE permission on the target database in order to query the sys.dm_db_xtp_checkpoint_files. This permission can be given to authorized users or roles by database administrators or users with the necessary rights. To protect the security and integrity of the database, it is crucial to make sure that access to this DMV is only given to reputable organizations.

Sys.dm_db_xtp_checkpoint_file illustrations:

Example 1: Examining Checkpoint File Specifications

Let's say we have an "OrdersDB" database with memory-optimized tables and indexes. The following query may be used to acquire details about the checkpoint files connected to these objects:

USE OrdersDB;

go;

SELECT * FROM sys.dm_db_xtp_checkpoint_files;

Details such as file IDs, file locations, sizes, and statuses will be displayed for the checkpoint files of memory-optimized tables and indexes in the "OrdersDB" database.

Example 2: Checkpoint File State Monitoring

The following query might be useful in situations when monitoring the condition of checkpoint files is critical:

USE OrdersDB;

go;

SELECT State, State_Desc FROM sys.dm_db_xtp_checkpoint_files;

The state and state description of the checkpoint files associated with memory-optimized tables and indexes in the "OrdersDB" database are returned by this query.

Conclusion:

Finally, sys.dm_db_xtp_checkpoint_files is a useful dynamic management view in SQL Server that offers information about the checkpoint files associated with memory-optimized tables and indexes. Database administrators may use this functionality to monitor performance, solve issues, and gather critical information for optimal database administration. It is critical to ensure that the proper permissions are issued to authorized persons or roles in order to preserve the database's security and integrity.

FAQs

Q1: Can sys.dm_db_xtp_checkpoint_files be used with tables that are not optimized for memory?

Ans: No, sys.dm_db_xtp_checkpoint_files is intended to function with memory-optimized tables and indexes.

Q2: Can the checkpoint files be modified using sys.dm_db_xtp_checkpoint_files?

Ans: No, sys.dm_db_xtp_checkpoint_files is a read-only dynamic management view that contains information about checkpoint files but cannot be modified.

Q3: Do you need a certain SQL Server version or edition to use sys.dm_db_xtp_checkpoint_files?

Ans: SQL Server 2014 and subsequent versions, including Enterprise and Standard editions, provide sys.dm_db_xtp_checkpoint_files.

Q4: How often should the sys.dm_db_xtp_checkpoint_files table be queried?

Ans: The frequency with which you query sys.dm_db_xtp_checkpoint_files is determined by your monitoring and troubleshooting requirements. It is advised that it be included in routine monitoring practices or troubleshooting scenarios.

What are Dynamic Management Views in SQL Server? Part - 1

What are Dynamic Management Views in SQL Server? Part - 2

What are Dynamic Management Views in SQL Server? Part - 3

What are Dynamic Management Views in SQL Server? Part - 5

What are Dynamic Management Views in SQL Server? Part - 3

DMVs that govern resources

Prefixed with "sys.dm_resource_governor_"

Examples are sys.dm_resource_governor_workload_groups and sys.dm_resource_governor_configuration.

a) sys.dm_resource_governor_workload_groups

Outline:

1. Initialization

2. What exactly are sys.dm_resource_governor_workload_groups?

3. Benefits of sys.dm_resource_governor_workload_groups DMV

4. Making use of sys.dm_resource_governor_workload_groups

5. Necessary permission to run sys.dm_resource_governor_workload_groups 

6. A few examples of sys.dm_resource_governor_workload_groups

7. Conclusion

8. FAQs

Introduction:

Effective resource management and allocation are essential in database setups with different workloads. The dynamic management view sys.dm_resource_governor_workload_groups in SQL Server helps administrators manage workloads and allocate resources. We will examine the benefits, usage, and permission specifications, offer real-world examples, and talk about the importance of sys.dm_resource_governor_workload_groups in improving database speed and resource usage in this article.

What exactly does sys.dm_resource_governor_workload_groups mean?

The dynamic management view (DMV) sys.dm_resource_governor_workload_groups in SQL Server gives details on the workload groups specified in the Resource Governor feature. Administrators can use the Resource Governor to assign resources, such as CPU and memory, to various workloads by specified rules and regulations. The configuration and current condition of these workload groups may be seen in sys.dm_resource_governor_workload_groups.

Benefits of sys.dm_resource_governor_workload_groups:

Optimizing the allocation of resources:

Administrators may track and fine-tune resource allocation to various workload groups by using sys.dm_resource_governor_workload_groups. To detect possible bottlenecks, balance resource utilization, and improve overall performance, administrators may use this DMV, which offers information on the CPU and memory use for each task group.

Effective workload control: It is made possible by sys.dm_resource_governor_workload_groups DMVs. Administrators may guarantee that key workloads receive the required resources while avoiding resource contention and performance degradation brought on by resource-intensive workloads by analyzing the state and configuration of workload groups.

Using the sys.dm_resource_governor_workload_groups:

We may use sys.dm_resource_governor_workload_groups by running a straightforward T-SQL query against this DMV. The query returns data on the workload groups and the CPU and memory use that goes along with them. Here's an illustration:

SELECT * FROM sys.dm_resource_governor_workload_groups;

This query will provide information such as the name of the workload group, the resource pool ID, the lowest and maximum CPU and memory use percentages, and more.

Needs Permission to run sys.dm_resource_governor_workload_groups:

Users need to have the VIEW SERVER STATE permission in order to query the sys.dm_resource_governor_workload_groups table. Members of the sysadmin fixed server position or others with the necessary rights are often given this permission. To protect the security and integrity of the server, it is essential to make sure that access to this DMV is only given to reputable organizations.

sys.dm_resource_governor_workload_groups examples

Example 1: Tracking Group Workload Utilisation:

Let's say we want to keep track of how much CPU and RAM each task category is using. The following query may be run:

SELECT * FROM sys.dm_resource_governor_workload_groups;

This query will return data on the workload groups and the CPU and memory use that goes along with them.

Conclusion:

A useful dynamic management view in SQL Server that offers insights into workload group configuration and resource use is called sys.dm_resource_governor_workload_groups. Database administrators may balance workloads, optimize resource allocation, and improve overall database performance by utilizing this capability. Effective resource management depends on granting the required rights and carefully using this DMV.

FAQs 

Q1: Can the settings for workload groups be changed using sys.dm_resource_governor_workload_groups?

Ans: No, the read-only DMV sys.dm_resource_governor_workload_groups contains data on resource utilization and workload group configuration. Setting adjustments for workload groups should be made using the proper SQL Server commands or the Resource Governor configuration.

Q2: Does SQL Server's sys.dm_resource_governor_workload_groups feature exist in all editions?

Ans: Yes, all versions of SQL Server, including the Enterprise, Standard, and Express editions, support sys.dm_resource_governor_workload_groups.

Q3: Is it possible to link numerous resource pools to a single task group?

Ans: No, a task group can only be linked to a single resource pool. However, a single resource pool may be used by several task groups.

Q4: Are there any performance implications of using sys.dm_resource_governor_workload_groups?

Ans: The performance impact of utilizing sys.dm_resource_governor_workload_groups to monitor resource usage is negligible. However, it could raise system overhead if used frequently or in queries that need complex calculations. It is advised to utilize the DMV sparingly and to optimize queries for speed.

Q5: Can I/O resource utilization be tracked using sys.dm_resource_governor_workload_groups?

Ans: No, sys.dm_resource_governor_workload_groups does not directly track I/O resource consumption; instead, it offers insights into CPU and memory resource utilization.

What are Dynamic Management Views in SQL Server? Part - 1

What are Dynamic Management Views in SQL Server? Part - 2

What are Dynamic Management Views in SQL Server? Part - 4

What are Dynamic Management Views in SQL Server? Part - 5

What are Dynamic Management Views in SQL Server? Part - 2

6. DMVs for system information

It may be recognized by the prefix "sys.dm_os_"

Examples are sys.dm_os_wait_stats and sys.dm_os_memory_clerks.

7. DMVs with High Availability

Using the prefix "sys.dm_hadr_"

Examples are sys.dm_hadr_database_replica_states and availability_replica_states.

8. DMVs for Extended Events:

Using the prefix "sys.dm_xe_"

Examples are sys.dm_xe_sessions and sys.dm_xe_database_session_events.

9. DMVs for CLR Integration:

It may be recognized by the prefix "sys.dm_clr_"

Examples are sys.dm_clr_appdomains and sys.dm_clr_properties.

10. DMVs for filestreams:

The prefix "sys.dm_filestream_" serves as an identifier.

Examples include sys.dm_filestream_non_transacted_handles and sys.dm_filestream_file_io_handles.

What are Dynamic Management Views in SQL Server? Part - 1

What are Dynamic Management Views in SQL Server? Part - 3

What are Dynamic Management Views in SQL Server? Part - 4

What are Dynamic Management Views in SQL Server? Part - 1

Introduction:

SQL Server's Dynamic Management Views (DMVs) are potent diagnostic tools that offer helpful details on the operation and behaviour of your SQL Server instance. DMVs give database managers and programmers the ability to acquire data in real-time on numerous facets of the SQL Server environment. This article will examine several DMV categories, their uses, applications, samples, explanations, frequent queries with purposes, and the advantages they provide.

Understanding Dynamic Management Views: 

Dynamic Management Views are specialized system views that reveal specific details about SQL Server's internal operations. They provide a plethora of diagnostic and monitoring data that may be accessed using conventional SQL commands, and they are kept as virtual tables within the master database. Performance monitoring, resource utilization, query execution, index optimization, and many other topics are all covered by DMVs.

Benefits of DMVs:

1. Performance Monitoring and Troubleshooting: DMVs give administrators thorough insights into the operation of SQL Server instances, allowing them to quickly spot and resolve problems. Administrators may identify bottlenecks and improve system performance by looking at key performance indicators including CPU consumption, memory utilization, I/O statistics, and query execution plans.

2. Query Analysis and Optimisation: Developers may discover wasteful queries and improve their performance by using DMVs to obtain visibility into the execution plans of queries. DMVs like sys.dm_exec_query_stats and sys.dm_exec_query_plan may be analyzed by developers to evaluate indexing tactics, evaluate query performance, and optimize queries.

3. Index tuning: DMVs offer useful data on index utilization, index fragmentation, and index gaps. In order to improve database performance, administrators can find underutilized indexes, spot fragmentation problems, and optimize index structures by running queries against the sys.dm_db_index_usage_stats and sys.dm_db_missing_index_details tables.

4. Resource Utilization Monitoring: Monitoring of resource consumption, including CPU, memory, disc I/O, and network utilization, is provided by DMVs in real-time. Administrators can efficiently distribute system resources by monitoring resource-intensive processes, locating performance bottlenecks, and querying sys.dm_os_ring_buffers and sys.dm_os_wait_stats.

Based on their features and purposes, Dynamic Management Views (DMVs) in SQL Server may be divided into a number of categories. Here are the main categories of DMVs and the symbols used to identify them:

1. DMVs for performance:

Prefixed with "sys.dm_os_" or "sys.dm_exec_"

Examples are given below:-

a) sys.dm_exec_query_stats:

Provide data on the overall performance of SQL Server's cached query plans. Within the cached plan, the view comprises one row per query line, and the rows' lifespan is dependent upon the plan. The rows for that plan are deleted from this view when it is taken out of the cache.

Advantages of sys.dm_exec_query_stats:

i. Identifies Queries with High Resource Consumption: By examining data from sys.dm_exec_query_stats, we may locate queries with high resource consumption, such as those that consume a lot of CPU time or carry out several logical or physical reads. This makes it possible to concentrate optimization efforts on the questions that will have the greatest overall system effect.

ii. Enhances the execution of plans: The statistics in sys.dm_exec_query Developers can access and examine the query execution plans related to certain queries by using the execution plan handles provided by DMV. Developers can find ineffective join methods, missing indexes, and wasteful index utilization by evaluating execution plans. With this information at hand, they may optimize query execution strategies for increased performance.

iii. Assessing Query Modifications: Developers can assess the effects of their optimizations by comparing statistics from sys.dm_exec_query_stats before and after making modifications to queries. This gives them the ability to evaluate the success of their changes and, if required, make more.

SELECT TOP 5 @@ServerName AS [ServerName], @@Version AS [Version],ServerProperty('Edition') AS [Edition],

SUBSTRING(t.text, (s.statement_start_offset/2) + 1, ((CASE s.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) 

ELSE s.statement_end_offset END - s.statement_start_offset)/2) + 1) AS [Query],

s.execution_count AS [No. Of Execution],s.total_worker_time AS [TotalWorkerTime],

s.total_logical_reads AS [TotalLogicalReads],s.total_physical_reads  AS [TotalPhysicalReads],

s.creation_time AS [CreationTime]

FROM sys.dm_exec_query_stats AS s

CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t

ORDER BY s.total_worker_time DESC;

According to the overall worker time, we extract the top 5 inquiries in this case. The query retrieves the text of the query, the number of executions, the number of workers, the number of logical reads, the number of physical reads, and the creation time. This data offers an insightful understanding of the query performance traits.

b) sys.dm_os_sys_info:-

The dynamic management view sys.dm_os_sys_info in Microsoft SQL Server offers details about the server's hardware setup and operating system. It includes various system-level data that may be used for monitoring and troubleshooting.

The following are some significant specifics that the sys.dm_os_sys_info view can offer:

Version, edition, and build information for SQL Server are all included in the display.

Information about the operating system: It gives specifics about the OS installed on the server, including the OS version, architecture (32- or 64-bit), and the number of CPUs.

It provides details about the actual hardware.

Advantages of sys.dm_os_sys_info:

i. Accurate system information: We may get accurate and current information about our system using sys.dm_os_sys_info like operating system version, CPU architecture, and the amount of physical memory accessible. These are just a few of the attributes that this DMV shows. We may learn a lot about the infrastructure that underlies our SQL Server setup by using this view.

ii. Get System Details: System information may be retrieved quickly, but doing so frequently requires deep dive into numerous levels of administrative tools and parameters. Sys.dm_os_sys_info, on the other hand, streamlines this procedure by offering a centralized and complete view of vital system information. Database administrators and system engineers may use this DMV effectively during troubleshooting and to improve system performance.

iii. Performance monitoring and troubleshooting: This plays an important part at the time of performance monitoring and troubleshooting. We can get details of important system parameters like the number of logical processors, system clock tick rate, and maximum level of parallelism by querying this DMV. With this knowledge, we can spot possible bottlenecks, adjust system settings, and maximize resource use.

Examples of how to use sys.dm_os_sys_info

1. Obtaining operating system information

We can use the following command to get information about the OS:

SELECT * FROM sys.dm_os_sys_info;

The results of this query will include details on the system architecture, service pack level, and version of Windows.

2. Analyzing memory configuration

To analyze memory configuration, we can use the following query:

SELECT @@ServerName AS [ServerName],GetDate() AS [CurrentDate],

physical_memory_in_bytes/1024/1024 AS [Total Physical Memory (MB)],

virtual_memory_in_bytes/ 1024 / 1024 AS [Total Virtual Memory (MB)],

max_server_memory / 1024 AS [Maximum Server Memory (MB)]

FROM sys.dm_os_sys_info;

We will get the total amount of physical memory, total amount of virtual memory, and maximum server memory configuration, all in megabytes (MB), from this query.

3 Checking system CPU details:

Use the following command to get the details of the CPU on the server:

SELECT @@ServerName AS [ServerName],GetDate() AS [CurrentDate],

CPU_count AS [Logical CPU], hyperthread_ratio AS [Hyperthread Ratio],

CPU_count / hyperthread_ratio AS [Physical CPU]

FROM sys.dm_os_sys_info;

In addition to calculating the number of physical CPUs, this query obtains the hyper thread ratio and the number of logical CPUs.

2. DMVs specific to databases:

It may be recognized by the prefix "sys.dm_db_"

Examples are sys.dm_db_file_space_usage and sys.dm_db_index_usage_stats.

3. Query and Execution Plan DMVs:

Has the prefix "sys.dm_exec_" in its name.

Examples are sys.dm_exec_cached_plans and sys.dm_exec_sessions.

4. DMV locks and blocks:

Assigned a prefix of "sys.dm_tran_"

Examples are sys.dm_tran_locks and sys.dm_tran_database_transactions.

5. Store DMV queries:

The prefix "sys.query_store_" serves as an identifier.

Examples are sys.query_store_plan and sys.query_store_runtime_stats

What are Dynamic Management Views in SQL Server? Part - 2

What are Dynamic Management Views in SQL Server? Part - 3

What are Dynamic Management Views in SQL Server? Part - 4