SQL Server CPU and Memory impact analysis is a vital process to evaluate resource usage and performance.
By monitoring query execution, workload patterns, and memory consumption, it enables efficient resource allocation, identifies bottlenecks, and ensures optimal database operations, leading to a more responsive and stable system.
SQL OS implements a hierarchy of system objects that provide the framework of scheduling, from workers, tasks and memory nodes. Memory node is a logical container associated with CPU share memory which can either be NUMA or soft Numa (Non-Uniform Memory Access).
Nowadays, current NUMA:
Memory controller are built into the processor die.
Each processor socket presents itself as an individual NUMA node to the OS unless interleaving is enable in the BIOS.
Inter-node communication paths are extremely fast and foreign-memory allocations have a lower impact on overall performance.
Is NUMA still matters? Yes, because..
It separate lazy writer process per NUMA node
Has impact on checkpoint operations
Impact on remote memory latency
Max Degree of Parallelism are defined by the number of physical cores per NUMA node.
Creates a custom NUMA configuration independent of hardware configuration.
Only includes processor from single hardware NUMA node.
Automatically configured in SQL Server 2016 by default if > 8 cores per NUMA node.
Provides cooperative scheduling and I/O processing for SQL Server instead of preemptive scheduling by Windows.
Two methods, one is THREAD by default and second is FIBER that can provide performance boost.
It is recommended to DISABLE priority boost.
Not sure about the problem, Do NOT do this unless you want to masked the root cause.
Rebuild all indexes
Restart SQL Server
Update all statistics
> It will remove the actual issue like parameter sniffing and recompiled plans so problem will not resolve.
By default configuration of 0
32-bit SQL Server
> Actual number is (NumberOfScheduler - 4)*8) +256
64-bit SQL Server
> Actual number is (NumberOfScheduler - 4)*16) +512
select max_workers_countfrom sys.dm_os_sys_info
Queries responsible for minor amount of I/O:
Calculations, loops and conversions. (check implicit conversion ex. varchar to nvarchar)
Resolving an IO often solve CPU issues:
Check possible indexing (non clustered index)
SQL 2012+ (Core based licensing limits)
> Standard Edition - 4 sockets & 16 cores
> Enterprise Server + CAL limited to 20 cores
It is recommended to DISABLE priority boost.
If define as server wide it will be a limit
Except SQL 2016+ will override database scoped configuration.
> MAXDOP of the database applied
> Check TempDB & #temp tables - tempDB MAXDOP may apply
MAXDOP query hint will override the server-wide defined
Workload group MAX_DOP cannot be exceeded
64GB RAM for SQL 2008 R2 and 2012 Standard Edition
128GB RAM for SQL 2014 Standard Edition
128GB RAM for SQL 2016 Standard Edition SP1 buffer pool
> 32GB additional for In-memory OLTP
> 32GB additional for Column Store
OS maximum for Enterprise Edition
> Windows Server 2016 = up to 24TB
> Windows Server 2012R2 = up to 4TB
Required on 64-bit servers to prevent memory pressure and out of memory condition
Only applies to the buffer pool, does not set the total amount of memory used by SQL Server
Symptoms: (Windows OS Pressure)
SQL Server memory usage constantly changing
Memory \Available Mbytes < 150-300MB
RING_BUFFER_RESOURCE_MONITOR entries in sys.dm_os_ring_buffers of type RESOURCE_MEMPHYSICAL_LOW
Cause:
VM ballooning
In-correct memory server wide settings
Resolution:
Adjust max memory to reserve OS memory for other application
Cache are memory clerks.
Buffer Pool - Data page cache.
Cache stores - Generic cache framework
> Procedure cache and system rowset cache.
User Store - Generic cache framework
> Schema manager, security and metadata cache
sys.dm_os_memory_cache_counters
Cause:
High data cache inside SQL Instance due to workload
Table or Index scans due to inefficient or missing indexes.
Fragmented index with low page density due to incorrect fillfactor specification
Plan cache bloat due to ad hoc non parameterized workloads
Resolution:
Tune index usage.
Index maintenance and adjust fillfactor based on fragmentation ratios.
Remove implicit conversions/probe residual from column side filtering predicates.
Add memory if needed - Last resort
Largest Cache Store (and memory consumer) for SQL Server
Single Page = 8KB page allocation
> Data page cache
> Procedure cache on 64-bit instance
Memory is committed and mapped on demand
CMEMTHREAD > Waiting for thread-safe memory object, generally for inserting or removing execution plan from cache.
RESOURCE_SEMAPHORE > Waiting for a workspace memory grant for execution of a sort or hash operations.
RESOURCE_SEMAPHORE_QUERY_COMPILE > due to excessive plan compilation at a given point in time (ad hoc workloads).
> incorrect configuration of Resource Governor
> Resolve by re-writing the queries and use store procedure to improve plan cache reuse through parameterization.
RESOURCE_SEMAPHORE_SMALL_QUERY > Due to a small gateways during execution
Sources and good reads:
Professional SQL Server Internals and Troubleshooting
IEPTO2 - SQLSkills immersion event docs (Recommended Training "https://www.sqlskills.com/sql-server-training/ ")