Object housekeeping

After my last blog entry it got me thinking about something I have been meaning to blog about for a while, which is object housekeeping. Some time ago I worked on a system that we upgraded from SQL 6.5 to SQL Server 2000, as part of the upgrade we looked at each database object to see if it was still being used.

Applications can grow in a very unstructured manner (what I call the carbuncle methodology and it isn’t a gem stone!"), a developer may not realise or check that a database object is made redundant by an application change, or they may just be “too scared” to remove something in case “something breaks”. Refactoring only tends to be considered when things are already in a mess.

But you should approach housekeeping in your database in the same way you approach cleaning your own house (well I do!). It’s a lot easier if you do a little bit often, rather than a massive spring clean once or twice a year with nothing in between.

The initial pass of our system required trawling through code and DDL scripts. Fortunately we had already put each individual object into a source code control system as an earlier task which helped. Unlike SQL Server 2008’s sys.sql_expression_dependencies looking at the sysdepends table was not reliable and syscomments sometimes breaks a keyword over multiple rows making it difficult to search.

As you may have realised, doing a single pass of the code is not going to root out all redundant objects, as a whole new set of redundant objects may start to appear once the first level is removed, so the task of highlighting redundancy is iterative.

That was fine for references to objects within database code, finding redundant application code was more of a problem. This really required the time and help of a developer. Compiler messages about unreferenced functions may help in this process, but you need to make sure they are switched on! There are also third party tools that can analyse projects.

When we stopped adding to the list, we then went through a process of renaming the objects (we were not that brave!) which meant we could easily undo the change if we were proved wrong (and there was a couple of instance where we had got it wrong). Subsequent releases added to the number of renamed objects, and after a while we were confident enough to remove the renamed objects.

The overall effect of this clear out was we had marked 1/3 of all the database objects were actually no longer used and the application executable size was drastically reduced. Going forward that meant that we could build the application and database significantly quicker and it was a lot easier to manage. It also meant that we had a much more compact database to migrate to SQL Server 2000.