How your SQL Server instances perform depends on database and application design, CPU model, and more. One of the primary elements to consider is capacity, or whether the system has sufficient underlying resources such as memory and storage. To this end, you’ll want to have insights and strategies in place to perform effective capacity planning.
Server capacity planning tools can help manage this task by surfacing data about relevant system metrics and trends, which you can use to inform your decision-making. In this article, I’ll share how database capacity planning works, which elements and datastreams you’ll want to monitor, and some recommendations for server capacity planning tools.
I suggest using a tool with server capacity calculator functionality, as this can significantly reduce the amount of time and energy you spend keeping track of your SQL Server resources. Depending on your needs, two excellent server capacity planning tool options include SolarWinds® Server & Application Monitor (SAM) and SolarWinds Database Performance Analyzer (DPA).
Server and Database Capacity Planning in a Nutshell
Server capacity planning is the process of determining the specific resources your SQL Server will need over time, as well as how to tailor your approach when rolling out a new SQL Server instance. While storage and disk capacity are important factors to consider, planning involves more than just determining the baseline amount of each a given server needs—you also need to consider the appropriate resources SQL Server requires to optimally handle its workload. Capacity planning often addresses immediate and near-term server usage, but you can also use historical and ongoing trends to anticipate how future growth or scale-up plans could impact capacity as well. You’ll want to factor those projections into the design, configuration, and resources you provision to support your databases and ensure they operate as intended.
How to Do Capacity Planning in SQL Server
Proactive monitoring can help stave off potential issues before they manifest and help you gather valuable performance data to drive your database capacity planning efforts. The following are key elements of effective capacity planning.
Track trends: Data-driven storage capacity planning begins with robust monitoring practices. Monitoring your server and resource usage for trends is critical for understanding how your typical usage patterns compare to overall capacity. This requires understanding how key resources like disk usage, I/O, indexes, memory, and others are used over extended periods of time, not just a few days or even weeks. Tracking trends can also help provide visibility into resource contention issues that could be negatively impacting the performance of your server instances.
Understand disk space utilization: Monitoring disk space utilization is an important part of database capacity planning, and you can include SQL queries that capture information like file size and amount of space used. This metric is important to monitor for seasonal trends over time to have a more holistic view of usage patterns. Because this information is easy to attain and takes up minimal storage space, it can be essential reference information during storage capacity planning and procurement. Quantitative data related to the growth of your systems can also help bolster your case for needing another SQL Server instance.
Optimize CPU performance: This means more than just increasing the number of CPUs you have available—model and workload are also important to factor into database capacity planning. A data warehouse capable of handling large parallel queries, for instance, has much different operating restrictions than an OLTP database with serial queries (the latter being more limited to specific applications). Take the time to research which CPU type and version of SQL Server make the most sense for your needs.
Ensure sufficient memory: Compared to other resources, memory is relatively inexpensive, so it’s generally good advice to purchase the maximum amount of memory for each instance during server capacity planning. The added advantage of this is reading data from memory rather than from disk will increase your database speeds, so the more data that can be fit into your available memory, the more efficiently your systems will operate. Typically, only the working set of data needs to be able to fit into your available memory, but, because purely OLTP environments don’t allow you to generate reports from large, cross-stack datasets, you may need to load older data into memory as well. Understanding the workload and types of queries you expect to be running against your databases is a central part of server and database capacity planning.
Calculate I/O responses: One final note about SQL Server and I/O metrics. Your goal as an admin is to try to get the fastest I/O responses possible from your databases. Faster disks will of course improve your I/O operations per second (IOPS) while keeping latency and queuing to a minimum. That said, if the I/O subsystem is delivering slow responses, procuring more CPU, memory, or other kinds of resources won’t improve the subsystem’s response times. Resolving the issue within the subsystem is critical to preventing slow response times from creating additional problems throughout your server farms.
The Case for Server Capacity Planning Tools
All of this is easier said than done, of course. Capacity planning is a significant undertaking, and the right combination of server capacity planning tools can make a world of difference.
I highly recommend SolarWinds Server & Application Monitor (SAM) and SolarWinds Database Performance Analyzer (DPA). Each provides a different set of features, and when used together, they can provide you with key insights to improve your database and storage capacity planning efforts.
SAM is designed to simplify your server and storage capacity planning processes.
The application can be used as a server capacity calculator and can generate capacity forecasting charts for you to see when resource utilization approaches and crosses critical performance thresholds. SAM helps you track your peak and average capacity over time in visually intuitive graphs, allowing your team to refine their forecasts and identify issues before they can impact your end users and business operations. This helps ensure consistent storage capacity planning and keep performance issues to a minimum.
DPA is a database capacity monitoring tool designed to provide valuable information for troubleshooting problems. DPA collects metrics related to SQL Server backups (including Active Backup Jobs, Longest Time for a DB without a Successful Full Backup, Size of Transaction Logs Not Yet Archived, and more), CPU, disk space, memory, network, sessions, TempDB, waits, and license compliance—all of which helps ensure you can stay on top of potential issues as they arise. You can also use intelligent recommendations provided by this software to plan changes in storage capacity of your SQL Server instances.
[video: https://www.youtube.com/watch?v=PkDv-P3Ebr4]When you’re looking for leading server capacity planning tools with server capacity calculator functions, SolarWinds SAM and DPA should be at the top of your list. With effective server capacity planning, you can help ensure consistent, optimal performance for each of your SQL Server instances. You can test them for free with a 30-day free trial of SAM or a 14-day trial of DPA today.