Capacity planning, especially when done for Microsoft SQL Server, is not for the faint of heart. Storage and disk capacity are apparent, but to right-size databases for a new SQL Server implementation or migration, there is a lot more than space to consider. Of course, sizing and future growth need to be considered. Also, it is critical to review the design, configuration, and other essential resources to ensure that the new SQL Server is performing optimally. Plan for future capacity for SQL Server with IDERA SQL Diagnostic Manager.
The Capacity Usage chart in the Summary view of the Database tab displays the usage of data and logs in the databases. This chart provides information on the text, tables, and indexes space, as well as the unused space of the databases. Refer also to the documentation View your SQL Server databases information.
Analysis reports provide information to analyze the current effectiveness of SQL Server systems. Analysis reports are instrumental when performing a consolidation or moving to another environment (such as the cloud). Refer also to the documentation Analysis reports.
The Baseline Statistics report analyzes and compares baselines within a single SQL Server instance and across two instances. When viewing baseline statistics for a monitored SQL Server instance, compare the baseline metric values at two different times or two different metrics at the same time. Include another instance and compare baselines values occurring at the same time or different times. View trends in the average value of a metric for a SQL Server instance and how this value changes over time for capacity planning. Baselines can be calculated related to past performance. Additionally, multiple baselines can be set up to provide a better understanding of what is "not normal" taking into consideration the time of day (for example, production hours versus maintenance hours). Refer also to the documentation Baseline Statistics server analysis report.
Use the Plan reports to forecast for future needs based on historical growth trends. Exploit the history of the collected data to predict the amount of space consumption required at the table, database and volume levels for a point in time in the future.
Choose linear or exponential (aggressive) forecasting types when running Plan reports.
Refer also to the documentation Plan reports.
The Disk Space Usage Forecast report forecasts disk space needs. This report predicts how much disk space is needed in the future based on current and historical growth rate trends. Run this report to help anticipate the needs and plan for future acquisition of disk space.
The Database Growth Forecast report forecasts future database growth. This report predicts how large selected databases may grow based on current and historical growth rate trends. Run this report to help anticipate the needs and plan for future database expansion.
The Table Growth Forecast report forecasts future table growth. This report predicts how large selected tables may grow based on current and historical growth rate trends. Run this report to help anticipate the needs and plan for the future creation of additional tables.
Every environment is different. SQL Diagnostic Manager has canned metrics that are monitored and collected historically for review, and canned reports. Having the ability to extend this monitoring footprint is paramount. Obscure monitoring requirements will be necessary. Having a tool that adapts to the environment is crucial.
In addition to monitoring a wide variety of the most common SQL Server and operating system performance metrics, add additional performance metrics via custom counters. Add any Windows system counters including any Performance Monitor and Windows Management Instrumentation (WMI) counters. Also, add any SQL Server system counter stored in the "sysperfinfo" system table, and any numerical value that custom SQL scripts return. Also, add and any performance counters accessible through the virtual machine and host server. Refer also to the documentation Use custom counters to track metrics.
Create or edit custom reports. Include in custom reports any collected metric, including custom counters. Choose the counters to include in a report, order the way the metrics appear, and specify the aggregation method used on each of the metrics. Refer also to the documentation Custom reports.
Refer also to the webcast Capacity Planning Done Right for SQL Server.
Penton Tech contributing editor and industry expert Robert Pearl digs into what you need to equip yourself with – and how you need to plan – before you deploy your next SQL Server. This will include disk size, memory, I/O, indexes, transaction logs, file growth, current utilization stats, and more.
Subsequently, Bullett Manale from IDERA will discuss how to gather data concerning the current usage and growth trends of databases for capacity planning. In this session, you will view database capacity usage for data and logs; see database file statistics for available and used space for data and logs; review and compare metric and baseline statistics; identify fast-growing tables; predict future disk space, database, and table needs from growth forecasts. You will also see how to create custom usage and trend reports using any pre-defined and custom counters.
Download a fully functioning 14-day trial of SQL Diagnostic Manager, request a one-on-one demonstration, or request a price quotation.