Editor's Note: This article is the first in a series about performance-tuning the data model.
Performance-tuning a SQL Server database starts with a sound database configuration and data model. Although you can achieve performance gains by retrofitting a database with various types of indexes and more powerful hardware, you can never completely compensate for a bad data model. Poor database configuration or a bad data model can cause slow system response, blocked or deadlocked transactions, weird or inaccurate results on business reports, lack of data synchronization, inconsistent data, and the inability to write a query that returns the data you're looking for. It's true that a bad data model might not be the only cause of such problems. For example, a database server pushed to its limits might cause slow system response. Or a bad mix of transaction updates from conflicting applications can cause deadlocking or blocking. You should always use due diligence and check for the cause of such conditions. Then, if you can't find an overwhelmed CPU or two conflicting transactions trying to acquire an X-lock on the same data resource, take a serious look at the database configuration and the data model; they might be the source of your trouble.
To create a database that performs the way you expect, you must first look at the foundation the database sits on. Start by optimizing the OS. Then, configure the database to support a good data model and preserve data integrity. When you proactively implement these basic elements, you can prevent many performance problems before they start and set the stage for better overall database performance.
Preparing the Environment
SQL Server performance tuning begins with the Windows OS. Because SQL Server can run only in a Microsoft environment, it's imperative that you stay on good terms with your Windows systems administrator. Two essential settings to pay attention to on the database server are the file system and the page file. For SQL Server, you should use the NTFS file system. NTFS is more robust and secure than FAT, even if the FAT file system (arguably) performs slightly faster on writes. When you configure your page file, remember that the rule of thumb for virtual memory is to assign a static size of 1.5 times the size of physical memory. Also, if any server components such as a network card or hard disk go to sleep after a certain period of inactivity, reset them (or have your systems administrator reset them) so that they never sleep. You don't want to risk having to cold-boot to wake up a sleeping computer component. In a multi-protocol environment, make sure that TCP/IP is at the top of the protocol stack. And if you have a slow network, ensure that the default login timeout is set longer than the time it takes to log in to applications that use the database.
In addition to preparing your OS for optimal support of SQL Server, be sure you make the environment fault tolerant. For reliability and speed, I recommend using a RAID environment for SQL Server. Although RAID setups can be expensive, you can make the best use of your RAID budget by carefully selecting the best type of RAID for your environment and by deciding what parts of your database are most important to protect. For an explanation of different types of RAID systems and their benefits, see the sidebar, "Running SQL Server on RAID," page 34.
Build a Solid Foundation
SQL Server performance tuning relies on a sound database configuration. Design your database for optimum performance by separating into individual filegroups the major types of data that you'll be storing in the database. Separate system tables from user tables. Separate data from indexes. Separate tabular data from image, text, and ntext data. Using this scheme for separating data into multiple filegroups creates a highly scalable database. Scalability is the ability to increase the number of transactions processed without diminishing performance. For a small system, you can put all the filegroups on one drive (with the exception of the transaction log file, which you should always put on a disk separate from all the other data). As the system grows, storing more data and supporting more users, you can move the various filegroups to separate drives, thus dividing the workload across multiple disks. Separating your database into multiple filegroups can also help you manage database backups. You can use filegroups to enable backups of very large databases (VLDBs) within the database backup time window. (For more information about using filegroups for backups, see Kimberly Tripp's article "Before Disaster Strikes," September 2002, InstantDoc ID 25915.) And you can use filegroups to facilitate horizontal partitioning, which I wrote about in "A New Lease on Life," September 2003, InstantDoc ID 39643. When you're designing a database for performance, filegroups are a useful tool that can help you avoid problems before they start.
The code in Listing 1 shows the standard way I create a database. Each filegroup has three names: a filegroup name, a logical file name, and a physical file name. You can see these names if you open any database properties window, then choose the Data Files tab. On that tab, the entries in the Filegroup column correspond to PRIMARY and the filegroup names that you see in the first part of Listing 1. The entries in the File Name column are the logical file names, and the entries in the Location column are the physical file names, which include a full path to the location of the physical files on your hard disk. Put SQL Server system tables in the PRIMARY filegroup. Put user tables and indexes in their respective filegroups, separate from the SQL Server system tables. Put image and text data in its own filegroup, as the code at callout A in Listing 2 shows.
The configuration options that follow the CREATE DATABASE command are set to conform to the ANSI SQL-92 standard. You might have to modify one or more of these configuration options based on your own shop standards. Check your existing configuration options to ensure that these will work in your environment.
Prev. page
[1]
2
next page 
