The Debugging Process

The objectives of the debugging process are to identify and resolve the defects present in a software product. This process consists of two phases:

• Identification

• Resolution

Identification

The identification phase consists of two primary activities:

• Stabilize the error.

• Find the source of the error.

Stabilize the Error

In most cases, identifying the error consumes 95 percent of your debugging time, whereas fixing it often requires just a few minutes. The first step in identifying an error is to stabilize (or isolate) the error. You must make the error repeatable. What this means is that you must find a test case that causes the error to recur predictably. If you are not able to reproduce the error, you will not be able to identify its cause nor will you be able to fix it.

But we need to qualify the test case in another way. It is not enough to create a test case that will cause the error to occur predictably. You must also strive to simplify the test case in order to identify the minimum circumstances under which the error will occur. Refining the test case is certainly the most difficult aspect of debugging, and cultivating this skill will greatly enhance your debugging efficiency, while removing a large part of the frustration. Stabilizing the error answers the question, "What is the error?" With this knowledge in hand, you can go on to answer the question, "Why does the error occur?"

Find the Source of the Error

After you identify the minimum circumstances under which the error will occur, you can proceed to find the source of the error. If your code is properly structured and well written, this search should not be a difficult task. You can apply a variety of tools at this point:

• Your brain The most important debugging tool at your disposal is your brain. If you can follow the program's execution and understand its logic, you will be able to understand the problem as well. When you have learned everything your test cases can teach you, you can create a hypothesis, and then prove it through further testing.

• SQL Server Some errors will be clearly reported by SQL Server. Be sure that your client application picks up and displays all error messages reported by the server. Also, try using the Query editor window to execute your stored procedures without the client application. Naturally, you should take care to use the same parameters that were passed from the client application when you produced the error.

• SQL Profiler Some errors will occur only when the application is executing stored procedures and queries in SQL Server. Too often, the application does not properly collect all error information, and then application and database developers play ping-pong, blaming each other for the reported defect. SQL Profiler can resolve such disputes. It can be configured to collect information about events such as stored procedures, transactions, and server, database, and session events. When you analyze the collected data, you will be able to determine which of the stored procedure's calls and parameters are responsible for the individual errors.

• Transact-SQL Debugger An integral part of Visual Studio 2005 is the Transact-SQL Debugger. It enables you to set breakpoints in your code and pause execution to investigate and change the contents of local variables, functions, and input and output parameters. The Transact-SQL Debugger lets you step through the code of your stored procedures and triggers. It is unfortunate, however, that Management Studio does not contain a debugger, as Query Analyzer did.

Resolution

Resolving defects in code is usually much easier than finding those defects, but do not take this phase too lightly. At this point in the development cycle, when the product shipping date is looming large, you may be tempted by the "quick fix." Resist this temptation: it often causes developers to introduce new errors while fixing the old ones. It is seldom an issue of carelessness or incompetence, but rather of increased pressure to fix and ship a product.

The resolution phase consists of two primary activities:

• Develop the solution in a test environment.

• Implement the solution in the production environment.

Develop the Solution in a Test Environment

To consistently resolve defects in your code, you need to assemble two critical ingredients—a test environment and source code control.

• Test environment SQL Server is especially susceptible to errors generated in haste to solve a problem because a stored procedure is compiled and saved as a single action.

• If you are trying to resolve defects on the production system, you are performing brain surgery in vivo. Although it is possible to perform fixes in a production environment, it is always much better to step back, spend adequate time understanding the problem, and then attempt to solve the problem outside of the production environment.

  If a test environment does not exist or if the existing test environment is outdated, you may be tempted to save time with a "quick and dirty" fix. Before you go down this path, however, you should consider the resources that would be required to reverse the changes made if you happen to make a mistake. Anything you do, you should be able to undo quickly and easily.

  Let it be understood, loud and clear: you need a test environment!

• Source code control Keep source code of your procedures and database objects. Source code control gives you a snapshot of your application at critical points in the development cycle and allows you to "turn back the clock." It gives you the ability to reverse changes if you find they have introduced new problems or failed to solve the existing one. Visual SourceSafe, which is examined in Chapter 17, is a perfect tool for this function.

  Source code control works best if you take a patient approach to debugging. You should save versions often to help you identify the source of errors when they occur. It is a poor practice to make multiple changes per version. Old and new errors tend to overlap and lead you to incorrect conclusions.

Implement the Solution in the Production Environment

Once you are satisfied with the change, you should implement it in the production environment. Then test. Then test again. You should not assume that it will work in the production environment because it worked in the test environment. If, after stringent testing, everything is still functioning properly, you should then look for other places in the code and database structure where similar errors may exist.