exec_query_stats
You can also review the statistics collection at the query level by executing a query against the sys.dm_exec_query_stats DMV, which returns aggregate performance statistics for cached query plans in SQL Server.
The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
The information contained in the sys.dm_exec_query_stats DMV is listed here => https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-ver15
sys.dm_exec_query_stats in combination with sys.dm_exec_sql_text, find queries that use a lot of resources, such as CPU time or IO.
sys.dm_exec_sql_text returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.
sys.dm_exec_query_stats can be combined with sys.dm_exec_sql_text by sql_handle or plan_handle as,
sys.dm_exec_sql_text(sql_handle | plan_handle)
sql_handle is a token that uniquely identifies the batch or stored procedure that the query is part of. Together with statement_start_offset and statement_end_offset, it can be used to retrieve the SQL text of the query by calling the sys.dm_exec_sql_text dynamic management function.
plan_handle is a token that uniquely identifies a query execution plan for a batch that has executed and its plan resides in the plan cache, or is currently executing. This value can also be passed to the sys.dm_exec_query_plan dynamic management function to obtain the query plan.
sys.dm_exec_sql_text also has information about ID of database (dbid) and ID of object (objectid). Other information in sys.dm_exec_sql_text can be found here => https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sql-text-transact-sql?view=sql-server-ver15
One of the important statistics in sys.dm_exec_query_stats is total_worker_time, which is the total amount of CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed by executions of this plan since it was compiled. For natively compiled stored procedures, total_worker_time may not be accurate if many executions take less than 1 millisecond.
Other relevant information includes:
last_worker_timebigintCPU time, reported in microseconds (but only accurate to milliseconds), that was consumed the last time the plan was executed.
min_worker_timebigintMinimum CPU time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed during a single execution.
max_worker_timebigintMaximum CPU time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed during a single execution.
For natively compiled stored procedures when statistics collection is enabled, worker time is collected in milliseconds. If the query executes in less than one millisecond, the value will be 0.
The goal of using memory-optimized tables is to execute processes as quickly as possible. Consequently, you could be surprised that some statistics, such as worker_time and elapsed_time, do not get collected by DMVs such as sys.dm_exec_query_stats and sys.dm_exec_procedure_stats. In fact, these DMVs include no information about natively compiled stored procedures.
Instead, you need to specifically enable the collection of execution statistics by using one of the following system stored procedures:
sys.sp_xtp_control_proc_exec_stats Use this system stored procedure to enable statistics collection for your SQL Server instance at the procedure level.
sys.sp_xtp_control_query_exec_stats Use this system stored procedure to enable statistics collection at the query level for selected natively compiled stored procedures.
In other words, using the system stored procedures sys.sp_xtp_control_proc_exec_stats andsys.sp_xtp_control_query_exec_stats to enable or disable the collection of execution statistics for natively compiled stored procedures at the procedure level or query level, respectively. After enabling statistics collection, use thesys.dm_exec_procedure_stats and sys.dm_exec_query_stats DMVs to review the statistics.
Example:
Answer:
About sys.dm_exec_sessions =>
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql?view=sql-server-ver15
About sys.dm_db_partition_stats =>
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-partition-stats-transact-sql?view=sql-server-ver15
About