SQL Server 2000 Design and Implementation, Part II -
Creating and Managing a DataBase (B)...

This series of articles shall focus on the data store you use for your .NET applications. In particular it shall consider relational database management systems in general for the first article and SQL Server 2000 in particular for the remaining.

By: Chris Sully

Date: April 11, 2004

Introduction

This series of articles is focussing primarily on the physical data store Microsoft developers are most likely to use for their applications, SQLServer. The main reference for this series of articles is MCSE: SQLServer 2000 Design by Israel and Jones.

Here's a quick overview of what we’ve covered so far as well as what we shall be covering:

Article I: we started with developing the conceptual and logical data models.

Article II: in this article we shall start looking at the physical data model with creating and maintaining databases.

Article III: creating and maintaining tables - looking at what we can store in tables, and how we do so.

Article IV: data integrity - default values, check constraints and rules, primary keys, unique constraints, as well as foreign keys and relationships.

Article V: indexes and the related area of statistics.

Article VI: creating and maintaining the various database objects that exist- views, stored procedures, transactions, user defined functions and triggers. This concludes the look at the physical design per se. The remainder of the articles look at issues relating to data and data access.

Article VII: accessing data

Article VIII: modifying data

Article IX: importing and exporting data

Article X: locking

Article XI: security

Article XII: analysing and optimising data access

Thus far we've reached article II: creating and maintaining databases. Creating a database in SQLServer is easy; creating a database optimised for the specific tasks it is required to perform is a little more difficult. It is the issues surrounding this optimisation we shall be considering.

The complexity of database creation lies in the database file management, which is SQLServer specific and which needs to be known and understood if you are to take advantage of this knowledge to improve the performance of your applications in a production environment.

As there is a fair bit of ground to cover we've split the subject matter into two parts: A and B. In part A we looked at Creating a Database, Query Processing and the Transaction Log, Space Management and Table Creation and Management Options. In part B we look at Database Options and File Groups.

Database options

There are a number of options you can set on the database that you may set via the SET keyword. These are:

Database option	Default setting
ANSI null default	OFF
ANSI nulls	OFF
ANSI warnings	OFF
auto create statistics	ON
auto update statistics	ON
autoclose	FALSE(1)
autoshrink	FALSE
concat null yields null	OFF
cursor close on commit	OFF
dbo use only	FALSE
default to local cursor	FALSE
merge publish	FALSE
offline	FALSE
published	FALSE
quoted identifier	OFF
read only	FALSE
recursive triggers	FALSE
select into/ bulkcopy	FALSE
single user	FALSE
subscribed	TRUE
torn page detection	TRUE
trunc. log on chkpt	TRUE

See Books Online for further information.

To query the database options you can run sp_dboption system stored procedure or use the DATABASEPROPERTYEX function. The former will show the set options. The function allows specification of a specific option:

SELECT DATABASEPROPERTYEX('Northwind','IsAutoShrink')

To alter these use SET, e.g.

ALTER DATABASE dbname SET SINGLE_USER

Obviously this would be problematic if there was more than one user in the database at the time busy running various operations so you can also specify whether to rollback all running transactions. There are three options (WITH ROLLBACK IMMEDIATE, WITHROLLBACK AFTER n SECONDS, WITH NO_WAIT).

The database compatibility level defines the level of SQL grammar used on the database. For example, if you group by a column when the database is in compatibility level 60 or 65 the result will be sorted on that column but not if in level 70 or 80. Remember also that if you upgrade your server from SQLServer 7 to 2000, all your databases but the master will be compatibility level 70. Compatibility level is designed to minimise the impact of upgrades on an existing application.

The stored procedure sp_dbcmptlevel allows querying and changing of the compatibility level of a database.

Note that the master database always has a compatibility level of 80/ max. for the version.

Filegroups

Filegroups are groups of data files allowing explicit placement of tables, indexes, text, image and ntext columns. There are two types of filegroups:

primary: the primary filegroup created by default contains all system tables allocation and some or all user tables.

user defined: may contain four files and three filegroups.

The FILEGROUP clause of the CREATE DATABASE and ALTER DATABASE statements is used to create and alter filegroups.

Here's an example from Books Online:

This example creates a database named sales with three filegroups:

The primary filegroup with the files Spri1_dat and Spri2_dat. The FILEGROWTH increments for these files is specified as 15 percent.
A filegroup named SalesGroup1 with the files SGrp1Fi1 and SGrp1Fi2.
A filegroup named SalesGroup2 with the files SGrp2Fi1 and SGrp2Fi2.

CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
  FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri1dat.mdf',
  SIZE = 10,
  MAXSIZE = 50,
  FILEGROWTH = 15% ),
( NAME = SPri2_dat,
  FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri2dt.ndf',
  SIZE = 10,
  MAXSIZE = 50,
  FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
  FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi1dt.ndf',
  SIZE = 10,
  MAXSIZE = 50,
  FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
  FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi2dt.ndf',
  SIZE = 10,
  MAXSIZE = 50,
  FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
  FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi1dt.ndf',
  SIZE = 10,
  MAXSIZE = 50,
  FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
  FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi2dt.ndf',
  SIZE = 10,
  MAXSIZE = 50,
  FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
  FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
  SIZE = 5MB,
  MAXSIZE = 25MB,
  FILEGROWTH = 5MB )
GO

By default there is one filegroup (primary) marked as default and all tables and indexes are placed on this filegroup unless otherwise directed. This can be changed with ALTER DATABASE. You can MODIFY, ADD or REMOVE a filegroup with the ALTER DATABASE command. Using MODIFY you can also change a filegroups name and mark it as READWRITE or READONLY.

See Books Online for further details.

Why use filegroups?

The creation of filegroups is generally driven by performance considerations. In some situations performance may be improved by placing different filegroups on different physical devices. For example, separating the indexes and data of a table on separate drives.

Filegroups are not easy to use. They require more administrative overhead so you must justify their use. You may do so via reference to 4 features:

The ability to place a table or index on a particular filegroup
The ability to place an image, text or ntext column on a specific filegroup
The ability to assign many files to one filegroup
The ability to backup a filegroup on its own

The four following situations are probably the most likely candidate scenarios for the use of filegroups

Physically separating tables and indexes
Isolating large tables
Isolating binary data
Ability to back up a portion of a database

File Placement and Performance

So we now know that the use of filegroups and files can improve performance and some of the occasions when we can use them, but how should they be used to improve performance?

Often the difference between a good and a poor database physical design lies in the file placement and the disk system on which they are placed. Both can have a huge impact on performance and together they are key. The type of access required will influence these choices.

For example, with a typical OLTP application most data is accessed through one or many indexes, many inserts are performed and the transaction log is heavily used. This would indicate that one should place heavily read tables (e.g. products and categories) on their own fast read-access disk sub-system, heavily inserted tables (e.g. sales and customers) on their own fast write-access sub-system, and the log file on a fast write-access disk system with a high availability feature.

As regards data placement here are some rules:

Always place data on striped disks. It is the max number of I/Os per sec which is key rather than disk throughput. RAID 0 is the best disk striping strategy for data files but as this is risky in production environments consider RAID0+1 and RAID 5. Again, if unfamiliar with the terminology here see Books Online.
Use hardware RAID rather than filegroups. You can emulate RAID with filegroups and multiple disks but it is better to spend a little more money on a good RAID controller as with filegroups you may create more problems than you solve. Ask your disk administrator to optimise your RAID controller by setting the read/write cache. Perfmon will help you to discover the read/write ratio on your disk sub-system and help set the controller to the optimal value.
Identify your access patterns. Know how your users access data as dependent on the access patterns, the indexes and the read/write ratio you may decide to use multiple file groups over multiple disks or just use a single filegroup.

As regards transaction logs:

always store the transaction log on its own physical disk
protect your log file disk
use the fastest available disks for you log files

That concludes our look at creating and maintaining databases and related issues. In the next article we'll move onto the next level of database object: tables.

References

MCSE: SQLServer 2000 Design
Israel and Jones
Sybex

SQLServer Books Online

MSDN