Capacity Planning Fundamentals For SQL Server DBAs

Edition: Saturday, February 24th, 2024

A common challenge DBAs encounter is efficient capacity planning.  Ensuring that SQL Server is operational, optimized, and ready to scale is important. 

In today's edition, I'll share insights on capacity planning, including strategies, tools, and best practices to efficiently manage database size, performance, and growth expectations.  You'll learn to anticipate future needs, allocate resources, and implement a planning cycle supporting your organization's objectives.

Effective capacity planning ensures that your SQL Server instances can handle current workloads and are prepared for future growth, preventing performance degradation and downtime.  Additionally, it influences budgeting and procurement decisions, helping your organization avoid unnecessary expenditures on hardware or cloud resources.  

Many of us encounter challenges with capacity planning due to a few common pitfalls:

• Underestimating Growth  - Failing to accurately predict future data growth and workload increases can lead to resource shortages and performance bottlenecks.
• Lack of Comprehensive Monitoring  - Planning becomes guesswork without a detailed understanding of current usage and performance trends.
• Ignoring Business Objectives  - Capacity planning should align with the organization's goals.  Neglecting this can result in misallocated licenses, CPU, memory, and storage allocation resources.
• Overprovisioning  - While it may seem like a good choice, overprovisioning wastes resources and inflates costs without delivering proportional value.

Effective capacity planning is not just a technical requirement; it's a strategic skill set for any SQL Server DBA.  By mastering this skill, you can directly influence your organization's agility, performance, and financial health.

Here's what I'll cover today:

Understand Your Workload:

Understanding the SQL Server workload is the foundation of effective capacity planning.

I've worked in environments without a budget for 3rd party monitoring software.  In these cases, I've had to be creative.  If you're in this situation, here's how you can leverage various tools and strategies to gain insights into your database performance and growth patterns:

• Database Growth - Utilize built-in system tables, such as sys.master_files, to capture database file sizes daily.  This can be stored within a utility database for regular reviews to understand growth patterns.  
• Performance counters - SQL Server provides many dynamic management views, exposing information.  Most 3rd party tools take advantage of DMVs, and you can, too.  Get familiar with them and begin capturing data regularly.  Watch for trends over time.  There are many scripts available, or you can write your own.  For example, Glenn Berry's diagnostic queries and DBATOOLS.  For a list of DMVs you can use, check out System dynamic management views (Transact-SQL) - SQL Server | Microsoft Learn.
• Query Store  - Query Store was introduced in SQL Server 2016 and offers a built-in "flight recorder."  Monitor performance by using the Query Store - SQL Server | Microsoft Learn
• Windows Performance Monitor - Perfmon is built into the Windows OS.  Learn how to use it, schedule it, and which counters are the most important.  
• Windows Admin Center - Windows Admin Center is a fairly new tool exposing great information from the Windows OS, local machine, or remote machine through a web UI. It includes performance monitor counters and additional resources.  Windows Admin Center | Microsoft
• Azure Arc - There may be an associated cost with this one, but this may be an option if your organization is utilizing Azure.  Azure Arc "simplifies governance and management by delivering a consistent multi-cloud and on-premise management platform."  I'm personally very interested in where Microsoft is headed with this tool.  Especially for SQL Server enabled by Azure Arc -SQL Server enabled by Azure Arc - SQL Server | Microsoft Learn.

If there is a budget for third-party tools, capacity planning gets easier.  Below are a few well-known solutions that combine historical reporting, trending analysis, and real-time monitoring:

• Redgate SQL Monitor
• SolarWinds SQL Sentry
• Idera SQL Diagnostic Manager

By utilizing the tools above, you'll better understand today's workload and its trending direction.  

Implement a Proactive Strategy:

Shift from reactive to proactive planning.  Regularly review performance metrics and adjust your strategy to accommodate changes in data volume and access patterns.  

Collecting the data is the first step.  Next, you'll need to review and report on it.  I've seen some cases where the organization collected performance data well, but no one regularly reviewed and made recommendations.  

Here are a few key components of a proactive capacity planning strategy: 

• Set up comprehensive monitoring - Utilize SQL Server's built-in tools or purchase a 3rd party tool.  Get familiar with Perfmon and/or Windows Admin Center.  Use well-known scripts or build your own to set up a daily collection.
• Analyze trends - Use historical data to identify patterns in database growth and workload characteristics.  This helps predict future requirements and identify potential bottlenecks before they impact performance.
• Forecast resource needs - Based on historical trends, estimate future resource needs (CPU, memory, storage, and I/O requirements).  Use these forecasts to plan hardware purchases, cloud resource scaling, or infrastructure upgrades.

This strategy works well by using the past to look forward.  However, business goals and needs change rapidly in some cases.  For instance, if your organization is planning an acquisition, this may affect resource consumption rapidly and lie outside your current trends.  This brings us to our next section.

Align With Business Goals

Aligning your capacity planning efforts with business goals is a crucial step in ensuring that your SQL Server instances not only support but also drive forward your organization's strategic objectives. This alignment ensures that IT resources, particularly database capacity, are utilized to maximize business value, support growth, and enhance competitiveness. 

Here's how to ensure you are in sync with your organization's goals:

• Understand Business Objectives  - Begin by gaining a deep understanding of your organization's short-term and long-term goals.  This could involve expanding into new markets, launching new products or services, or improving operational efficiency.  Engage with stakeholders across departments to understand their needs and how they align with the overall business strategy.
• Involve Stakeholders in Planning - Obtain input from various departments in your capacity planning process.  This goes both ways.  Ensure you're included in communications involving changes to the current SQL Server workload or data ingestion.  As DBAs, we don't enjoy meetings, but you must be part of these planning sessions and initiatives. 
• Forecast Demand Based on Business Activities - Use business forecasts, such as projected sales growth or new customer acquisition targets, to estimate future database workload and capacity requirements.  For instance, an anticipated increase in online transactions may require scaling up database resources to maintain performance.

Plan For Scalability

Once aligned with the business goals, you must plan for scalability.  Here are a few ways I'd approach this depending on if SQL Server is using virtual machines, physical machines, or cloud VMs:

• Virtualization - If your SQL Servers are virtual, work with the system admins to determine current capacity constraints within the virtual environment.  If, for example, your organization expects to grow rapidly within the next quarter, begin cross-department planning to ensure the environment can scale, and the budget has been allocated to allow for the anticipated growth.
• Physical - Again, work with the system admins to determine what resources should be added to the server to support the business goals.  For example, is the current server maxed out on memory, or can more be added?  Don't forget CPU, storage capacity, network, and I/O throughput.  
• Cloud - Is there a budget for upsizing the VM if needed?
• Scaling out - Does the application support a scale-out approach at the database tier?  If so, consider planning for additional VMs, servers, SQL Server licenses, etc, to support growth.

Document and Communicate

Documenting and communicating your strategy is vital for ensuring transparency, aligning with team efforts, and facilitating organizational decision-making processes.  A well-documented strategy helps in the following ways:

• Alignment - Ensures that IT strategies and business objectives are aligned, facilitating more effective planning and resource allocation. 
• Transparency - Builds trust within the organization by making the capacity planning process transparent and understood by all stakeholders.
• Proactive Management  - Encourages proactive management of resources by keeping everyone informed about current capacities and future needs, reducing the risk of performance issues or downtime.
• Continuous Improvement - Facilities continuous improvement by incorporating feedback from across the organization into the capacity planning process.

You can ensure that capacity planning is not seen as just a technical necessity but as a strategic asset that supports the organization's overall goals and objectives.

Conclusion:

Not all aspects of a DBA's role are technical.  Some require thinking ahead, working with other departments, and ensuring you are strategic about enabling your organization's future growth.  

If you haven't done so, I invite you to join the weekly DBA Challenges newsletter -  DBA Challenges - Newsletter (automatesql.com)

That's it for this week.  Have a great week!