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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s