SQL Server management Views - Part 1

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

Data

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.

SQL Server Worker Threads

Here is my take on SQL Server Worker Threads and Max Worker Thread Setting.

What are Worker Threads

SQL Server, serve "Requests". Request is a logical representation of query or batch or system task (e.g. log update).

Requests can be served via one or more tasks. If it is serial request, one task works at a given time. But if it is a parallel request, multiple tasks can works at a given time.

SQL server spawn, worker to carry on a task. "Worker" is a logical representation of operating system thread. Worker threads are the backbone of SQL Server’s multitasking capabilities.

Max Worker Threads

If you go into SQL Server Properties page -> Processors section, you see a setting called "Maximum worker threads".

This controls how many maximum worker threads SQL server can spawn at a given time. Well Zero doesn't mean there will be none.

When setting is set to zero, SQL server use following formular to calculate maximum number or worker threads it can have:

On 86x processor -> 256 + ((No of Logical Processors – 4) * 8)

On 64x Processor -> 512 + ((No of Logical Processors – 4) * 16)

So if you have 64bit machine with 12 Logical processors, max worker count will be 640.

You can find this out easily by executing following query:

SELECT max_workers_count

FROM sys.dm_os_sys_info

You can set this value to a custom value. But you will find most of experts suggest to keep it default value and look for other issues for resolution.

References: Configure the max worker threads (server configuration option) - SQL Server | Microsoft Learn

Expert articles:

BrentOzar -> https://www.brentozar.com/archive/2020/01/three-reasons-to-temporarily-change-max-worker-threads/

Pinal Dave -> https://blog.sqlauthority.com/2018/06/15/sql-server-optimal-value-max-worker-threads/