Shulou(Shulou.com)06/01 Report--

one。 Overview

We know that from the operating system's point of view, sql server products, like other applications, have no special treatment. But memory, hard disk and cpu are the most important core resources of database system, so SQLOS appeared in sqlserver 2005 and later. This component is the middle layer between sqlserver and windows, which is used for task scheduling of CPU, solving resource contention of CPU, coordinating other resource coordination work such as memory management. Let me try to talk about Scheduler scheduling management under SQLOS. Say no more, let's take a look at the detailed introduction.

two。 Configuration of CPU

In Sql server, right-click the database instance to the properties and select the processor to configure. The default value for the maximum number of worker threads is 0 (note that worker is configured here, which is a true encapsulation of CPU). This allows 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 the maximum number of worker threads to a specific value can sometimes improve performance. When the actual number of query requests is less than the maximum number of worker threads, one thread processes a query request. However, if the actual number of query requests exceeds the maximum thread, SQLServer pools the Worker Threads thread so that the next available worker thread can process the request.

The configuration is shown in the following figure:

It can also be configured through T-sql. The following example configures the max worker thread option to 900via sp_configure.

USE AdventureWorks2012; GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure' max worker threads', 900; GO RECONFIGURE

Max Worker Threads server configuration options do not take into account threads, such as high availability, Service Broker, Lock management, and so on. If the number of threads configured exceeds, the following query will provide additional thread information about the tasks of the system

Is_user_process = 0 represents a system task, not a user task.

SELECT s.session_id, r.command, r.status, r.wait_type, r.scheduler_id, w.worker_address, w.is_preemptive, w.state, t.task_state, t.session_id, t.exec_context_id T.request_id FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id INNER JOIN sys.dm_os_tasks AS t ON r.task_address = t.task_address INNER JOIN sys.dm_os_workers AS w ON t.worker_address = w.worker_address WHERE s.is_user_process = 0

The following shows the number of active sessions per user

SELECT login_name, COUNT (session_id) AS session_count FROM sys.dm_exec_sessions WHERE status'sleeping'GROUP BY login_name

The following table shows the maximum number of worker threads automatically configured for various CPU and SQLServer combinations.

Number of CPUs

32-bit computer

64-bit computer

50select state,last_wait_type,tasks_processed_count,task_address, worker_address, scheduler_address from sys.dm_os_workers where worker_address = 0x00000000043621A0

3.4 View Scheduler

-- Scheduler such as scheduler_id=255 are used internally. For example, resource management, DAC, backup and restore operations, etc.

Is_online: 0 scheduler offline, 1 online.

Current_tasks_count: number of current tasks. Status includes: (wait, run, completed).

Runnable_tasks_count: to assign tasks and wait for the number of scheduled tasks in the runnable queue. In the case of low utilization, this value will be 0.

Current_workers_count: the number of threads associated with this scheduler. Includes work for threads in an idle state.

Active_workers_count: the number of threads currently processing activity, which must be associated with task task, including running,runnable,suspend.

Work_queue_count: the number of task waits for tasks in the queue. If it is not 0, it means that the thread is running out of pressure.

At this point, I will talk about the analysis of excessive CPUf.

References:

Troubleshooting SQL Server Scheduling and Yielding

Management practice of Microsoft SQL Server Enterprise platform

How It Works: SQL Server 2012 Database Engine Task Scheduling

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.