SQL Server Performance tuning Check List

Best Practices:

1. Power Plan – Make sure server is set to High Performance otherwise you have 30% performance loss (CPUz benchmark tool)
2. 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.
3. Patching – Some of the bug fixes come up with new patches.
4. 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
5. TempDB
   • 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
6. 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)

12. COMPRESSION

• Row/Page Compression – More Data in MEMORY
• Saves Space on Disk
• Backup Compression: Less Data in STORAGE
• Costs CPU speed, but worth it

13. STATISTICS

• 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?

14. INDEXES

• 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