ApexSQL Clean: Impact Analysis and Graphical Dependency Viewer tool

Introduction

Have you ever been given the task to clean up a database from unused and unreferenced objects?

If your answer is no, then you’re probably a very lucky person. However, if your answer is yes, then, believe me, I feel for you! Finding objects like stored procedures, views, and/or user-defined functions, or even columns that are not referenced (anymore) in either the database itself and/or the client code is a very tedious task, that almost no one I know likes to undertake. Even a medium-sized database can call for a lot of trouble and endless hours of work.

Well, sure, there is a build-in system table named sysdepends in SQL Server that is supposed to do exactly this job. But unfortunately, it is not very difficult to bring this table out of synch with reality. So, you cannot rely on the mechanisms SQL Server provides itself.

For example BUG: Recreating a Table Causes sysdepends to Become Invalid or, probably even more significant: http://groups.google.com/group/microsoft.public.sqlserver.programming/search?q=sysdepends.

As you can see from these links, the system table sysdepends has never worked 100% accurately and probably never will, but fortunately there are third-party solutions available that exactly address this issue.

Purpose

The purpose of this paper is to introduce you to the ApexSQL Clean tool. This is one of the few aforementioned specialised third-party tools. Its sole purpose is to help you identify any unreferenced object in a database that can safely be removed.

Some thoughts about why removing unreferenced objects anyway

You might have asked this question yet yourself, why you should remove unreferenced objects in a database anyway. Well, actually there isn’t necessarily a strictly technical reason for undergoing such a laborious task. SQL Server databases can have as much as 2,147,483,647 objects per database and if you get anywhere near this number, I’m tempted to say that you are likely to have a whole bunch of other problems, starting with your logical design and its physical implementation, rather the some probably unreferenced objects.

Sure, any database object will occupy some space on your hard disk, but, frankly, with more and more SQL Server databases heading for the Terabyte border, do you really think these few bytes matter at all? You probably should be more concerned about the number of rows these objects occupy in the internal system tables.

So, when there is no compelling technical reason to remove unused objects, why should you to so? Well, personally I think the most compelling reason is that you keep your databases maintainable. And not only maintainable by someone who works with the database day by day, but also maintainable by someone who has only a limited time to become familiar with the database or who is new to the database. I guess there is hardly a person who exclusively takes care of just one single database. And so most people do split their time between different tasks on different databases. That might however mean, that you don’t have a look at each and every database as long as things are running fine, but when you return to it, what database would you want to find? A database that is well organized and where you know that only these objects are present that are really needed or a database with database objects that might or might not be used by other objects or some client app where you have to spent hours and hours of work to get through the dependencies to find the relevant piece you are looking for. I for one would certainly opt for the first alternative. And I’m certain that the benefit outweighs the cost by far.

Installation, System Requirements, supported SQL Server versions

You can download ApexSQL Clean from the Apex homepage for a fully functional 30-days trial period. The download is just about 8 MB in size and is provided in form of a Windows installer package.

The installation is straight-forward and just a matter of minutes.

The system requirements are as follows:

Software:

ADO 2.8 (or higher)
Microsoft SQL Server 7.0 (or higher)
Microsoft .NET Framework 2.0 (or higher)

Operating System:

Windows 2000
Windows XP
Windows 2003

CPU:

PC at 450 MHz (or faster). Pentium III recommended

RAM:

256 MB RAM (512 RAM suggested)

Hard Disk Drive Space:

30 MB

After initial installation you can play around with the software and test it in your environment for 30 days. After that period, ApexSQL Clean will stop working. If you decide to further use the software, you need to buy it. Based on the last price information (Dec 2006) from the ApexSQL homepage, the Clean tool is quite affordable at $249 for a version with 0 years maintenance and $362 with a 3 years maintenance period. After that, you only need to activate the software with a license key which you can obtain online and you’re ready to use it.

A brief introduction into the use of ApexSQL Clean

Once you’ve successfully installed ApexSQL Clean and have launched it, you find a typical Windows GUI. If you already use some other ApexSQL tool, you certainly do not have any problems getting familiar with the tool as its “Look and Feel” is very similar to, for example, ApexSQL Log.

Continues…

Leave a comment

Your email address will not be published.