SQL Server Tuning

/ Author Jared Knisley, Client Retention, Cloud Support, Communication, Computer Repair, Customer Service, Data Backups, IT Services, IT Support, Leadership, Managed IT, Mobile Device Management, Productivity, Project Management, Teaching Others, Team Building, Working Remotely / By

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.

SQL Server Performance Tuning

“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, we compiled the following list to help define best practices. (Microsoft SQL Server is a Relational Database Management System (RDBMS).)

We will walk through items you should seriously consider to run DB instances optimally. Please reach out to one of our team members for further clarification.

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.

TempDB

Keeping your tempDB configured optimally is very important, otherwise it will lead to operational bottlenecks and degradations in performance. For better performance keep a single data file per core with optimum size, proceed with caution.

Auto Growth

Your auto-growth size should be configured to minimize auto-growth events. Inappropriate auto-growth sizes will trigger auto-growth events during working business hours. This 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. Do not place both data AND log files on the same drive. This causes 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 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

We recommend creating a SQL Agent job or Maintenance Plan to automate your backup processes. Your backup jobs should use the RESTORE WITH VERIFY ONLY option to verify backup integrity. You should also be sure to regularly perform test database restores with your database backups.

More Tips on SQL Server Tuning

  • A max degree of parallelism should be set as per Microsoft guidelines that were 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. 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.

Alerts and Other Regular Maintenance

  • Use alerts to notify your administrative team of potential problems, and have them emailed for troubleshooting.
  • Ensure that 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. This negatively affects end users.
  • If you rarely restart the SQL Server service, the current SQL Server log file(s) become large and take a long time to load. 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. Set this up as a weekly job.
  • Avoid using the “auto shrink” database option, as it can waste SQL Server resources unnecessarily. It can also contribute to broader index fragmentation. 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. Write your DR plan 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. Test your DR plan at least annually, while you practice your database restores on at least a quarterly basis.
  • Lastly, monitor for complete backups on a daily basis, with a weekly verification check.

Fizen™

Have questions about IT Support and Service? Contact Us today to learn more about how we can assist in supporting your technology.

Related Posts