A Beginner's Guide to Improver SQL Server Database Performance

Most of the SQL server users do not think about SQL database performance before creating a database but after creating SQL database they realize that their database performance is decreasing day by day. After this, they think about how to gain the database performance as similar to the early database. The main purpose of this article is to guide you this point at the initial stage (when you create a new database) to overcome the database performance problem. Before going in details, it will be better to summarize files & filegroups.

Files:

To create and manage a database, MS SQL uses some files. All the related data and objects of the database such as tables, indexes, views, triggers, stored procedure are stored in operating system files. Following are the list of operating system files that are used by SQL server to store database:

• Primary File: It is the starting point of the database and points other files of the database. Primary file is used to store data. Every SQL server database has one primary file and the recommended file extension for the primary file is .mdf. I have used term recommended because you can choose any other file extension for your primary file as you want. But it may create some problem to understand what file is by others.
• Secondary File: A file that is not a primary file called secondary file. It stores all the data & the data that does not fit into the primary file. If your primary file can store all the data of database then there is no need to have secondary file. It is shortly known as optional data file. Some databases have many secondary file while others may have not any secondary file. The recommended file extension for secondary data file is .ndf.
• Transaction Log File: It stores all the log information that might be required to recover a database. Every SQL server has at least one transaction log file and it can be more than one. The recommended file extension for transaction log file is .ldf.

Filegroups:

As name suggests it is used to group database objects and file together. There are two types of filegroups available in the SQL server: primary filegroup and user-defined filegroup.

• Primary Filegroup: It contains primary data file (.mdf) and other files that are not associated with any other filegroup.
• User-Defined Filegroup: A filegroup specified by using filegroup keyword in create or alter database statement is known as user-defined filegroup.

Create a new database: A simple new database can be created using primary data file that holds data & objects of the database and a transaction log file that stores log information. Alternatively, database administrator can create a complex database using one primary data file and two secondary data files. All the data and objects of the database are spread across three files and two transaction log files. As we have discussed above filegroup groups all the files for the administrative purpose.

Example

Two secondary data files can be created on two disks and associated with a filegroup. Now a table can be created specifically on the filegroup. Queries for data from the table will be spread across two disks, thereby improving performance. The same performance improvement can be accomplished with a single file created on a RAID stripe set.

Note: Files and filegroups help database administrator to add new files to the new disk drives. If you notice that your database has reached the maximum size for a single Windows NT file then, you can use a new secondary data files to gain size.

Rules for designing files and filegroups: When you design file or filegroup, you should adhere given below rules.

• Make sure that any file or filegroup cannot be used by more than one SQL server database. For example, student.mdf & student.ndf hold the data and objects of the student database, so it cannot be used by the teacher database.
• A file (primary or secondary) can be member of only one filegroup.
• Transaction log file (.ldf) is never a part of any filegroups.

Recommendations for designing files and filegroups:

• Most of the database work better with single data and log file.
• Try to place transaction log file on different physical disk location that does not has files and filegroup.
• If you have multiple files then try to create a new filegroup for the additional files and make it as default filegroup. In this way, primary filegroup will contain tables and objects of system only.
• Try to use filegroup in such manner that it works as a placement of objects on the specific physical disk.

If you create a new SQL server database by following manner then I ensure that your database will run smoothly.

STEP 1:

Open Microsoft SQL server management studio (SSMS) and Right click on databases and then click on new database. To create a new database by default value then click on the OK button otherwise write the database name. Database name can be anything, we have take Firstdatabase as for the example purpose. A window will be open like bellow figure.

SQL server management studio -> Right on the databases -> New database -> OK (default value)

Automatically logical name of data & log file of the database 'Firstdatabase' will be assigned by Firstdatabase_data and Firstdatabase_log. Data file of the Firstdatabase will be created on Primary filegroup because it is the default filegroup for the database. To ensure that click on the Filegruop, you will see there is only one filegroup. Here the entire Firstdatabase_data file will store all system data & objects. Now close this window.

STEP 2:

Now right click on newly created database (Firstdatabase) and point to the properties. Now click on the Filegroups option and then Add button. A newly blank row will be added into list. Give the name of this newly created filegroup as Secondary and click on OK button.

Database -> Properties -> Filegroup option -> Add -> Rename -> Ok

Step 3:

Again right click on Firstdatabase database and goes to the properties section. Now click on files and then Add button. A blank new row will be added into database files list. Give the logical names as Firstdatabase_User (for example) and choose filegroup as Secondary. Finally click on OK button to finish the task.

Database -> Properties -> File option -> Add -> Rename -> Filegroup selection ->Ok

Step 4:

Again right click on firstdatabase database and points to the properties section. Now select secondary Filegroup as default Filegroup and click on the Ok button.

Database -> Properties -> Make Secondary Filegroup as default Filegroup

From now whatever data you store or object you create, it will be automatically stored or created into specified default filegroup. Here SECONDARY is the default filegroup. Now the question is how you would 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.