CPU Count

I document the CPU Count for SQL Server inventory management.

Using a powershell script , iterate through all the SQL Servers and capture the CPU count with the following t-sql.

----Returns the logical cpu count on server 
SELECT cpu_count FROM  sys.dm_os_sys_info

----Ratio of the number of logical or physical cores that are exposed by one physical processor package

SELECT  hyperthread_ratio FROM  sys.dm_os_sys_info

----Physical CPU count 
SELECT cpu_count/hyperthread_ratio as physical_count  FROM sys.dm_os_sys_info

 Related Posts

Affinity Mask on SQL Server 2008 R2

SQL Server – SOS_SCHEDULER_YIELD and how to reduce it

Affinity Mask on SQL Server 2008 R2

  
A recommendation made by the vendor for Database Tuning SQL Server on Windows Server 2008 R2 is to apply CPU affinity for the SQL Server process. Or the affinity mask option

 As of SQL Server 2008 R2 , ALTER SERVER CONFIGURATION SET PROCESS AFFINITY , CPU affinity can be configured on up to 256 logical processors.  

 The options are:

AUTO (Default setting; no threas assigned to a CPU)

Distribute SQL Server Worker thread to specifc CPU range

 

 Applying an affinity mask , partitions the SQL processes on specific cores.

 Notes:

1)  Do not enable affinity masking , without careful analysis.

 

 

2)  Check sys.dm_os_schedulers (DMV) .  Returns a recordset where  one scheduler is mapped to a processor.  The DMV gives you a good picture of the schedulers

 

3)  What is affinity masking   aka CPU affinity masking in SQL Server ?   Affinity masking is an option where a processor is assigned a specific thread.

 

4)   Prove that excessive thread migration between processors is causing excessive context switching , causing performance decrease. 

 

5)  Servers with 33 – 64 processors support affinity masking only on 64 bit architecture

 

6)  A Microsoft recommendation is to not enable Operating system affinity masking and SQL Server affinity masking simultanenously.

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server – SOS_SCHEDULER_YIELD and how to reduce it

SQL Server Books online defines SOS_SCHEDULER_YIELD as “Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed “

What is happening when a SOS_SCHEDULER_YIELD is measured? The SQLOS worker has voluntarily yielded the CPU to another worker. This happens regularly and is part of the SQL Server CPU management system . At the point the thread yields it is SUSPENDED.

The point to become concerned is if the SOS_SCHEDULER_YIELD is a high percentage of overall wait time. This points to multiple concurrent requests on the CPU

A few  issues to consider and approached to minimizing SOS_SCHEDULER_YIELD

1)A high percentage SOS_SCHEDULER_YIELD does not always mean CPU pressure. Use perfmon counters to cross-check.

2)Use  the DMV – sys.dm_exec_query_stats by worker time

3)Check for high levels of recompilations.Look to Query Tuning

4)Consider redistributing database files onto other servers, to decrease CPU pressure

5)Assess response from sys.dm_os_schedulers  . From BOL “Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks”

SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

Author: Jack Vamvas (http://www.sqlserver-dba.com)