SQL Server can be a resource intensive application to host and run in the infrastructure. For this perspective, you have to expect heavier usage of resources on these servers and workstations.

HOWEVER, there are times when the server performance is affected in a negative way due to high CPU or high memory usage. Below are a few ways to try to remediate this issue:

1. Identifying and Tuning Expensive Questions
   • Use Query Store (introduced in SQL 2016) or sys.dm_exec_query_stats to find queries with high CPU time.
   • Rewrite the sentences or index them correctly.
2. Fix Parameter Sniffing Issues
   • If you are experiencing problems with queries, please use:
     • OPTION (RECOMPILE)
     • OPTIMIZE FOR hints
     • or Query Store Plan Forcing
3. Modify MAXDOP Settings and Parallelism
   • Default MAXDOP=0 may cause excessive parallel queries.
   • Best Practice: Set MaxDOP = number cores per Numa node. Cost Threshold of Parallelism should be raised from 5 to 30-50.
4. Reduce Plan Cache Bloat
   • Ad-hoc queries in excess can eat up memory and bloat plan cache.
   • Enable optimize ad-hoc workloads.
5. Check for Blocking & Deadlocks
   • Long-running blocked requests can spike CPU and Memory.
   • Monitor using Extended Events, Activity Monitoring, or whoisactive.
6. Optimize TempDB Configuration
   • High TempDB consumption consumes CPU and memory.
   • Use multiple TempDBs of equal size for faster storage. Also, ensure that Instant File Initialization (IFI) is enabled.
7. Patching and applying the latest CU/Service Packs SQL Server 2016 contained known CPU/memory issues that were addressed in later Cumulative Upgrades. Always keep SQL Server updated.