select @@SERVERNAME,
SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
cpu_count, 
hyperthread_ratio, 
softnuma_configuration, 
softnuma_configuration_desc, 
socket_count, 
numa_node_count 
from 
sys.dm_os_sys_info

--select * from sys.dm_os_memory_nodes

--EXEC sys.sp_configure 'show advanced options',1;

--RECONFIGURE;

--EXEC sys.sp_configure 'cost threshold for parallelism';--varsayżlan 3
--EXEC sys.sp_configure 'max degree of parallelism';--varsayżlan 5


--EXEC sys.sp_configure 'max degree of parallelism',4;
--EXEC sys.sp_configure 'cost threshold for parallelism',12;
--RECONFIGURE;



---- Use the following guidelines when you configure the MAXDOP value for SQL Server.

---- NUMA nodes	Logical processors	MAXDOP value
---- Single	? 8	4, 2, or number of cores (for one or two cores)
---- Single	> 8	8, 4, or 2
---- Multiple	? 16	8, 4, or 2
---- Multiple	> 16	16, 8, 4, or 2


--ALTER DATABASE eflow SET allow_snapshot_isolation ON;
--ALTER DATABASE eflow SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--ALTER DATABASE eflow SET read_committed_snapshot ON;
--ALTER DATABASE eflow SET MULTI_USER;



----USE master; 
----GO 
----EXEC sp_configure 'automatic soft-NUMA disabled', 1; 
----RECONFIGURE WITH OVERRIDE; 



----ALTER SERVER CONFIGURATION
----SET PROCESS AFFINITY CPU = 0 TO 4;

--ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE=0

--select cpu_count, hyperthread_ratio, softnuma_configuration, softnuma_configuration_desc
--from sys.dm_os_sys_info

--SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced 
--FROM sys.configurations WITH (NOLOCK) 
--ORDER BY name OPTION (RECOMPILE);

--SELECT * FROM sys.dm_os_performance_counters
--WHERE object_name LIKE '%Buffer Node%';

--SELECT
--inf.affinity_type AS [AffinityType],
--nd.node_state_desc AS [NodeStateDesc],
--mnd.memory_node_id AS [ID],
--nd.processor_group AS [GroupID],
--nd.cpu_affinity_mask AS [CpuIds],
--nd.online_scheduler_mask AS [CpuAffinityMask]
--FROM
--sys.dm_os_memory_nodes AS mnd
--INNER JOIN sys.dm_os_sys_info AS inf ON 1=1
--INNER JOIN (Select SUM (cpu_affinity_mask) as cpu_affinity_mask,
--SUM(online_scheduler_mask) as online_scheduler_mask,
--processor_group,
--node_state_desc,
--memory_node_id
--from sys.dm_os_nodes
--group by memory_node_id, node_state_desc, processor_group) AS nd ON nd.memory_node_id = mnd.memory_node_id
--ORDER BY ID ASC




----
---- Since these are advanced options, we first need to set 'Show Advanced Options' & RECONFIGURE
----
--EXEC sp_configure 'show advanced options', 1
--RECONFIGURE
--GO
 
----
----  Integer value 15 translates to 0x000F (which shows that the last 4 bits are set to 1. Similarly 240 translates to 0x00F0
----
--EXEC sp_configure 'affinity mask', 15
--EXEC sp_configure 'affinity I/O mask', 240
--GO
--RECONFIGURE
--GO
 
----
----  Once completed, reset the Advanced Options
----
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO