Performance Management
In my last few columns I discussed some key data management issues when configuring and managing a production database, whether it's for transaction processing, decision support or a hybrid of the two. Last time I discussed some availability features of Microsoft Corp.'s SQL Server 7.0. In this column, I'll examine some performance management features of SQL Server 7.
Performance tuning is simpler in SQL Server 7 than it was in prior versions. Microsoft invested heavily in re-engineering the product to make it flexible and easy to manage. This can be both wonderful and frustrating, depending on your level of expertise and your degree of satisfaction with the way SQL Server manages data.
Automatic functions. Memory management and lock management are two functions not under the direct control of the database administrator (DBA). SQL Server 7 automatically allocates buffers from the available system memory and releases them when they are no longer required. Similarly, SQL Server automatically allocates and releases locks -- based on the level of activity -- and the anticipated usage the query will make of the data.
Database partitions. SQL Server 7 provides several mechanisms for controlling how the database will be allocated on disk. One option is the ability to partition the database using filegroups. A filegroup is a set of one or more files that can be placed on a specific set of disks. You then can create one or more tables within the filegroup. This allows you to place a table on specific disks. There is also the option to create a database partition, which gives you the ability to partition the data within a table, even if the table spans multiple disks.
Indexes. Indexing the database is probably the best and cheapest method of improving performance. An index is simply a data structure that represents a column sorted in a particular order. If you index a table on a column, the database optimizer can scan the appropriate index to identify any target rows quicker than it would be able to scan the entire table. SQL Server 7 supports clustered and non-clustered indexes. A clustered index actually stores the data rows in sorted order, while a non-clustered index doesn't force a particular order on the table itself. A clustered index is most appropriate when you know that many queries will be searching on the sorted column, such as customer name. Because a clustered index forces the table to be sorted on the index column, there can be only one clustered index per table. A table can have up to 256 non-clustered indexes.
OLAP cubes. If you use the database for decision support, then you'll probably want to take advantage of SQL Server 7's built-in OLAP Services. You can build OLAP cubes to capture summary data in a hierarchical structure that is easier for users to navigate than a third-normal form database. Also OLAP cubes deliver a faster performance if the data request can be satisfied from an existing cube, rather than scanning a relational table. SQL Server 7 OLAP Services allow you to define three types of OLAP cubes. Relational OLAP cubes are stored in a star schema in the database. Multidimensional OLAP cubes are stored entirely in a specialized cube structure. Hybrid OLAP cubes have some summary data stored in an OLAP cube structure, while more detailed data is stored in relational tables. This flexibility can be useful when you need to tune the system for better response time.
I/O processing. Most I/O processing problems arise from either a disk hot spot or insufficient memory. A disk hot spot is typically an application design issue, such as a "take-a-number" table. This problem may require an application redesign. On the other hand, if the disk hot spot is caused by a table being heavily hit in a relatively random fashion, then you can investigate putting the table onto a RAID stripe set, which will evenly disperse the table across the RAID array.
If you're suffering from insufficient memory, the system will write pages to the swap file. Since memory buffers are allocated from virtual, not physical, memory, database buffers can be written out from memory, which can cause severe performance problems. Monitoring the system for paging and swapping will alert you to add more memory to the machine.
Query optimization. Finally, SQL Server 7 has a powerful query analysis tool that can analyze the impact of a specific query on the system. This tool can identify queries that stress the system and help you decide how to modify the query to make it more efficient. --Robert Craig is vice president of marketing at WebXi Inc. (Burlington, Mass.), and a former director at the Hurwitz Group Inc. Contact him at rcraig@webxi.com.