有一台SQL Server（SQL Server 二〇一五标准版）服务器中的scheduler_count与cpu_count不等同，如下截图所示：
Detect CPU Bottleneck in SQL Server
SELECT cpu_count , scheduler_count FROM sys.dm_os_sys_info;
By : Kasim Wirama, MCDBA
When you have your database server experiencing a problem, there are many possibilities here, such as CPU, memory, hard disk or database configuration itself. There should be a systematic way to trouble shooting performance problem in SQL Server. This article, I would like to point out how to detect bottleneck in CPU with SQL Server 2005.
The straight forward way to detect CPU problem is to look at performance counter, with object : Processor, and counter name : %Processor Time. If it shows high percentage value, let’s say 80% or over during 15 to 20 minutes, you definitely have CPU bottleneck. Anyway, you need to establish your baseline for CPU threshold above. Another counter name that is useful is System:Processor Queue Length. This counter gives information how long a queue for each processor. If you see 2 or more value for most of the time, your processors are under pressure. When your server box has some applications running besides SQL Server, probably one of the application takes up significant CPU resource. To prove your suspicious thought, get information from Process:%Processor Time counter.
If you have your CPU bottleneck caused by SQL Server, you need to find out how many processes that are running, runnable, and suspended. An amount of runnable processes indicate that the CPU is busy serving other request, and an amount of suspended processes indicate that there is blocking issue. Here is the query to get the information.
SELECT COUNT(*) ,
From sys.dm_os_workers as t2, sys.dm_os_schedulers as t2
Where t1.state = ‘runnable/running/suspended’ and t1.scheduler_address = t2.scheduler_address and t2.scheduler_id < 255
Group by t2.scheduler_id
In general, there are 2 things that causes CPU bottleneck, they are :
- 1. Inefficient query plan.
If you want to associate the query with CPU bottleneck, you query it from DMV sys.dm_exec_query_stats and extract query text from sys.dm_exec_sql_text with parameter sql_handle. You sort the result based on most expensive average CPU cost that consists of division between total_worker_time and execution_count
- 2. Excessive compilation and recompilation.
If SQL Server needs some time to compile/recompile the query, it shows that your execution plan is not reusable. If your query is very complex, try to rewrite/adding some index that will make the compilation time run faster.
These are 3 performance counter relating to excessive compilation/recompilation issue :
- a. SQL SERVER: SQL Statistics : Batch Requests/Sec
- b. SQL SERVER: SQL Statistics : SQL Compilations/Sec
- c. SQL SERVER: SQL Statistics : SQL Recompilations /Sec
With wealth information from DMV and performance monitor, you have a useful tool for troubleshooting CPU bottleneck right away.
SQL Server中Scheduler数量应该与逻辑CPU的核数一致，而sys.dm_os_sys_info中的scheduler_count 为8，少于cpu_count的11个数据，那么很有一点都不小大概，有一对Scheduler的气象为VISIBLE ONLINE.上面摘自Healthy SQL: A Comprehensive Guide to Healthy SQL Server Performance
SELECT is_online ,[status] , COUNT(*) AS [count] FROM sys.dm_os_schedulers WHERE scheduler_id < 255 GROUP BY is_online, [status];
Indicates the status of the scheduler. Can be one of the following values:
- HIDDEN ONLINE
- HIDDEN OFFLINE
- VISIBLE ONLINE
- VISIBLE OFFLINE
- VISIBLE ONLINE (DAC)
Is not nullable.
HIDDEN schedulers are used to process requests that are internal to the Database Engine. VISIBLE schedulers are used to process user requests.
OFFLINE schedulers map to processors that are offline in the affinity mask and are, therefore, not being used to process any requests. ONLINE schedulers map to processors that are online in the affinity mask and are available to process threads.
DAC indicates the scheduler is running under a dedicated administrator connection.
HOT ADDED indicates the schedulers were added in response to a hot add CPU event.
内部有关OFFLINE与ONLINE的演讲：OFFLINE 安顿程序在关乎掩码中映射到地处脱机状态的微处理器，因而不用于拍卖任何央求。 ONLINE 陈设程序在事关掩码中映射随处于一块状态的Computer，而且可用以拍卖线程。