Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 4, 2001

Optimizing SQL Server Performance by Using File and Filegroups

By Alexander Chigrik


General concepts
Optimization tips
Literature

General concepts

There are no devices and segments in SQL Server 7.0 and SQL Server 2000,
as in SQL Server 6.5. Now databases reside on operating-system files.
There are three types of such files:

  • primary
  • secondary
  • log
  • Each database consists of at least two files: one is a primary data file (by default, with the .mdf extension), the other is log file (by default, with the .ldf extension). There are also secondary data files (by default, with the .ndf extension). A database can have only one primary data file, zero or more secondary data files, and one or more log files. Each database file can be used by only one database. So there is no such situation (as in SQL Server 6.5 was) in which you can create some databases with their logs on the same device (on the same file with the .dat extension). The database files are combined into filegroups. Each data file can be a member of only one filegroup, but the log files cannot be members of any filegroups. In other words, log files are managed separately from one another. There are three types of filegroups:
  • primary
  • user-defined
  • default
  • Each database can have only one primary filegroup, only one default filegroup, and zero or more user-defined filegroups. If you don't specify user-defined filegroups, your database will contain only one primary filegroup, which will also be the default filegroup. The primary filegroup contains the primary data file with all system objects in it (system tables, system stored procedures, extended stored procedures and so on). You cannot remove system objects from the primary filegroup, but you can create user objects in the user-defined filegroups for allocation, performance, and administration purposes. To create user-defined filegroup, you should use a CREATE DATABASE or ALTER DATABASE statement with the FILEGROUP keyword. The default filegroup is the filegroup, where all the new user objects will be created. You can change the default filegroup by using ALTER DATABASE statement with the DEFAULT keyword. SQL Server database files can be configured to grow and shrink automatically, reducing the need for database management and eliminating many problems that occur when logs or databases run out of space. The autogrow feature is set by default; the autoshrink feature is set by default only for the Desktop Edition of SQL Server 7.0. When you create a database, you must set an initial size for the data and log files. If you want to set database files to grow automatically, you should also specify the autogrow increment in megabytes, kilobytes, gigabytes, terabytes, or percent. The default is MB. You can also specify a maximum file size to prevent disk drives from running out of space.

    Optimization tips

  • Set a reasonable size for your database. First of all, before database creation, you should estimate how large your database will be. To estimate the reasonable database size, you should first estimate the size of each table individually, and then add the values obtained. See this link for more information: Estimating the Size of a Table
  • Set a reasonable size for your transaction log. The general rule of thumb for setting the transaction log size is to set it to 20-25 percent of the database size. The smaller the size of your database, the greater the size of the transaction log should be, and vice versa. For example, if the estimated database size is equal to 10Mb, you should set the size of the transaction log to 4-5Mb, but if the estimated database size is over 500Mb, then 50Mb can be enough for the size of the transaction log.
  • Leave the autogrow feature on for the data files and for the log files. Leave this feature to let SQL Server increase allocated resources when necessary without DBA intervention. The Autogrow feature is necessary when there is no DBA in your firm, or your DBA has limited experience.
  • Set a reasonable size for the Autogrow increment. Automatically growing does result in some performance degradation, therefore you should set a reasonable size for the autogrow increment to avoid automatic growing too often. Try to set the initial size of the database and the size of the autogrow increment so that automatic growing will only arise at most once per week.
  • Don't set the autoshrink feature. Autoshrinking results in some performance degradation, therefore you should shrink the database manually or create the schedule task to shrink database periodically during off-peak times, rather than setting the autoshrink feature.
  • Set the maximum size of the data and log files. Specify the maximum size for which the files can grow in order to prevent disk drives from running out of space.
  • Create a user-defined filegroup and make it the default filegroup. In general, it's a good decision to store and manage system and user objects separately from one another. This is so that the user objects will not compete with system objects for space in the primary filegroup. Usually a user-defined filegroup is not created for the small database if, for example, your database is less than 100Mb.
  • Create a user-defined filegroup and create some tables in it to run maintenance tasks (backups, DBCC, update statistics, and so on) against these tables. LOAD TABLE and DUMP TABLE are no longer supported in SQL Server 7.0 (and higher), but you can place a table in its own filegroup and can backup and restore only this table. This will allow you to group user objects with similar maintenance requirements into the same filegroup.
  • If you have several physical disks, try to create as many files per filegroup as there are physical disks, and put one file per disk. This will improve performance, because when table is accessed sequentially, a separate thread is created for each file to read the table's data in parallel.
  • Don't create many data and log files on the same physical disk. Leaving the autogrow feature on for the data and for the log files can cause fragmentation of those files if there are many files on the same physical disk. In most cases, it's enough to have 1-2 database files on the same physical disk.
  • For a heavily accessed table, place this table in one filegroup and place the table's indexes in a different filegroup on different physical disks. This will improve performance, because separate threads will be created for the table's and index's data in parallel.
  • For a heavily accessed table with text/image columns, place this table in one filegroup and place text/image columns in a different filegroup on different physical disks. You can use a CREATE TABLE statement with TEXTIMAGE_ON keyword to place text/image columns in a different filegroup. See SQL BOL for details.
  • Place the log files on different physical disk(s) than data files. Because logging is more write-intensive, it's important that the disks containing SQL Server log files have sufficient disk I/O performance.
  • If one of the join queries is used most often, place the tables used in this query in different filegroups on different physical disks.
  • If you have read-only tables, place these tables in different filegroups on different physical disks and use ALTER DATABASE statements to make just these filegroups READ ONLY. This not only increases read performance, but it prevents any data changes and allows you to control permissions to this data.
  • Use Windows NT Performance Monitor to determine the appropriate number of the data and log files by checking the Disk Queue Length counter. The more database files and filegroups, the more difficult administering this database will be. Consider reducing the number of files if Disk Queue length is above 3, and continue monitoring.
  • Literature

    1. SQL Server Books Online.
    
    2. Microsoft SQL Server 7.0 Performance Tuning Guide
       http://msdn.microsoft.com/library/techart/msdn_sql7perftune.htm
    
    3. Microsoft SQL Server 7.0 Storage Engine
       http://msdn.microsoft.com/library/backgrnd/html/msdn_sqlstore.htm
    
    4. Microsoft SQL Server 7.0 Storage Engine Capacity Planning Tips
       http://msdn.microsoft.com/library/techart/storageeng.htm
    


    » See All Articles by Columnist Alexander Chigrik




    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















    Thanks for your registration, follow us on our social networks to keep up-to-date