The most abused statement in TSQL

CREATE DATABASE

No, not the fact that anyone can create a database but the "advanced" options.
The amount of times I have seen this statement butchered is enough to warrant a post.

Do you have a database this is over 250Mb in size that has 1 data file?

My "best practice" for this is that only system tables (and associated objects.. SP, views, triggers etc..) should reside on the PRIMARY file group.
You should, as a minimum have 2 filegroups, and in most cases three. Forget the log filegroup for the moment as I am only concerned with data.

As a in house standard, we have 3.. PRIMARY, DATA and INDEXES
After creating the database use the ALTER DATABASE command to make the DATA filegroup the DEFAULT (that is, all tables not explicitly stating their Filegroup will be placed here.)
Clustered indexes should also go in the DATA filegroup and all non-clustered indexes on the INDEXES File Group

These options should be included from the development tier and up, as having to go through a database script and altering them as you move through environments sucks pretty bad and is error prone.

The PRIMARY filegroup could then be restricted in size (50Mb is a hell of a lot of meta data and would be a very big schema.).
FielGroups can be marked as readonly, backedup separately and most importantly, can take advantage of multiple IO controllers in you storage system.
Another advantage is that you can target device size for your backups... Say you back up to CD (I'm just giving an example), you could then create multiple files within the filegroup that match the CD size (~700MB).
Not really a common scenario, but without multiple filegroups and files, you don't have a chance in hell..

A gotcha from this approach is that if you have any base tables that are in your model DB.
You cannot add Filegroups to this database and thus, any tables will placed in the Primary File Group.

I recommend that you create a template in QA and use this every time you need to create a database...

Print | posted on Tuesday, February 24, 2004 10:08 AM

Feedback

# SQL Server Best Practices for Database Files

left by Dim blog as New ThoughtStream( at 2/24/2004 9:06 AM Gravatar

# re: The most abused statement in TSQL

left by Richard Tallent at 2/25/2004 12:33 AM Gravatar
Frankly, the pain doesn't seem worth the gain...
- What benefit is a ReadOnly index?
- Who has multiple RAID controllers?
- For most SQL Server backups, zipping does wonders to backup size.

I can see some small benefit in being able to back up the data and forego backing up the associated non-clustered indexes, but other than that I can't think of any reason why such a setup should be considered a standard or a "best practice."

# re: The most abused statement in TSQL

left by DavidM at 2/25/2004 8:23 AM Gravatar
Thanks for the feedback Richard.

What pain are you taking about? Use the wizard if you hate typing.

- We have a table that is this 1GB and is updated once a year. Readonly on its file group gives us increased security/piece of mind and a slight speed improvement on the reads.
- We have 4 RAID controllers (PRIMARY, DATA, INDEXES and LOGS)
- Zipping does good work, but I hate spanning.

To each his own.. Spearation of meta data is a good thing...

# re: The most abused statement in TSQL

left by eyechart at 2/26/2004 9:34 AM Gravatar
David,

Do you have any other recomendations? Specifically, what about a large database? How many datafiles should be created, etc.



-ec

# re: The most abused statement in TSQL

left by DavidM at 2/26/2004 10:05 AM Gravatar
A VLDB requires its own storage analysis. So no, not really.

SQL Server can take a TINYINT number of FileGroups and a SMALLINT number of Files per database.

# re: The most abused statement in TSQL

left by eyechart at 2/26/2004 4:43 PM Gravatar
Are you aware of any whitepapers or other analysis of VLDBs in SQL Server. This would be interesting reading.


-ec

# re: The most abused statement in TSQL

left by Justin Pits at 3/25/2004 2:10 AM Gravatar
This was, once upon a time, a standard practice for us. Now, with the arrival of "virtualized RAID" SANs (EVA5000 by HP) that automatically spread ALL IOs over every arm in the cabinet (@ 140 spindles for us right now) I wonder if the practice has a purpose beyond backup/restore. My leanings now are to refrain from datafile segmentation unless a RLDB (thats Rather Large DB - not necessarily as big as a VLDB :) ) needs it for backup.
Comments have been closed on this topic.