What the Developer DBA Needs to Know about Performance

Good performance is built on a solid foundation, which for a SQL database is the schema. A well-designed schema will provide a solid foundation on which the rest of your application can be implemented. The rules to follow are less simple than traditional concepts such as "normalize to the n Normal form." Instead, they require that you have a solid understanding of the use of the system, including a knowledge of the users, the queries, and the data. The optimal schema for an OLTP system will be less optimal for a DSS system, and of no use at all for a DW system.

Users

You first need to know who is going to use the system, how many users there are, and what they are going to do. The users will usually fall into different groups based on either job function or feature usage. For an e-commerce-based system, for example, the user groups might be browsers, purchasers, order trackers, customers needing help, and others.

Queries

After determining the different groups of users, you need to understand what they do, which queries will be run, and how often each query is run for each user action. In the e-commerce example, a browser might arrive at the site, which invokes the home page, requiring maybe 20 or even 30 different stored procedures to be called. When users click on something on the home page, each action taken will require another set of stored procedures to be called to return the data for the next page. So far it looks like everything has been read-only, but for ASP.NET pages there is the issue of session state, which might be kept in a SQL database. If that's the case, then you may already have seen a lot of write activity just to get to this stage.

Data

The final part of the picture is the data in the database. You need an understanding of the total volume of data in each table. You need to understand how that data gets there and how it changes over time. Going back to the e-commerce example, the main data elements of the site would be the catalog of items available for sale. For example, the catalog could come directly from the suppliers' Web sites through an Internet portal. Once this data is initially loaded, it can be refreshed with updates as each supplier changes their product line, and as prices vary. The overall volume of data won't change much unless you add or remove items or suppliers.

What will change, hopefully very quickly, will be the number of registered users, any click tracking you do based on site personalization, the number of orders placed, the number of line items sold, and the number of orders shipped. Of course, it is hoped that you will sell a lot of items, which will result in a lot of new data growth every day.

A sound knowledge of the data, its distribution, and how it changes helps you find potential hot spots, which could be either frequently retrieved reference data, frequently inserted data, or frequently updated data. All of these could result in bottlenecks that might limit performance.

Robust Schema

An understanding of all the preceding pieces - users, queries, and data - needs to come together to help implement a well-designed and well-performing application. If the foundation stone of your database schema isn't solid, then anything you build on top of that shaky foundation is going to be shaky. Although you are unlikely to achieve an optimal solution, you may be able to achieve something that's acceptable.

How does all this information help you tune the server? You need to understand where the hot spots are in the data to allow the physical design to be implemented in the most efficient manner. If you are going through the process of designing a logical data model, you shouldn't care about performance issues. Only when you come to design the physical model do you take this information into account, and modify the design to incorporate your knowledge of data access patterns.