SQL Server Management views (DMVs) are system views which provide insight into internal working of the SQL Server. When you are going to trouble shoot SQL Server or performance of it DMVs plays major part giving us very useful information.
There are hundreds of DMVs in SQL Server today. However this series of blogs going to look into interesting ones.
One of the DMVs I came across recently is called -> dm_exec_query_optimizer_info.
This DMV provide us with statistical data for SQL Server's Query Optimizer. It returns a table with following columns:
| Counter | Occurrence | Value |
|---|---|---|
| Name of the counter/event which is measured | No of times event has occurred | Average value of the property (per occurrence) |
Data Collection
Note that these values and occurrences are get reset, when SQL Server restarts and when they are collected values are occurrences are cumulative (add up for each event). If query was executed using a cached plan, stats in here doesn't get change (i.e. only update when optimizer starts working).
Permission
SQL Server 2019 and earlier -> VIEW SERVER STATE
SQL Server 2022 and later -> VIEW SERVER PERFORMANCE STATE
Execution
SELECT * FROM sys.dm_exec_query_optimizer_info
This view collects data for about 40 events (might vary from version to version). Some of the most interested are listed below:
| Counter | Occurrence | Value |
|---|---|---|
| optimizations | Total Number of optimizations | N/A |
| elapsed time | Number of times time elapsed | Average time taken |
| final cost | Number of times final cost calculated | Average final cost |
| trivial plan | Number of times trivial plan used | N/A |
| no plan | Number of times optimizer didn't find a plan | N/A |
| timeout | Number of times optimizer time out | N/A |
Total List (in 2019)
optimizations
elapsed time
final cost
trivial plan
tasks
no plan
search 0
search 0 time
search 0 tasks
search 1
search 1 time
search 1 tasks
search 2
search 2 time
search 2 tasks
gain stage 0 to stage 1
gain stage 1 to stage 2
timeout
memory limit exceeded
insert stmt
delete stmt
update stmt
merge stmt
contains subquery
unnest failed
tables
hints
order hint
join hint
view reference
remote query
maximum DOP
maximum recursion level
indexed views loaded
indexed views matched
indexed views used
indexed views updated
dynamic cursor request
fast forward cursor request
Note that optimizer counts are not updated if optimizer choose to use cache plan.
No comments:
Post a Comment