How much of your time is spent dealing with disk space management issues on your SQL Server machine? What methods do you use to identify which SQL Server databases contain lots of unused space? Do you have the space usage statistics necessary to allow you to accurately forecast the growth rate of your SQL Server databases? Do you have the tools needed to tell you how your database is fragmented? If you don’t have any automated tools, or spend lots of time answering these questions manually then you need to know more about how Quest Capacity Manager software can simplify your disk management tasks for SQL Server.
Quest Software just released a capacity management tool for SQL Server known as Quest Capacity Manager (CM). Version 1.0 of CM was release in February 2006. CM simplifies the effort of managing disk space usage within SQL Server. Version 1.0 of CM supports SQL Server 2000, as well as SQL Server 2005. In this article I will review CM and will give you my opinion of it strengths and weaknesses as a tool for performing SQL Server capacity management.
CM performs real time disk space usage analysis of SQL Server instances, as well as can be set up to capture snap shots of space usage over time. CM uses the capacity usage information to provided rich graphical views and/or textual information for your servers and instances of SQL Server. CM presents space usage data in the form of pie and bar charts, which helps you to quickly identify how your servers and databases are using and consuming disk space. In addition to the graphical information, CM also shows you the detailed tabular data, for those of you that prefer to have the numbers over graphs. In addition to real time disk usage information, CM is also able to perform trend analysis, and can predict when your server and/or database will run out of disk space.
CM allows you to perform the following tasks associated with viewing and collecting disk capacity information:
- Import Server Registration
- Create Repository
- Migrate data from legacy repository
- Collect space usage data
- Analyze Fragmentation
A wizard is available for each of these tasks to guide you through the selected task. These wizards allow you to be productive in using CM with little or no experience with the tool. Online help is available to help out when needed.
CM stores its space usage information in a repository that lives in a SQL Server database. The repository is created when you run the “Create Repository” wizard. To track historical growth of your databases over time, you will need to use the “Collect Data” wizard to create a data collection job. The data collection job will store a snapshot of your space usage information into the repository. You have control over what data you collect and how often the collection job should run. CM interfaces with SQL Server Agent and builds a SQL Agent job for collecting the disk space usage information. By collecting snap shots of usage information over time, you will be able to use CM to perform trend analysis.
You do not have to collect trend data to get capacity reports out of CM. CM provides real time analysis, which reads directly from a SQL Server instance to provide you with an overview of how your disk space is currently allocated for an instance of SQL Server. CM presents this overview with pie and bar charts, as well as a tabular format. Below is an example of an “Overview” look at the instance level for a SQL Server instance running on my desktop machine:
Here you can see CM displays a number of different charts showing the distribution of data and log space across my entire instance. Additionally CM shows the allocations of data and log space for the five largest databases. CM allows you to specify the number of databases you would like to see in the “largest databases” chart. CM is totally configurable. It allows the DBA to specify any forecast, chart and threshold options a shop might desire.
Using the “Overview” display of an instance allows the DBA a quick method of determining the distribution of disk space across an instance. When disk space is limited, this overview gives you the information needed to identify which databases are consuming most of the disk space. By obtaining this information, the DBA can take corrective action should a disk space crisis occur. Using CM allows a DBA to quickly identify and resolve a SQL Server outage caused by lack of disk space.
Tracking capacity information over time is useful for predicting how fast your applications are consuming disk space. CM allows you to create a “collect data” job that runs on a schedule to capture disk space usage information. CM then can uses this historical data to perform trend analysis to predict when additional space will be needed. Using CM trend analysis data allows the DBA to predict when more disk space will need to be purchased and how much. CM provides you with the data you need to support your purchase request with real statistical growth data.
CM is able to help you with managing fragmentation on your server. CM has a wizard to analyze the fragmentation of your databases. This wizard allow you to specify your fragmentation thresholds, identify the databases you want to watch, schedule when to review fragmentation, and who to contact when your fragmentation thresholds have been reach. When CM identifies that a database has reach one of your specified fragmentation thresholds then an alert is sent via email. This notification allows the DBA time to resolve fragmentation issues before they become performance problems.
In addition to gathering, storing and reporting on space usage information, CM also has the following wizards to help DBAs perform tasks to manage the space allocations of their databases:
- Shrink database
- Reorganize indexes
- Relocate Objects
- Reorganize Heaps
- Partition management (for 2005 Enterprise or Developer edition only)
These wizards generate the actual TSQL commands necessary to perform the above administrative tasks. The wizards let you execute these tasks immediately or schedule them for a later time. Having CM generate the actual commands for these tasks eliminates the need for a DBA to build and implement these routines, therefore saving the DBA time.
With any 1.0 version of software, you expect to find bugs and shortcomings. CM is no exception to this rule. When I communicated the bugs/issues I found to Quest Software, I found them dedicated to fixing these problems, and growing this tool. In fact, not only is Quest Software fixing a number of the bugs I found, but they are also enhancing the tool to collect CPU, I/O and memory usage as well. The next version of CM is targeted to be available in July.
The biggest issue I uncovered with CM was it takes a long time to build the data collection job (SQL Server Agent Job) when monitoring a server with a large number of databases, tables, and indexes. My SQL Server Agent job contained over 7000 steps, which SQL Server Agent had problems handling. To eliminate this issue I had to minimize the number of objects monitored. I have heard that the data collection mechanism is going to improve dramatically in the next release of CM, which is scheduled for early July.
Another area where CM falls short of my expectation is in the online documentation department. Even though CM has lots of online help, the documentation lacks depth. The documentation provides only enough information to walk you through the feature of CM, but does not describe the tasks in any detail. The “why” you would need to use this feature or technical specifics of why a specific feature is useful would greatly add additional value to their documentation, but that kind of information is not available.
CM for the most part is a good tool to help DBA’s with capacity management. The graphical representation of capacity and trend information is exceptional. If you are looking for a capacity management tool, I would suggest you download the trial version of CM and give it a test drive prior to making your purchase.