Most of us never think about database performance before creating database. But after increasing database size when database performance down day by day, then we start to think how to improve database performance. That’s why better to think about it when we create the database. I have some idea about to improve database performance. My ultimate target is to tell you that, how physical & logical storage system of SQL server will help you to improve database performance. Before going detail, better to explain about files and filegroups.
FILE & FILEGROUPS
To map a database, SQL Server uses a set of operating system files. All data and objects in the database, such as tables, stored procedures, triggers, and views, are stored within the following types of operating system files:
- Primary. This file contains the startup information for the database and is used to store data. Every database has one primary data file.
- Secondary. These files hold all of the data that does not fit into the primary data file. If the primary file can hold all of the data in the database, databases do not need to have secondary data files. Some databases might be large enough to need multiple secondary data files or to use secondary files on separate disk drives to spread data across multiple disks or to improve database performance.
- Transaction Log. These files hold the log information used to recover the database. There must be at least one log file for each database.
A simple database can be created with one primary file that contains all data and objects and a log file that contains the transaction log information. Alternatively, a more complex database can be created with one primary file and five secondary files. The data and objects within the database spread across all six files, and four additional log files contain the transaction log information. Filegroups group files together for administrative and data allocation/placement purposes. For example, three files (Data1.ndf, Data2.ndf, and Data3.ndf) can be created on three disk drives and assigned to the filegroup fgroup1. A table can then be created specifically on the filegroup fgroup1. Queries for data from the table will be spread across the three disks, thereby improving performance. The same performance improvement can be accomplished with a single file created on a redundant array of independent disks (RAID) stripe set. Files and filegroups, however, help to easily add new files to new disks. Additionally, if your database exceeds the maximum size for a single Windows NT file, you can use secondary data files to grow your database further.
RULES FOR DESIGNING FILE & FILEGROUPS
- When designing files and filegroups, you should adhere to the following rules:
A file or filegroup cannot be used by more than one database. For example, the files sales.mdf and sales.ndf, which contain data and objects from the sales database, cannot be used by any other database.
- A file can be a member of only one filegroup.
- Data and transaction log information cannot be part of the same file or filegroup.
- Transaction log files are never part of a filegroup.
When creating database if you follow the following steps, then I believe that the database will run smoothly. I am showing all the things with SQL server 2005.
Open Microsoft SQL server management studio. Right click on databases and click on new database. A window will open like figure 1. Write the database name as TESTDB. Automatically logical name of data & log file will be assigned by TESTDB and TESTDB_log. Data file TESTDB will be created on PRIMARY filegroup. Because initially PRIMARY filegroup will be the default filegroup. If you click on Filegroups, you will see there is only one filegroup exist. At this stage, TESTDB data file will contain all system data & objects. Close the new database window.
STEP 2: Now right click on TESTDB database and click on properties. A window will appear like Figure 2. Click on Filegroups option. Click on Add button. A blank new row will be added into filegroup list. Write the name as SECONDARY. Now click on OK button.
Step 3: Once again right click on TESTDB database and click on properties. A window will appear like Figure 3. click on files. Click to Add button. A blank new row will be added into database files list. Write the logical names as TESTDB_USER and select the filegroup as SECONDARY. click on OK button.
Step 4: Once again right click on TESTDB database and click on properties. A window will appear like Figure 2. select the SECONDARY filegroup as default. Click on OK button. From now whatever data you store or objects you create, it will be automatically stored or created into specified default filegroup. Here SECONDARY is the default filegroup. Now the question is how would you know that the objects you are going to create it will be automatically created into SECONDARY filegroup. Create a table and see the properties of that table. You will see the filegroup of that table is SECONDARY.
In this way if you separate the system data and objects from user data and objects, your database will run smoothly.
Update – 21-MAR-2009
FILE AND FILEGROUPS and RULES FOR DESIGNING FILE AND FILEGROUPS both section is taken from MS SQL SERVER DATABASE DESIGN AND IMPLEMENTATION book.