Introduction
This review will be different. I am not going to review one complete software package, but rather focus on 4 different parts of one single software package. The parts which I am concerned here with form the Analysts series of Embarcaderos’ DBArtisan Workbench suite. Namely they are Space Analyst, Capacity Analyst, Performance Analyst, Backup Analyst.
Just by looking at the names of the Analysts’, you see that each one deals with a different major duty of a DBAs’ job.
General system requirements, download instructions and pricing information
General system requirements for a full DBArtisan Workbench installationMicrosoft® Windows NT® 4.0 Server or Workstation with at least Service Pack 4 or later
- Microsoft Windows 2000 Advanced Server, Server, or Professional Microsoft Windows XP
- 130MB of client disk space (full DBArtisan Workbench install)
- 64MB of RAM
- 800 x 600 monitor resolution
- Internet Explorer 5.5 or later (for Performance Analyst and Space Analyst only)
Note that different Analysts’ may need some extra storage space in the range of 2 – 5 MB.
You can download a fully featured 14-days trial version of the DBArtisan Workbench from the Embarcadero homepage. Note that although the Analysts are stand-alone applications which can be started without being within the DBArtisan Workbench environment they are not separately downloadable or purchasable. They are exclusively available in the premium edition of DBArtisan: the DBArtisan Workbench.
Pricing information about Embarcaderos’ DBArtisan Workbench is available on request.
Space Analyst
Purpose
Space Analyst helps the DBA identify, diagnose, and troubleshoot any bottleneck or performance inefficiencies that results from space management.
Installation and supported SQL Server versions
Upon first start of the Space Analyst, it needs to create several stored procedures on the SQL Server instance that is about to be monitored.
These procedures are created in the central repository database that all Analysts’ use and their creation is just a matter of seconds. No custom object is created during installation in any of SQL Servers’ system databases.
Space Analyst can be used against any SQL Server version from SQL Server 7.0 upwards.
Description of Space Analyst
The main GUI of Space Analyst looks like the one displayed above. You get a good first graphical overview of the space state of your databases and the server on which they reside.
By moving the mouse pointer over any bar in any chart Space Analyst displays further information about the underlying object.
The core purpose of Space Analyst is obviously space management. Most of this task is done by the “Space Management Wizard”.
As you can see from the above screenshot, you use the “Space Management Wizard” not only to create new space management jobs, but also to edit already existing ones.
Basically you can choose from the three different type categories you can see above. The differences between these three types of space management tasks are subtle and so I deliberately resort here to quote from the online help:
Type of Space Management Task
| Required Information | Description |
| Create standard reorganization job | Select to build a reorganization job for a database or the objects you specify. |
| Create reorganization analyst job | Select to create and deploy a reorganization job that runs periodically to detect and automatically correct space inefficiencies. |
| Create space maintenance job | Select to build a space maintenance job that validates database structures and performs storage maintenance tasks. |
For the purpose of this paper, I have decided to create a standard reorganization job.
Note at this point, that Space Analyst allows you to include objects from more than one database in one single space management job. While I guess that in production environments you typically will want to handle most of your databases differently in some aspects, I can imagine that this feature of Space Analyst will come in handy for common tasks you want to perform in one go on more than one database, such as reorganisation tasks over the weekend or during off-peak usage time periods. I, however, have chosen to include just one database in this space management job.
This page of the wizard allows you to specify what kind of customizations you want to apply to this job. You can perform no customization, global customization, and you can use this wizard to drill down to the database object level and set custom options for each single object.
  |
For example, you can specify to relocate all table objects to a particular filegroup and/or choose from a variety of index and table reorganization methods.
Finally you can decide how this space management task should be handled. You can:
- Open the script that is about to be generated in a new editor window. Especially in the starting phase when you need to get familiar with Space Analyst you will want to choose this option to see what commands Space Analyst actually generates and would execute against your databases. This option leaves it up to you to run the script against the database. It is neither automatically executed nor scheduled for execution.
- Perform the space management task immediately against the database. This option also opens a new editor window, but, in difference to the first option, the script will be immediately executed.
- Schedule the script for later automated execution.
By now, I have described one core feature of Space Analyst: the Space Management Wizard.
Another core feature is the visual reporting on and analysis of the storage situation of your SQL Server.
Such as the “Map” function. Here the corresponding explanation of this feature from the online help:
“The Map Tab displays a graphical file map that displays extent placement for objects within database files. The Map Tab lets you view a graphical layout regarding the physical placement of every object inside a selected database and file. The map helps identify allocated and unused extents throughout a database file. An object grid is also present that lets you view detailed information about every object’s storage usage in that file.”
Or the “Database Summary”, which provides summarized information about the space state of your databases.
You can generate reports on the collected data. These reports are static HTML files, which can easily be distributed or published.
The generated report would look like the sample report in the screenshot above.
Let’s do a quick rundown on the other available functions in Space Analyst, which I haven’t described in details:
| Menu item | Description |
| Extract | This function “extracts” the DDL for all database objects of a particular database and displays it in a new editor window. |
| Object Details | Displays general space usage for tables and indices, such as total, used, and free space. It also displays fragmentation information. |
| New Database | Self-explanatory. |
| Quick Space Tasks | This function gives you the opportunity to perform a variety of DBCC operations against a database or a database object. Some of the included DBCC operations are:
|
| Shrink… | Self-explanatory. |
Let me end this part by drawing your attention to a very special dialog in Space Analyst: The “Space Analyst Threshold Options” dialog. This dialog allows you to specify certain thresholds that Space Analyst will use in its reports. Once a database or a database object exceeds or drops below a certain threshold, it will get included in the Space Analysis Reports. This is probably best explained by means of an example.
Say, we want to identify those tables in our database with average page density problems. As “problematic” we define a threshold figure of 99 percent (just for illustration purposes only, of course). Note the comparison operator for this threshold. Any table with an average page density less than or equal to 99 percent, will be considered “problematic” by Space Analyst and will be included in the corresponding Analysis report.
As you can see, the Average Page Density of MyTable is with 98.7346% below the threshold of 99 percent and, thus, is included in the Analysis report. If we would have specified a threshold figure of 98 percent, the “MyTable” table object would not be considered “problematic” and therefore would not be included in the above report.
Summary and Conclusion
Key features:
- Collection and analysis of space and storage related information about an SQL Server instance, its databases and database objects.
- Identification and correction of any space and storage issues.
Advantages
- No proprietary objects added to SQL Server system databases.
- SQL Server 2005 ready.
Disadvantages
- It would be useful, if the threshold options would not only be displayed in the Analysis reports, but, optionally perhaps, also be displayed in the different charts of the main GUI.
Space Analyst is a great tool to identify and rectify any space issues and performance inefficiencies related to storage issues. You can perform all major tasks in this field with just a matter of mouse-clicks or create your own jobs and schedule them in order to implement an automated and efficient space management of your SQL Servers.