Many technologists dislike the term ‘Best Practice’ because what works for one environment might not work for another. I prefer the term ‘Industry Best Practice,’ prefaced with the caveat: ‘For most environments, this is a solid starting point and a widely accepted standard.’ With that in mind, here are several key configurations I wish every customer would consider for their own environment.
1. Prioritize “Proper” Backups
- A backup is only as good as your ability to restore it. To meet your Service Level Agreements (SLAs), you must verify that your recovery process actually works within your required timeframe.
- The Strategy: For most environments, this means regular full backups supplemented by transaction log backups every X minutes.
- Advanced Needs: Depending on your database size and Recovery Point Objective (RPO), you may also need a High Availability (HA) solution or a warm Disaster Recovery (DR) site.
- Best Practices: Always test and validate backups regularly. Store copies off-site, and if possible, utilize immutable backups to protect against ransomware and accidental deletion.
2. Streamline SQL Server Maintenance
Maintenance shouldn’t be manual. Your routine should include consistency checks (DBCC CHECKDB), statistics updates, index maintenance, and routine cleanup of MSDB data and SQL Agent history.
Pro Tip: I’ve relied on Ola Hallengren’s maintenance solution for over 15 years—it remains the industry gold standard for handling these tasks efficiently.
3. Optimize Instance-Level Settings
Don’t stick with “out of the box” defaults for your server settings:
- Max Server Memory: While the old rule was to leave just 1GB for the OS, modern recommendations vary. Microsoft suggests a conservative 75% for SQL, while many experts (myself included) start around 85% and adjust based on performance.
- Cost Threshold for Parallelism: The default value of 5 is far too low for modern hardware. I recommend starting at 25; it’s a solid baseline that rarely needs to be adjusted downward.
4. Refine Database-Level Configurations
Small changes at the database level can yield massive performance gains:
- Query Store: Enable this on all critical databases to gain immediate insight into query performance and regressions.
- Right-Size TempDB: The default 64MB size (with 64MB autogrowth) is insufficient. Set a proper fixed growth rate for both data and transaction log files to prevent fragmentation and performance bottlenecks.
5. OS and Hardware Tuning
Your operating system settings can hinder SQL Server if not configured correctly:
- Instant File Initialization (IFI): Enable this to allow SQL Server to skip “zeroing out” data files. This significantly speeds up TempDB startups and data file growths.
- Power Plans: SQL Server and “Balanced” power plans don’t mix. Ensure your servers are set to High Performance. If you’re running on physical hardware, make sure power saving is disabled in the BIOS as well.
For me, it is crucial to develop a set of standards for your organization and ensure that all new servers are built to those standards and that your team takes the time to retro the standards to existing servers.
The post SQL Server Best Practices appeared first on Tim Radney.