- Power Plan – Make sure server is set to High Performance otherwise you have 30% performance loss (CPUz benchmark tool)
- Services – Make sure to keep your surface area is as small as possible (Ex: If you don’t use SSRS or SSIS, turn them off and keep the service disabled). The more services are enabled, the memory is used.
- Patching – Some of the bug fixes come up with new patches.
- Memory –
- Check Min and Max Memory
- Multiple Instances
- Extra Services SSRS, SSIS, SSAS
- Lock Pages in Memory
- Set Memory Reservation for your VM on the Host
- Pin to High Performing Disk
- Multiple Files – 1 file per CPU core up to 8 cores
- Trace flag – 1118 (mixed events)/1117 (allows filegroup to grow at the same time) pre 2016
- Heavy Contention in Metadata fixed 2016 CU8 and 2017 CU5
- Trace Flag 2453 allows table variables to trigger recompile
- Parallelism – check MAXDOP and CTFP
7. Instant File Initialization (IFI) – SQL Server Service account to be granted Perform Volume Maintenance Tasks permissions in windows
8. Blocking – Allow Snapshot Isolation & Read committed Snapshot Isolation. Make sure to allow higher storage for TempDB before you turn RCSI ON.
9. Virtual Log Files:
- A large number of VLFs can slow things down.
- VLF counts under 300 ideally
- Shrink files to two VLFs
- Grow in chunks back to the current size
- DBCC LOG INFO or sys.dm_db_log_stats (2016 SP2)
10. Make Friends with Network & Storage Admins – Storage could be the problem and network
11. Over Provisioned VMs & Host
- Too many VMs on One Host
- What Happens on One VM Now Affects Another
- VM Over/Under CPU Allocation (Watch SOS_Scheduler_Yields & CPU Ready Time)
- Thin Provisioning (make sure you are thick provisioned or you make sure you grow the files to the higher end to take up the storage)
- Row/Page Compression – More Data in MEMORY
- Saves Space on Disk
- Backup Compression: Less Data in STORAGE
- Costs CPU speed, but worth it
- All about estimates versus actuals
- Need to make sure we are keeping them up to date
- Have a job that keeps stats up to date
- Use DMV to check if they are updated frequently
- When you have auto statistics set to ON, it will trigger a stats update. It will have a hiccup in performance. Set that to asynchronous stats update.
- Trace Flag 2371 (defaulted in 2016).
- Did you know you can create your own stats?
- Remove UN-Used indexes (wasted Disk IO, disable then delete)
- Find Missing Indexes (Wasted Read IO, Avoid Duplicates, Don’t just ADD, look at % impact it is going to make. See if you can modify existing to add a column rather than create an extra index)
- Covering Indexes (Consolidate indexes, specific tuning). Quarterly go through your indexes, script them out, export to excel and look through them.
15. EXECUTION PLANS