Capacity Analyst
Purpose
Capacity Analyst examines analyzes and reports on all things’ capacity management related.
Installation and supported SQL Server versions
Upon installation of Embarcadero’s Capacity Analyst several proprietary database objects are pushed onto the SQL Server instance. These objects include primarily some proprietary stored procedures and a few custom tables to hold the data the Analyst needs to work properly.
As you will note throughout this review, no Analyst will create the objects it relies on silently in one of SQL Server internal system databases. You always have the chance to specify where you want these objects to be created. To me, this is certainly a huge plus. The creation of user objects in system database is not regarded as “best practise” and can make security and maintenance more complicated.
Thus, the Installation Wizard prompts you for the database where the objects Capacity Analyst needs should be pushed onto. You can create a new Repository database or choose an already existing one.
The rest of the installation process is straightforward and I will skip it here with one exception. As you can see from the above screenshot is the Repository database password protected. The Capacity Analyst comes along with its own security and at this stage of the wizard you need to supple the owner’s login password. You can change the security after installation via the “Manage Repository Security Interface” I will return to this interface in just a moment.
Embarcadero’s Capacity Analyst supports all SQL Server versions from SQL Server 7.0 upwards. Despite the product documentation, it works fine with SQL Server 2005. Embarcadero is aware of this missing piece of information in their documentation and you can expect the addendum of SQL Server 2005 as supported version very soon.
Description of Capacity Analyst
The ability to analyze current trends of database usage and interpolate future forecasts from these trends is the key feature of Capacity Analyst. It lets you collect almost any piece of data related to capacity management.
When you have just installed Capacity Analyst, first thing you need to do is to specify a new capacity collection. A capacity collection is a set of collectable information you can define by using the “Capacity Collection Wizard”.
From the context of the main grid choose “New” to invoke that wizard.
On the first page of the wizard, you give the collection a custom name and (optionally) a description.
On the next page you can select for which objects you want to collect more data.
As the above screenshot illustrates, is Capacity Analyst able to collect information in one collection from more than one database. I’ve chosen to collect data from my review sample database.
You can either collect the data by owner or by object. The selection you make here on this page of the wizard has an effect on what objects will be data collected. Choose:
- “Collect by Owner” if you want to analyze a certain user or schema with all of the objects owned by that particular user or schema.
- “Collect by Object” if you want to select certain object from all available objects.
If you have specified to collect SQL Server Performance Statistics on page 2 of the Capacity Collection Wizard, you are now able to refine your selection and choose from virtually any available performance counter.
To ensure that the collected data is up-to-date at the time of collection, you can tell the Capacity Analyst to force SQL Server to refresh its statistics right before the data is collected. This guarantees that the data you collect isn’t already outdated at the time of collection.
In cases, when you do not need a long-running history for your analysis, or when the data you collect becomes too voluminous, you can specify that this data is automatically deleted once the time period for keeping that data has expired.
Now that all specifications for the new capacity collection have been made, you decide if you want to create a schedule for this collection to run it at regular intervals.
The schedule dialog is similar to the one in SQL Server, so there is no need to explain its functionality in greater details.
As soon as you have saved your capacity collection, the main grid is updated with this information and will display information similar to the ones in the screenshot above.
No matter, if you have created a schedule for a capacity collection or not, you are always able to start the collection process manually.
A very useful feature is the “Clone” feature in Capacity Analyst. Imagine you have a large SQL Server installation with many databases you want to analyze. In either case having to (re)create a capacity collection over and over again is a time-consuming task that is prone to errors due to its manual nature. To save time and avoid errors Capacity Analyst allows you to clone a particular capacity collection. That means you can create a “master” collection with all the metrics you want to be collected and just clone this master collection each time you wish monitor another database.
One of the key features of Capacity Analyst is the ability to turn the collected data into meaningful charts providing both historical and forecast analysis. This is managed in Capacity Analyst by the Analysis Viewer.
As you can see in the above screenshot, you can move the mouse over any chart line and Capacity Analyst will provide you with detailed information about that particular metric. If you want to modify the metrics that are displayed in the Analysis Viewer, just do a right-click with the mouse in the “Capacity Metrics” grid.
This will bring up the “Capacity Metric” dialog which lets you specify what metric should be added to the analysis and what aggregation will be performed on the collected data.
Note the “Save As…” and “Open” buttons. Here you can save the currently selected set of metric to a file or load a previously saved set. These features are very handy when you need to move metrics selections from one machine to another.
By now I have performed an analysis on the collected historical data. While this already itself provides good information about usage patterns in your databases, probably even more interesting and important is the ability to forecast these figures for the future.
Capacity Analyst makes this task very easy to perform. Just change the “End Date” and the “To” date to any date in the future you want to use. In my example, I changed both dates to 4/20/2007, which is a forecast of one month in the future (at the time of writing).
That is all I have to do. Once the future dates are entered, the Analysis Viewer starts to work and extrapolates trends based on the collected historical data into the future for the chosen metrics. These future figures are graphically represented as the graph lines coloured in red. As with all these sort of trend analysis, you are still responsible for interpreting the presented data and carefully judge on these figures. They are based on statistical methods but they may or may not become reality. However, this ability of Capacity Analyst to create forecast statements can give you a very good impression about where your server will be at a certain point of time in the future, so that you can timely organize everything that might be needed to organize.
Next point in Capacity Analyst I want to draw your attention to is the reporting function.
As you can see from the above screenshot you can choose between a summary report and several predefined reports that ship with the software. I’ve chosen to create a summary report.
Reports are generated in static HTML format, which means that they can easily distributed to interested parties or even published on an intranet.
Some lines above I have mentioned the “Manage Repository Security Interface” to which I now want to return to:
Capacity Analyst ships with its own security management. This security management will affect the ability of deleting capacity collections, purge historical data, or manage the privileges of other users.
The “Manage Repository Security” dialog is intuitive and doesn’t need much explanation itself.
Summary and Conclusion
Key features:
- Collection and analysis of different capacity metrics.
- Ex-Ante and ex-post analysis of collected data.
Advantages
- No added objects added to SQL Server system databases.
- SQL Server 2005 ready.
- Statements about future usage patters can easily be generated.
Disadvantages
- The documentation could be more “descriptive”. Some screenshots, especially for the Analysis Viewer topic, would be helpful.
Capacity Analyst is a mighty tool for any serious and professional enterprise DBA. The ability to be able to collect almost any possible capacity-related metric and quickly generate statement about past, present, and future usage patterns and trends is an essential plus and can help to avoid any capacity problems before they even arise.