Believe it or not, there are people in the world whose only job is to manage SQL Server and SQL Server databases. But you don’t have to be a full-time database administrator (DBA) to manage the performance of your server’s performance. If you plan your installation and get in regularly to assess and tweak performance, you can keep your SQL Server installation running like a well-oiled machine.
The following tips are three ways that you can manage your SQL Server’s resources which will provide you with long-term benefits:
1. Make SQL Server Your Server’s Full-Time Job
It may seem like common sense, but avoid running non-SQL Server processes on your SQL server. For example, don’t make your SQL Server a web server as well. The web server will consume resources that could better be used by the SQL Server application.
Likewise, keep file server or domain controller services off your SQL Server, as they may not only draw resources away from your SQL Server, but they may inadvertently provide users with access to areas of the SQL Server that they shouldn’t be touching.
2. Avoid Contention for Resources
Every computer system has a limited supply of resources: processor (CPU), memory (RAM), and disk are typical physical resource limitations. Managing your server’s resources can make the difference between excellent, acceptable, and terrible system performance.
3. Manage Your Database Files
- Processor: Within the SQL Server, you can choose how much of the total processor capacity it uses. Monitor how your computer is using the CPU and adjust the percentage SQL Server uses to make sure you are leaving enough CPU for non-SQL Server system processes.
- Memory: SQL Server will use as much memory as you give it, and more memory usually equals better performance. But make sure that you reserve enough memory for your operating system and other critical processes.
- Storage: Spinning disks are usually slower than Solid State Disks (SSD). However, spinning disks provide a higher capacity at a lower cost than SSD. Many successful and high-performing SQL Server implementations put each database on a separate set of disks. Your implementation may require a mix of SSD and spinning disks for optimal performance.
Each SQL Server database consists of two types of files: database files and transaction log files. Both are critical for efficient operation of your SQL Server, and your SQL Server interacts with each type of file differently. The database file is the long-term repository for data, and the transaction logs are where new data goes short-term until it’s committed into the database file.
It’s a good idea to put the database files and transaction log files on separate disks. That way, outgoing data can be read from some disks while new data is being written into your database’s transaction logs on other disks.
Size your databases appropriately from the start and plan for growth. Although SQL Server can automatically grow your database files when they’re running out of space, you’re better off controlling the file growth and having growth occur when there’s little activity, as the SQL Server will freeze all activity while the files are grown.
Think about growing databases like a school crossing guard at a busy intersection. When the crossing guards see that kids need to cross the road (the disk needs to grow), the crossing guard stops all traffic until the kids are across, then allows the traffic to resume. The crossing guard can stop traffic each time a student needs to cross (on-demand file growth) or wait until there are many students (planned file growth) before bringing traffic on the busy road to a halt.
Planning your file locations and managing your database file sizes and growth will keep your SQL Servers and your applications running smoothly.
For more tips about improving your SQL Server’s performance, see: 11 Ways to Improve Your SQL Server Performance.