Syncade MES - Database tuning, maintenance, and optimization

[AgentSmith]

Do you have timeouts, errors, deadlocks, blocked sql queries, or other performance related issues? Lets all talk about what we are doing to proactively try and resolve our issues.

[Nerd4me]

Here are some basic example SQL Tunes:

SQL Server 2016+

• Set the power plan to - High Performance
• SQL Server Startup Parameter - Enable Trace Flag T834 (If the SQL server has greater than 64 cores)
• Max Degree of Parallelism = 0
• Cost Threshold of Parallelism = 50 (for 8 or more vCPU's)
• Leveraging core based licensing is required in some circumstances to be able to leverage all the cores.
• If using Query Store - Enable Trace Flags 7745 & 7752 (faster startups / faster shutdowns)
• Enable Instant File Initialization (understand the security risks)
• Follow best practices for separating the disks for tempdb, logs, databases, backups, binaries, etc.
• As long as there are at least 8 processors set the TempDB to 8 (try to place on SSD disks) Read here to optimize
• Ensure that the growth settings on the databases are correctly optimized
• Use Enterprise edition of SQL Server for online maintenance (production systems without outage windows)
• Leverage SQL AlwaysON
• Read Only Intent for reporting
• Research your storage and understand if the disks should be formatted with 64KB disk allocation units per MS best practices. (Learn more)
• Set the Min & Max Memory & SQL Server

Virtualization

• VMWare - if using a SAN create separate VMWare Paravirtual SCSI Contollers Per Disk.
• Disable Hot CPU Add
• Enable reserve all guest memory to prevent VMWare Ballooning & Swapping

Maintenance

• Consider using Ola Hallengrens Maintenance Solution
• Brent Ozars SP_Blitz for additional recommendations

Additional ideas for helping boost performance

If performance issues occur after an upgrade of SQL and the queries run roughly 10x slower than prior. Try adding cardinality hints into your queries to leverage legacy cardinality. Additionally, use sql profiler to see the problematic queries and use the query optimizer to see if there are any hints for creating custom indexes.