Performance Tuning SQL Server
Over the years we have been involved in numerous efforts to tune and harden SQL Server. The following article is an on-going effort to document best practices for ourselves and the technology industry.
"Most people spend more time and energy going around problems than in trying to solve them."
~ Henry Ford
The Full Scoop
As we work to address performance requests from our clients utilizing Microsoft SQL Server, a Relational Database Management System (RDBMS), the following list has been compiled to help define best practices.
Our goal is not to delve into great depth but instead walk through items you should seriously consider to run DB instances optimally. Please reach out to one of our team members if further clarification is required.
- Instant File Initialization
- Reserving space without initialization, helps reduce delays and the time required to expand data files. This will reduce the start time of your SQL Server and allow for faster tempDB initialization.
- Keeping your tempDB configured optimally is very important, otherwise it can lead to operational bottlenecks and degradations in performance. For better performance keep a single data file per core with optimum size, proceed with caution as you make these changes.
- Auto Growth
- Your auto-growth size should be configured in such a way that minimizes auto-growth events. Inappropriate auto-growth sizes will trigger auto-growth events during working business hours, which will be costly from both a performance and impact perspective.
- Place Data and Log Files on Separate Drives
- Data and log files should always be on two different physical drives, as insert/update/delete operations, must write at the exact same time. Placing both data AND log files on the same drive can cause contention, resulting in poor system performance.
- Lock Pages in Memory
- Granting the Lock Pages in Memory user rights option to the SQL Server service account prevents SQL Server buffer pool pages from paging out by Windows. This setting is useful and has a positive performance impact.
- Keep your database servers dedicated to SQL Server
- Don't fall into the trap of running (for example) your Exchange Server and Database Server on the same box. Keep your database server dedicated to running your database!
- Implement and Test your Backup and Restore Plans
- A recommended best practice is to create a SQL Agent job or Maintenance Plan to automate your backup processes. Your backup jobs should use the RESTORE WITH VERIFYONLY option to verify backup integrity. You should also be sure to regularly perform test database restores, with your database backups, to verify that they working as expected.
- A max degree of parallelism should be set as per Microsoft guidelines that have been released for SQL Server 2016 (13.x).
- Regularly defragment the indexes on your most heavily modified tables.
- Configure max server memory appropriately so your Operating System (OS) does not experience detrimental memory pressure. For instance, reserve 1GB-4GB to the OS, plus 1GB for every 4GB to 16GB of RAM, and then 1GB or so for every 8 GB in more than 16 GB of RAM.
- Keep your SQL database patched and up to date, as Microsoft releases timely security and performance patches.
- Monitor disk space to ensure your SQL Servers won’t run out of disk space. For best performance, all disks should have at least 15% or more in free space
- Use alerts to notify your administrative team of potential problems, and have them emailed for troubleshooting. Always take action if needed.
- Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
- Avoid running most DBCC commands during your most busy times of the day. These commands are often I/O intensive and can reduce performance of the SQL Server, which may negatively affect your end users.
- If you rarely restart the SQL Server service, you may find that the current SQL Server log file(s) become large and take long periods of time to load and view. You can close the current error log and create a new one by running "sp_cycle_errorlog" or "DBCC ERRORLOG", to prevent the log from getting overly large. We would suggest setting this up as a weekly job.
- Avoid using the “auto shrink” database option, as it can waste SQL Server resources unnecessarily and contribute to broader index fragmentation. Instead, if you need to shrink a database, do it manually during off-hours.
- Your Disaster Recovery (DR) plan should be in place and detailed out. Be sure to write it in a way that multiple members of your staff are able to understand and follow your DR plan, in the event that an experienced DBA is not available. Be sure to test your DR plan at least annually, while you practice your database restores on at least a quarterly basis. Be sure to monitor for complete backups on a daily basis, with a weekly verification check.