sql server cpu占用率高，t–sql pl–sql_SQL Server處理器性能指標–第4部分–處理器指標有助于更深入的調查和故障排除

2023-10-17 阅读 22 评论 0

摘要：t–sql pl–sqlIn the previous part of this series, we presented the processor metrics that indicate hardware component problems that can affect SQL Server performance In this part, we will present processor metrics that are not necessary in every performa

t–sql pl–sql

In the previous part of this series, we presented the processor metrics that indicate hardware component problems that can affect SQL Server performance

In this part, we will present processor metrics that are not necessary in every performance monitoring scenario, but are useful for deeper investigation and troubleshooting

在本系列的前一部分中，我們介紹了處理器指標，這些指標指示可能會影響SQL Server性能的硬件組件問題。

在這一部分中，我們將介紹處理器指標，這些指標并非在每個性能監視方案中都必需，但對于更深入的調查和故障排除很有用。

系統：通話/秒 (System: Calls/sec)

“This counter is a measure of the number of calls made to the system components, Kernel mode services. This is a measure of how busy the system is taking care of applications and services—software stuff. When compared to the Interrupts/Sec it will give you an indication of whether processor issues are hardware or software related” [1]

“此計數器衡量對系統組件（內核模式服務）的調用次數。 這是系統忙于處理應用程序和服務（軟件方面）的度量。 與Interrupts / Sec進行比較時，它將指示處理器問題與硬件還是軟件有關” [1]

sql server cpu占用率高、In other words System: Calls/sec metric shows the number of software calls to the operating system service routines per second. As the Processor: Interrupts/Sec shows the number of hardware calls to the processor, these two metrics can determine the source of the performance problems

換句話說，“ 系統：呼叫/秒”度量標準顯示每秒對操作系統服務例程的軟件調用次數。當處理器：中斷/秒顯示了對處理器的硬件調用次數時，這兩個指標可以確定性能問題的根源

The normal values for System: Calls/sec are under 20,000. Higher values might indicate excessive operating system activity caused by software applications. In this case, check the Context Switches/sec value (explained later) to identify the process responsible for excessive software calls

系統：呼叫/秒的正常值在20,000以下。較高的值可能表示由軟件應用程序引起的過多的操作系統活動。在這種情況下，請檢查“ 上下文切換數/秒”值（稍后說明），以確定造成過多軟件調用的進程

It’s expected that the System: Calls/sec values are higher than Processor: Interrupts/sec. However, if the latter is higher, it’s a clear indication of a hardware problem

預期System：Calls / sec值高于Processor：Interrupts / sec 。但是，如果后者更高，則表明存在硬件問題

sqlserver性能優化？

Graph showing System: Calls/sec values and threshold

線程對象：優先級基礎 (Thread Object: Priority Base)

“The thread gets a base priority from the Process that created it. The priority of the thread can be adjusted by the system or through a program. This priority is used to judge when the thread is going to have access to the process and how many other threads it may jump ahead of in the processor queue of ready threads.” [1]

“線程從創建它的進程中獲得基本優先級。 線程的優先級可以由系統或程序調整。 該優先級用于判斷線程何時將有權訪問該進程，以及它在就緒線程的處理器隊列中可能會提前多少個其他線程。” [1]

The idle thread has the lowest priority among all processor threads. Useful processor threads can have the following priority classes: low, below normal, normal, above normal, high, and real time

空閑線程在所有處理器線程中的優先級最低。有用的處理器線程可以具有以下優先級類別：低，低于正常，正常，高于正常，高和實時

Monitoring the Thread Object: Priority Base metric will help you determine whether there are threads with higher priority than optimal. We have already recommended using a dedicated machine for SQL Server. Having additional applications, especially resource-intensive ones, can use resources needed by SQL Server and degrade its performance. Assigning these processes higher priority than normal will affect execution and performance of other threads and processes, and even block them. SQL Server will have to wait for the processor to handle other applications first and its performance will suffer

sql和oracle的區別。監視線程對象：“優先級基礎”度量標準將幫助您確定是否存在優先級比最佳優先級高的線程。我們已經建議對SQL Server使用專用計算機。擁有其他應用程序，尤其是資源密集型應用程序，可能會使用SQL Server所需的資源并降低其性能。為這些進程分配比正常更高的優先級將影響其他線程和進程的執行和性能，甚至會阻止它們。 SQL Server將不得不等待處理器首先處理其他應用程序，并且其性能將受到影響

To see the base priority of the thread, you can use the Windows Task Manager

若要查看線程的基本優先級，可以使用Windows任務管理器

Open Windows Task Manager (press Ctrl+ Alt + Delete)
打開Windows任務管理器（按Ctrl + Alt +刪除）
Details tab詳細信息選項卡
Show columns顯示列”
Base priority. Now the Base priority column is shown in the grid, so you can order the records descending by their priority基本優先級。現在，“基本優先級”列顯示在網格中，因此您可以按其優先級對記錄進行排序
To change the thread priority:

更改線程優先級：
Right-click a process
右鍵單擊一個進程
Select Set priority and select one of the options offered
選擇設置優先級，然后選擇提供的選項之一

cpu性能指標有哪些？

All processes have the normal priority by default, except some system processes

默認情況下，除某些系統進程外，所有進程均具有正常優先級

Now, check the priority of the SQL Server process – sqlservr.exe. By default, it should be normal. If there are some non-system and non-essential applications, processes, or services running with priority higher than normal, they can jeopardize the performance of your SQL Server. If there are no obvious or logical reasons for this, reduce their priority to normal and allow SQL Server to use as much processor time it can get

現在，檢查SQL Server進程的優先級– sqlservr.exe。默認情況下，它應該是正常的。如果有一些非系統和非必要的應用程序，進程或服務運行的優先級高于正常水平，則它們可能會損害SQL Server的性能。如果沒有明顯或邏輯上的原因，則將其優先級降低至正常水平，并允許SQL Server使用可以獲得的盡可能多的處理器時間。

pl sql安裝？The same metric, but in different presentation is shown in Windows Performance Monitor

Windows Performance Monitor中顯示相同的度量標準，但表示形式不同

Windows Performance Monitor – open Windows Performance Monitor –打開“ Control Panel, Administrative Tools, Performance Monitor控制面板”，“管理工具”，“ Performance Monitor”
Performance Monitor性能監視器”
Right-click the graph on the right
右鍵單擊右側的圖
Add counters添加計數器
Thread and then 線程” ，然后選擇“ Priority Base優先級基礎”
In the lower list, select the processes you want to monitor
在下方列表中，選擇要監視的進程

The values shown in the Performance Monitor graph are not descriptive like in Windows Task Manager, but numerical:

sql if， 性能監視器圖中顯示的值不是Windows Task Manager中的描述性值，而是數字：

Numerical value	Priority
4	Low
6	Below normal
8	Normal
10	Above normal
13	High
24	Real time

數值	優先
4	低
6	低于一般
8	正常
10	超出正常水平
13	高
24	即時的

上下文切換/秒 (Context Switches/sec)

The Context Switches/sec metric is used to obtain more in-depth information about processor issues

上下文切換/秒度量標準用于獲取有關處理器問題的更深入的信息

“A context switch occurs when the kernel switches the processor from one thread to another—for example, when a thread with a higher priority than the running thread becomes ready.” [2]

“上下文切換是在內核將處理器從一個線程切換到另一個線程時發生的，例如，當優先級高于運行線程的線程準備就緒時。” [2]

A thread can be replaced by a thread of a higher-priority, or a thread executed in the user mode can be replaced by a kernel mode thread

sqlserver占用cpu過高，可以用更高優先級的線程替換線程，或者可以用內核模式線程替換在用戶模式下執行的線程

It’s important to monitor context switching as it can indicate performance issues. If a specific process is using the processor more than others, not allowing other processes to be processed, the rate of context switching will be low. A high rate of context switching indicates that many processes are using and competing for the processor time, and it is switching from one thread to another frequently. Too frequent switching, the same as too rare, indicates problems

監視上下文切換非常重要，因為它可以指示性能問題。如果特定進程比其他進程更多地使用處理器，而不允許處理其他進程，則上下文切換的速率將很低。高上下文切換率表明許多進程正在使用并爭奪處理器時間，并且它正在頻繁地從一個線程切換到另一個線程。切換太頻繁（與切換太少一樣）表明存在問題

The Context Switches/sec counter shows a rate at which the processor switches from one thread to another. This is a total rate for all processors on the machine, i. e. the sum of the Thread:Context Switches/sec metric for all threads on all processors on the machine

上下文切換/秒計數器顯示處理器從一個線程切換到另一個線程的速率。這是計算機上所有處理器的總費率，即計算機上所有處理器上所有線程的Thread：Context Switches / sec指標之和

The average Context Switches/sec value should be below 2,000 per processor. Some DBAs consider this limit to be 5,000 per processor. Higher values can be caused by excessive page faults caused by insufficient memory. Also, if hyper-threading is turned on, turn it off and test the performance. It can significantly reduce the threading and solve performance problems

sqlserver版本性能，每個處理器的平均上下文切換/秒值應低于2,000。一些DBA認為此限制為每個處理器5,000。較高的值可能是由內存不足導致的過多頁面錯誤引起的。另外，如果打開了超線程，請將其關閉并測試性能。它可以大大減少線程并解決性能問題

High Context Switches/sec values are more frequent on the machines that besides the database system host other resource-intensive applications. They can also occur on a system with several SQL Server instances

除了數據庫系統托管其他資源密集型應用程序之外，計算機上的高上下文切換/秒值更加頻繁。它們也可能在具有多個SQL Server實例的系統上發生

Constantly high values of Context Switches/sec and Processor: % Processor Time are a clear indication of processor bottlenecks. The ultimate solution is changing the processor to a more powerful one, or adding an additional one

上下文切換/秒和處理器：處理器時間百分比的值始終很高，這清楚地表明了處理器瓶頸。最終的解決方案是將處理器更改為功能更強大的處理器，或添加一個附加的處理器。

線程對象：ID線程 (Thread Object: ID Thread)

Every thread running on the operating system is uniquely identified by its ID. If the same thread is executed again, it will be assigned a new ID

sqlplus連接oracle，操作系統上運行的每個線程均由其ID唯一標識。如果再次執行同一線程，則會為其分配一個新的ID

The Thread Object: ID Thread metric shows an ID of the process thread, necessary to differentiate a specific thread among others. This metric is useful if you’re monitoring other metrics that provide details based on the thread ID

線程對象：ID線程度量標準顯示進程線程的ID，這是區分特定線程所必需的。如果您要監視其他基于線程ID提供詳細信息的度量，則此度量很有用

流程：流程ID (Process: Process ID)

Every process running on the operating system is uniquely identified by its ID. If the same process is executed again, it will be assigned a new ID

操作系統上運行的每個進程均由其ID唯一標識。如果再次執行相同的過程，則會為其分配新的ID

The same as Thread Object: ID Thread, the Process: Process ID metric is usually used for determining the right process when process details are based on the Process ID

sqlserver性能，當進程詳細信息基于進程ID時，通常與“ 線程對象：ID線程 ”相同，使用“ 進程：進程ID”度量標準來確定正確的進程。

The performance of SQL Server can be affected by other applications or by other SQL Server instances hosted on the same machine. Monitor the System: Calls/sec value to identify resource-intensive applications, check Thread Object: Priority Base to find out if any application has been given higher priority and thus eating processor time needed by SQL Server, watch Context Switches/sec to see whether all applications can get the processor time they need

SQL Server的性能可能會受到其他應用程序或同一臺計算機上承載的其他SQL Server實例的影響。監視系統：調用/秒值以識別資源密集型應用程序，檢查線程對象：優先級基礎以發現是否有任何應用程序被賦予更高的優先級，從而吃掉SQL Server所需的處理器時間，觀看上下文切換/秒以查看是否所有應用程序都能獲得所需的處理器時間