All geek questions in one place

SQL Server 2005 - Using Multiple Processors

We have a 16-processor cluster of SQL Server 2005. Looking at the CPU usage data, we see that in most cases only 4 out of 16 processors are ever used. However, during periods of high load, the 5th and 6th processors are sometimes used, although it is never next to using another 4. I am worried that during periods of extremely high load not all other processors will be used and we will have performance degradation.

Is this what we see in the standard SQL Server 2005 cluster behavior? I assumed that all 16 processors will be used at any time, although this does not seem to be the case. Is this something we can customize? Or is this the expected behavior? Will the SQL server be able to use all 16 processors if this happens?

+3
source share
4 answers

I will think that you have done due diligence and confirmed that the CPU consumption belongs to the sqlservr.exe process, so we do not pursue the red herring here. If not, verify that the processor is consumed by sqlservr.exe by checking the performance counters of Process \% Processor.

SQL Server, . SQL Server (sys.dm_exec_requests) (sys. dm_os_schedulers), (sys.dm_os_tasks), (sys.dm_os_workers). (sys.dm_os_threads). (, SQL Server) , ( ).

SQL Server 2005 . . . , , . , SQL Server , . , SQL Server 16 , 4 . , :

  • Hyperthreading. hyperthreading, . SQL Server .
  • . , (, ..).

, SQL 2005, , SP2, SP CU. Windows ( Windows 2003 Windows 2008?).

, .. SQL , parallle. , .

+3

IO, , , maxdop, SMP NUMA, , maxdop.

, 16- , 2 SQL- 16 2 x 8-way SQL-?

+2

, ? , IO?

+1

, , , IO , CPU- , 4 , .

, - , 4 , , 5- 6- .

+1

Source: https://habr.com/ru/post/1719117/

More articles:


All Articles