How to improve MS SQL database performance

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.

STEP 1:

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.

p1

Figure 1

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.

p2

Figure 2

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.

p3

Figure 3

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.

18 thoughts on “How to improve MS SQL database performance

  1. Database Management » Blog Archive » How to improve MS SQL database performance

  2. I have been seeing deteriorating performance on my SQL2000 DB. Can I perform these steps on an existing database? Any pointers appreciated.

    • In an existing database, data already stored into specified filegroup. So you cannot transfered that data from one filegroup to another. Thats way It will not be wise decision to do this on an existing database. But for better performance you can do re indexing once in a week. Run this reindexing by using scheduling.

  3. Hey, I found this blog post while searching for help with JavaScript. I have recently switched browsers from Chrome to IE. Just recently I seem to have a issue with loading JavaScript. Everytime I browse page that needs Javascript, the site does not load and I get a “runtime error javascript.JSException: Unknown name”. I cannot seem to find out how to fix the problem. Any aid is greatly appreciated! Thanks

  4. Hi Sadeque,

    Nice post. Because you are already in the Database properties window, the one thing DB Administrators often forget is to change the Autogrowth settings. Try to create database files as big as you can. If you have the space and you are expecting you db file to be 2GB than create a file of 2GB. Think that every time the autogrowth option is triggered this will consume a lot of processor and disk i/o.

  5. I Have DataBase With 2GB Size
    Ane Approx 2 Crore Record

    How I Incress Performance Of Database ,,
    Please Help Me,,
    And Answer Send Me
    On This Mail,,,
    Please…

  6. IN MY CASE I UNABLE TO CREATE THE DATABASE THROUGH ABOVE MENTIONED STEPS AT THE SAME TIME I AM GETTING FOLLOWING ERROR.

    “CANNOT CHANGE THE PROPERTIES OF EMPTY FILEGROUP’SECONDARY’,THE FILEGROUP MUST CONTAIN ATLEAST ONE FILE”

    PLEASE SUGGEST………..

    • May be you are trying to change the properties of SECONDARY filegroup before attach any logical file with SECONDARY filegroup. to change the properties you have to follow the the step 3 first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s