Capacity Management

Capacity Plan

Capacity management process should be followed for all production and non-production SQL Servers.Capacity planning can help identify performance and capacity issues early on.Capacity statistics should include CPU/Memory/Storage

/Network capabilities along with other configuration related to the application (Version upgrades, 64bit vs 32bit etc…).Capacity planning is pro-active whereas performance tuning is mostly reactive.Capacity planning is anticipating demand ahead of time and recommending suitable changes to the environment.

1.       For new SQL Server or database implementations, Determine capacity requirements:

a.       Define required system performance thresholds to support     

       associated business and process workloads.

b.      Agree upon performance measures to assess with defined system 

      performance requirements

c.      Test the current system against defined business requirements     

      and make adjustments where needed.

d.      Agree upon minimum levels of service (capacity) the system must    provide

2.       For existing SQL Servers, Analyze current capacity:

a.       Check existing usage of system/database resources (Collect Stats)

b.      Record and track utilization of existing system resources (Create Baseline)

c.       Understand historical capacity utilization

3.       Plan for future capacity :

a.       Forecast  expected workloads and database growth

b.      Plan for future system usage

Thresholds and Statistics

Capacity Thresholds and Statistics should be gathered for each SQL Server instance below are some of them :

• ·         CPU Utilization,
• ·         Memory,
• ·         Disk Space Usage (OS, Database),
• ·         Pages/Sec,
• ·         Pct of free time,
• ·         Disk I/O
•       Network throughput