Building and operating a performant SQL Server-based application requires strong process and engineering discipline. Many approaches to performance engineering—techniques such as bottleneck analysis and wait-state analysis—apply only after the system has been developed, when it's more challenging and expensive to address design problems. But if you create an ongoing performance-engineering process, you can anticipate certain behaviors and characteristics, applying a variety of design techniques and monitoring and maintenance techniques that will ensure proper design as well as smooth running operations.
Of course, the holistic process includes more than just technical steps. You need to include steps for covering formal release processes to ensure that you promote only tested applications to production; recommending that you have a forum with users for discussing upcoming business events that will affect the application; and insisting that you document any installation or operational actions to ensure good performance from the get-go. But when you get down to planning your database-design process, you need to have a laundry list of essential technical items. In this article, I cover the items you need on that list. In an upcoming article, I'll cover the essential pieces of setting up performant indexes, maintenance, and statistics for your environment.
The Life of a Query
If I were to oversimplify the steps to good database performance, I'd look start
by looking at the "life of a query" and think about the process of statement
execution. Fundamentally, the key to great performance is to help SQL Server
minimize the time it takes to save or retrieve your data. In addition, you want
to minimize the overall resources used and reduce response time. In simplifying
this idea, I focus on four primary performance areas: statistics, maintenance,
indexing, and database design. After a system is in production, you can make
changes in some of these areas with little trouble. Other areas require schema
changes and might be difficult to do at any time other than during initial database
design or during a major revision of your database application.
These four performance areas flow into each other naturally; statistics are updated when maintaining a database, having the right indexes ensures that the maintenance will be effective, and an appropriate design will simplify index design and even make specific indexing features available. But database design is really the foundation on which you build. Some performance problems can be helped with indexes, maintenance, or statistics, but a truly scalable system must start with good design.
Application design traditionally consists of two steps: you develop a logical model of the business process you're automating, then you map that model to the database by creating a physical model, which is implemented as a series of tables. (To learn about database modeling, see Related Reading, page 14.) You can choose from many modeling techniques. In my experience, all modeling techniques provide a means to interact with the system's users in terms they understand, but all fall short when it comes to efficiently mapping to the physical database. I think that this gap exists because all the methodologies tend to be database agnostic, but to get true performance, you have to understand how to leverage the specific features provided by the underlying technology. Equally true, you must understand the system and how it's being used.
For example, consider the following scenario. At a conference, someone asked me how I would index a specific entity relationship diagram (ERD). I could look at the tables on that ERD, and if I found a Customer table, I could suggest an index on CustomerName because it's likely that you look up data based on a customer's name. However, if you add the information that it's your demographics analysis database and all sales are analyzed by city-state-ZIP code combinations, my suggested index on CustomerName would be useless and a burden in terms of space and performance (if you were to analyze data loads and insert rates).The moral of this story is that you can't design a highly performant database application in a vacuum: You must know your data, know your users, and know your system.
Database Design
When you set out to design your database, you'll quickly discover that you need
to consider a lot of pieces. Table design, row size and page density, correct
and consistent data type usage, column nullability, how to deal with LOB data,
and data integrity are all key design aspects that you need to deal with in
your process.
Let's take a look some of the most important details you'll need to assess in each of these areas. As with all things technical, my recommendations in each of these areas represent the tip of an iceberg. You'll fill in the gaps in your process by making mistakes, monitoring for performance, and deriving new solutions.
Prev. page
[1]
2
3
4
next page 
