Worker threads
Worker (worker thread) – This is the logical SQL Server representation of a thread (think of it as a wrapper on top of the OS thread). It is a structure within the Scheduler which maintains SQL Server-specific information about what a worker thread does. sys.dm_os_workers. Workers are the humble servants who carry out the task assigned to them by the Master (scheduler).
Sometimes it is confusing to relate threads in SQL Server to the ones with the OS threads. But they are different. For SQL Server, the threads (worker threads) sit on top of the OS threads. Whenever a request comes in, the User Mode Scheduler (UMS) manages the execution of the request. These are the worker threads that are used here. There will be a single UMS for each CPU. At any point, UMS will have a running queue of requests waiting for CPU, IO Locks, Memory and or user requests.
The max worker threads option configures the number of worker threads that are available to SQL Server processes. SQL Server uses the native thread services of the operating systems so that one or more threads support each network that SQL Server supports simultaneously, another thread handles database checkpoints, and a pool of threads handles all users. The default value for max worker threads is 0. This enables SQL Server to automatically configure the number of worker threads at startup. The default setting is best for most systems. However, depending on your system configuration, setting max worker threads to a specific value sometimes improves performance. (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-server-ver15)
The database engine must process a high number of inserts, either as a steady stream or in bursts. Bottlenecks from locking and latching are a common problem in this scenario. Furthermore, last-page contention can occur when many threads attempt to access the same page in a standard B-tree and indexes intended to improve query performance add overhead time to insert operations. Performance is often measured in terms of throughput rate or the number of rows loaded per second.
Optimize memory configuration
SQL Server’s memory manager dynamically allocates memory according to the workloads on the host computer and in the database engine. However, you can use the following server configuration options to optimize SQL Server memory:
min server memory Use this option to prevent SQL Server from releasing memory to the operating system when the server memory drops to this threshold.
max server memory Use this option to ensure that other applications running on the same computer as SQL Server have adequate memory. When an application requests memory only as needed, you do not need to configure this option. It applies only when an application uses the memory available when it starts and does not later request more memory when necessary. You should configure this option to prevent SQL Server from taking the memory that the application might need.
max worker threads Use this configuration to define the number of threads available to user operations. If you keep the default value of 0, SQL Server configures the number of worker threads each time the service restarts.
index create memory Use this option to set the maximum amount of memory that SQL Server initially allocates for index creation. SQL Server will allocate more memory later if necessary, but only if it is available. Typically, you do not need to configure this option, but if SQL Server is experiencing performance delays related to indexing, you can increase the value of this option.
min memory per query Use this option to improve performance of memory intensive queries by establishing the minimum amount of memory allocated for query execution. SQL Server can use more memory than the configured minimum if it is available.
To configure memory, right-click the server instance in Object Explorer and select Properties. Click the Memory page, and then in the Server Memory Options, type the appropriate values for any property except max worker threads. You can also use T-SQL to adjust a property value like this:
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'min memory per query', 512 ; GO RECONFIGURE; GO
One of the best ways to determine which SQL Server resource is a bottleneck on performance is to review wait statistics.
To better understand why waits, it is helpful first to understand how SQL Server manages incoming requests. Each authenticated connection is assigned to a session by SQL Server which then uses a pseudo-operating system called the SQL Operating System (SQLOS) Scheduler to schedule CPU time for each session’s requests.
There is one SQLOS Scheduler per logical CPU core on the server to manage the worker threads performing operations necessary to complete a request. These worker threads must work cooperatively by running only for 4-milliseconds, known as a quantum, before yielding the CPU to another worker thread and waiting in a runnable queue for another turn.
It might voluntarily yield the CPU if its quantum has not yet expired and it cannot complete its task, because a resource it needs is unavailable. In this case, the worker thread is moved to a waiter list and then later moves back to the runnable queue when the needed resource becomes available.
As long as SQL Server is the only application running on a server, it can manage memory dynamically without intervention. When you must run other applications on the server, you can optimize SQL Server’s memory configuration by specifying minimum and maximum memory thresholds, maximum worker threads, maximum memory for index creation, and minimum memory for query execution.
We can use Performance Monitor, also known as PerfMon, to monitor CPU usage by System: Processor Queue Length Number of threads waiting for processor time. An increase in this counter means the CPU is not keeping up with demand and a faster processor is necessary.
We can DMV sys.dm_os_wait_stats for troubleshooting lock issues, it is also useful for discovering the most frequently occurring waits since the last reset of the cumulative values. The cumulative wait time in this DMV includes the cumulative signal wait time, so subtract signal wait time from wait time when you want to determine the cumulative time threads spend in the waiter list.
We use the sys.dm_os_wait_stats, sys.dm_exec_session_wait_stats, or sys.dm_os_waiting_tasks DMVs to gather information about the amount of time that threads must wait on resources, determine whether the server is experiencing CPU, memory, or IO pressure, or find out which resources are causing excessive waits.
Bulk Update (BU) This lock mode is used for bulk copy operations to allow multiple threads to bulk load data into the same table at the same time and to prevent other transactions that are not bulk loading data from accessing the table. SQL Server acquires it when the table lock on bulk load table option is set by using sp_tableoption or when you use a TABLOCK hint like this: INSERT INTO Examples.TestParent WITH (TABLOCK) SELECT <columns> FROM <table>;
Example:
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours. You must monitor and optimize the SQL Server to maximize throughput, response time, and overall SQL performance. You need to examine delays in executed threads, including errors with queries and batches. What should you do?
A. Create a sys.dm_os_waiting_tasks query.
B. Create a sys.dm_exec_sessions query.
C. Create a Performance Monitor Data Collector Set.
D. Create a sys.dm_os_memory_objects query.
E. Create a sp_configure "˜max server memory'query.
F. Create a SQL Profiler trace.
G. Create a sys.dm_os_wait_stats query.
H. Create an Extended Event.
Answer: G