Debugging Stored Procedures

I have performed many tasks in numerous different roles, and one of the most frustrating has been debugging stored procedures. But no more! The ability to debug stored procedures as though we were debugging any development platform code is part of one of the enhancements to SQL Server 2000's Query Analyzer. We can insert break points, step into, step over, and so forth. This is wonderful for those of us who have tried to monitor what is happening in a stored procedure.

Previously we could do this with SQL Server 7.0 and Visual InterDev, but there was a lot of overhead in setting it up. With the new debugging tools, all we do is right- click and select Debug. How simple is that?

We have the standard debugging windows as well. We can get the values of variables from the Watch window and view the procedures that have been called and not completed in the Callstack window. This makes it easy to migrate from a development environment to using SQL Server 2000. So you Access developers out there must be getting really excited by now!

What? No More Room?

One of the trouble spots a DBA must keep an eye on is conserving a computer's most precious resources, memory and disk space.

If we have several databases on one server, we can find that we run out of disk space, and if that happens, our databases will fail.

Of course, in Spy Net's fictional scenario, that could mean World War III! But in the real world, running out of space can still cause serious problems, especially in mission-critical databases such as utilities or emergency response systems. In this section, we look at the causes of resource failure and several ways to avoid down time, including managing file and log size.

How Memory Affects Database Transactions

The memory-deprived databases will fail because tempdb is where most of the changes that you make to your data are performed before they are committed to disk. If you have enough RAM available, SQL Server 2000 will put as much of your database as it can up into RAM. After all, it is much faster to read from RAM than to scan a disk for the information. However, if RANI is a short commodity or you have concerns about the amount of disk space your database is eating, relax, because we have even have control over that.

When you are in Enterprise Manager you have the option to view how much space your data files for your database are allocated and how much is used. To see this information, simply click the SQLSpyNet (or any other) database within Enterprise Manager, and you will see a screen.

Shrinking Your Data Files to Reduce the Database

When we are talking about shrinking our data files we are not actually referring to the process of compacting them like a zip program would.

If we shrink our data files, we remove unused data pages. For example, if we had a table that had five data pages on which it stored the data, and we deleted two pages worth of the data, although our table would have only three pages that actually stored data, SQL Server 2000 still would have five pages allocated to the table.

When we shrink the data files, we just get rid of the extra two pages that the table was using. This does, however, have restrictions, but I think you get the idea.

What do we do when our data files are too large? Although we cannot shrink an entire database smaller than its original size, we can shrink our data files smaller than their original allocation sizes. We must do this by shrinking each data file individually by using the DBCC SHR I NKF I LE Transact-SQL statement. This allows us to reallocate how much space the given data file is allowed to use.

Caught Between Real and Virtual Worlds

While agreement was breaking out at this workshop, so too were some concerns. As most HCI practitioners would agree the distinction between design and evaluation in HCI is often blurred, indeed design and evaluation have been described as the different sides of the same coin. The design of the DISCOVER system proved to be no different. As we elicited requirements and undertook early design we kept an eye on evaluation, ultimately as a sanity check. The use of the prototype, described above, had given us clear usability requirements. The affinity diagram had given us detailed requirements of the design, content and substance of the DISCOVER collaborative virtual environment but concerns as to the issue of evaluation (and with it validation) began to emerge.

Design Tensions: No Magic, Thanks

For many collaborative tasks in virtual environments, the goal is to achieve a particular state of affairs within the virtual world, for example agreeing the layout of an office, as in Hindmarsh et al. (1998). For others, the activity within the CVE is part of a larger collaborative process, but the way collaboration works within the CVE need not exactly replicate real world interaction. In contrast, the DISCOVER environment must support the acquisition of real world skills. In short, skills acquired in the DISCOVER CVE must be transferable to the real world of the ship. This presents a significant constraint: ideally, interaction and collaboration must not be artificially harder or take longer than in the real world, but neither must they be artificially easier or executed faster. Thus the design of DISCOVER should treat with caution "magical" devices such as birds' eye views of the state of environment, Star Trek-like transporting, or visible rubber banding between an avatar and its current focus of attention.

Accordingly, much research which has addressed the problems of ensuring that the users of such environments are aware of their surroundings and of other users cannot be employed directly. Consider, for example, the problems with recognizing other avatars. To maintain realism, avatars cannot be simply labelled. There may also be the presence of dense smoke, and perhaps the need for the avatars to wear vision obscuring breathing apparatus. All of this means that recognizing one's colleagues (as avatars), mediated in the real situation by such characteristics as gait, stance and minor variations in standard issue clothing, becomes far more difficult.

Thus there is a fundamental tension between exploiting the technology to the full to produce a state-of-the-art virtual training environment and creating one which is faithful to the behaviour and constraints of a real ship. This, of course, is not merely a design issue but also presents a corresponding evaluation challenge.

The Customer Lifecycle

How do you monitor your customer throughout the lifecycle? Each customer enters the buying process at a different stage. This is most apparent with technology products and is not necessarily transferable to a commodity.

However, the Internet is, in essence, a technology product from an adoption perspective. Understanding how the customer interacts with your site or your products and when will help you target the correct intervention point. Pinpointing your communications to lifecycle stages will avoid a shotgun effect and will solidify your relationship with your customer as an evolutionary process.

Identifying Your Most Valuable Customers

You can identify your top customers by analyzing historical sales for dollar volume and frequency of repeat purchases. The data must be carefully analyzed because repeat customers can be more valuable over time than one-time, large ticket item purchasers. This analysis must then be matched up with the demographics and other attributes and characteristics you've previously identified in your customer segmentation. Integrating all of these elements gives you a good picture of your key customers. You will also need to include customers you want to have in your cultivation plan.

The opportunity equals the combination of customer retention and the value of all future profit plus those customers you deem to be potentially valuable over the lifecycle—minus the investment required to keep and to acquire them. It also includes future profit made by referrals. Prospective customers who visit your website prior to purchase are much more likely to become buyers than to become non-visitors.

Investments in personalization, promotions, and communications depend on the ranked value of your customers.

It's equally important to establish the identity of the consumers that do not measure up. Spending time and money on this group drains the company of the ability to cultivate new and more attractive customers.

Direct mail companies use a similar technique to determine their mailing investment to each customer segment. After dividing the customer base into ten equal groups, they then rank based on profit potential. These companies must ensure the cost of catalogs is worth the ultimate customer value.

After customer segments are defined, the data are incorporated into future forecasts to make product selection, pricing, inventory management, and promotion decisions.

Business-to-Business Relationships

Up to this point, we have used the terms "customer" and "consumer" interchangeably. Consumer package goods companies normally focus on the consumer as the end user and reserve "customer" for other businesses, resellers, and distributors. Manufacturers and retailers with business-to-business relationships know that these customer relationships can be among the most profitable.

A one-to-one system with a company must still focus on an individual. Each functional area will approach the relationship uniquely. Depending on whether you're dealing with a professional purchaser, a technical specifier, or an administrative assistant, the customer's needs will vary. Simplifying routine purchases by offering custom lists or access to prior purchase invoices is critical for those tasked with frequent purchasing responsibilities. Ordering expensive or technical products may require justification and additional information.

Environmental information should be easily accessible along with health and safety materials data. Customized web pages address individual company requirements and purchaser needs. Automated ordering and delivery systems can be linked to other systems for integrated purchasing, billing, and supply-chain management.

Many mid-sized companies worldwide will use or plan to use the Internet to purchase supplies and equipment in the near future. While personal calls by sales reps could be construed as true one-on-one marketing, busy business managers perceive time as money. They don't want a sales pitch: they want to order and receive services their way. Convenience, speed, and cost are the main drivers.

Usage Behavior

We've examined shopping behavior, lifestyle, demographics, and other factors that influence customer purchasing and loyalty. How products are used, at home or in business, may also have an impact on web-based shopping. For instance, if a web designer knows that a customer routinely purchases a spare product in addition to a regular order, he may bundle two products together or offer an online reminder. If segment data confirm that most customers purchase two products together, a promotion may be offered to customers still purchasing the single item.

Historical purchasing data will be useful to monitor usage behavior. The best way to thoroughly understand customers and how they use the products they purchase is to visit representatives of your top segments in their homes or offices. Following them for a day or two will give you invaluable insight that you cannot achieve in a sterile test environment.

The analysis of customer behavior over time must be measured to get an accurate picture of usage. This is best managed with a permanent program rather than a one-time research project.

Predicting the Success of the Customer Relationship

Relationships between customers and merchants are very much like other typical relationships between people. Understanding what makes a relationship successful with your employees, spouse, children, or co-workers helps you determine how to measure a good relationship with a customer.

Relationship studies conducted by the University of Denver indicate that marital failure is predictable to a surprising degree. This means that, for many couples, the seeds of divorce are present prior to marriage. The study was grounded in understanding how to prevent marital failure and determining how prediction can lead to better interventions designed to help couples reduce the risk of divorce.

Dr. Howard Markman, Department of Psychology at the University of Denver has identified the steps in a relationship and potential danger signs and patterns in his book, Fighting for Your Marriage: Positive Steps for Preventing Divorce and Preserving a Lasting Love (JosseyBass, 2001).

Once a relationship is established by two partners, problems can develop. Escalation occurs, which can be resolved, ignored or neglected. Continued neglect implies invalidation, and contempt usually follows. Negative interpretations, withdrawal and avoidance lead to indifference. These are danger signs and patterns of a relationship headed for failure.

The same patterns and principles can be applied to the customer relationship. By reducing risk factors and increasing protective elements, you can preserve a fragile relationship. How the customer "feels" at every point of contact with an online store influences the relationship. Points of contact can be shopping on the website, asking a question of the customer service department, returning a product, or tracking an order. At each of these touch points, a customer must feel important, appreciated and respected.

The large group of people called "customers" must now be narrowed down to specific individuals. When you think of these individuals less as "targets" and more as "partners," you begin to get the idea of how powerful a relationship can be. The customer is not someone we do something "to"—the customer is someone we do something "for." It's a symbiotic relationship in which we have to give something of value.

Shrinking the World

If e-commerce mirrored the actual composition of the world, it would be very diverse. Marianne Williamson, noted author and speaker on world issues, presents a new way to look at the people of the world:

"If we could at this time shrink the Earth's population to a village of precisely 100 people, with all the human ratios remaining the same, it would look like this:

1. There would be 57 Asians, 21 Europeans, 14 from the Western Hemisphere (North and South), and 8 Africans.
2. Seventy would be nonwhite; 30 white.
3. Fifty percent of the entire world's wealth would be in the hands of only six people. All six would be citizens of the United States.
4. Seventy would be unable to read.
5. Fifty would suffer from malnutrition.
6. Eighty would live in substandard housing.
7. Only one would have a college education.

As web shopping gains adoption throughout the world, human factors engineering becomes even more critical. The Internet used to be a domain of technology aficionados and game-playing teens. Truly understanding the customer and designing for the future demands that you stay abreast of the changes in the population at large as well as feel the pulse of the shopping environment.

Major changes are usually addressed at the strategic level of a business. Yet, as soon as a belief about the customer is ingrained in a corporation's culture, it tends to be perpetuated. That's why many major corporations still believe that Internet use in the U.S. is dominated by men.

Subtle changes are even more difficult to acknowledge or track. Continual customer contact, whether through individual or group research, is the primary guard against complacency. Monitoring secondary research and staying in touch with customer advocacy groups will give dimension to growing customer needs.

Often, one channel will spot change faster than another. Even if your company uses only one channel—a pure-play web merchant, for example—it's still critical to understand how the customer shops in a variety of modes, what they prefer in each, and how a best practice can be replicated in another domain. Each channel can learn from the other because they take different approaches toward helping the customer have the best possible shopping experience.