Professional Microsoft® SQL Server® 2008 Administration

11.2. 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 nth form." Instead, they require that you have a solid understanding of the use of the system, including the usage pattern, the SQL statements, and the data. The optimal schema for an online transaction processing (OLTP) system may be less optimal for a decision support system (DSS), or for a data warehousing (DW) system.

11.2.1. Users

You first need to know who is going to use the system: how many users there will be, their concurrency, peak usage level, 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. For a sales analytics system, the user groups may be primarily analysts reading the data with report tools such as PerformancePoint Server, Proclarity, or Excel, and perhaps running reporting for the sales team. For example, the e-commerce-based system is an OLTP database workload that is optimized for fewer and faster reads, updates and writes requests while the sales analytics system is a DSS database workload that ...