SQL Server Profiler and Query Analyzer

This is an introductory lesson in using the SQL Server Profiler and Query Analyser.
It would make a great introduction to support staff.

However I would like a more detailed article. This only touches the surface and goes one step beyond the obvious. I would like to see a more detailed article on this. In particular how to save a trace so that the profiler can use it – useful tips like what events are required – the basics that Microsoft forgot to put in their help files.

The article does reference “Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook by Ken England (Digital Press, 2001)

Codegen at Runtime

Codegen is usually used at build time to produce source code for an application.
However I have recently found a use for it at runtime (although infrequently).

The application that I work on has an archive process that takes the older data away to a secondary database. Periodically a new archive database is created.

We used to use dynamic sql within a stored procedure to move the data from the live to the archive. Strings were constructed within a stored procedure and executed. This turned out to be too slow.

I replaced the dyamic sql with a set of stored procedures that were regenerated at archive creation time using XSLT. This is exactly the same technique as traditional code generation, but  has provided a massive speed increase (3X to 7X improvements have been seen).

TDD Pragmatic Approach

Test Driven Developement is a development pratice in which the test are written before the code. The Cycle goes:

  1. Write a test, ensure that it fails [RED]
  2. Make the smallest possible change to get the test to work [GREEN]
  3. Refactor. Simplify the code to eliminate duplication and keep the design clean [REFACTOR]
  4. Repeat

The biggest mistake that people make when trying to get into test driven developement is to try to write all of the tests up front. This process is all about small error free steps.
The trick is to keep the tests small and focused. Don’t try to test too much with one test. If they are too fragile then you will spend your entire time rewriting your tests. As I write the tests I oftern find myself with a substantial helper object that performs the common tasks for me. The tests end up as documentation for the system.

Another important thing to remember is that this cycle is the formal version akin to the full blown scientific method:

  1. State Hypothesis
  2. Design an experiment, then perform it
  3. Identify whether Hypothesis is true or false
  4. Repeat

In addition if you have a project covered with automated tests adding new ones becomes simpler. It is especially powerful if you need to maintain several branches of a given codebase. If the helper object can keep it’s interface untouched across the streams then the tests can stay largely untouched.

I have sucessfully used this when porting an application from one database to another, but the technique would work in the same mannerif you were to change langauages. The trick is to create an abstract interface to test against. You write helper objects that implement that interface against the old and new systems. You write the tests against the old system and then implement the new in order to get the tests to build.


Dia is an open source diagramming tool on the edge of becoming a mainstream utility.
It does most of what Visio does but has a superior price tag.
It still has it’s rough edges but it looks like a promising project.

Codegen and Generative Programming

Kathleen Dollard’s CodegendotNet seems to satisfy Generative Programming Generators definition. Generative programming was a book that aimed to set the stage for improvements in productivity. Kathleen Dollard has provided examples to demonstrate how this technology can work. The main problem is that her demo works too well and people are trying to use it as the code generation template.

Here is the overview of codegen as implemneted in an OO-language: here