What is Files and Filegroups in SQL Server & it's
implementation.
- 1 Answers
- 8349 Views
- Zenith, I also Faced
- E-Mail Answers
Answer Posted / compmastbipin@yahoo.com
Usage of different filegroups is a very good feature that
is available in SQL Server as well as many other RDBMS
(though in other RDBMS like Oracle and DB2 UDB, the concept
is a bit different for tablespaces but similar feature
exists). Filegroups allow files to be grouped 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, respectively, 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 RAID/SAN stripe set. Files and
filegroups, however, allow you to easily add new files on
new disks. Additionally, if your database exceeds the
maximum size for a single Windows file, you can use
secondary data files to allow your database to continue to
grow. The advantage of using such an approach is that for
large database systems you can keep the filegroups on
separate disks, thus increasing response time for your
queries.
One other advantage relates to quick backup and recovery.
You can take the backup of separate filegroups and hence
restore only a particular filegroup and continue working.
This can be particularly useful in cases of production
databases where these activities are time critical and, of
course, minimizing downtime is always desirable.
You should never store objects in the primary filegroup
which is also used for storing the system objects in the
user database. Filegroups are also a good choice if you
have decided to implement partitioning for Very Large
Databases (VLDBs). Where RAID disk technology is being
used, multiple filegroups can be set up to span different
stripe sets. The objective here is to spread the I/O as
evenly as possible across the physical disk devices.
When you are using filegroups, how often have you seen
scenarios where-in you have a multi-processor powerful box
but all the processors are not being utilized ? You take a
look at the processor usage, either through task manager or
Performance (System) Monitor, and notice only one processor
seems to be hard at work, the others are just idling.
Well, before you start cursing Microsoft, have a look at
how many files you have for your database out on disk. Is
it one .mdf and one .ldf – the default ? Yes? Well that’s
most likely your problem – you can only write to one file
with one thread. Your database has become IO bound as only
one processor can write to the database at any one time.
The solution is to create a new database (or add to the
existing database) with as many data files (all the same
size) as you have processors and transfer the data from the
old database into this new one – this is to ensure you get
an even proportional fill across the files.
You can still run into issues at times though even after
you do this. If you do create the files properly and still
see this issue, then you have had automatic database growth
turned on for this database. Assuming when you created the
database you created it with all the database data files
the same size, then in such a scenario, at least one of
those files would have now grown beyond that size? If this
is the case then I think your problem is you’ve
lost ‘proportional fill’. Look at the following excerpt
from Books Online (BOL).
From Books Online: As data is written to the filegroup,
Microsoft® SQL Server™ writes an amount proportional to the
free space in the file to each file within the filegroup,
rather than writing all the data to the first file until
full and then writing to the next file. As soon as all the
files in a filegroup are full, SQL Server automatically
expands one file at a time in a round-robin fashion to
accommodate more data (provided that the database is set to
grow automatically).
You can loose proportional fill because now you only have
the one, newly expanded, file to write to; all the others
are full! With only one file to write to, you are
essentially only able to use one CPU
Solution is to avoid automatic database growth on multi-
processor system and have alerts in place to notify you
well in advance when the size is going to become an issue.
| Is This Answer Correct ? | 6 Yes | 5 No |
Post New Answer View All Answers
How to provide default values to stored procedure parameters?
How to use subqueries with the in operators in ms sql server?
How to create database with physical files specified in ms sql server?
What are the advantages of policy management?
What are the different authentication modes in sql server? How can it be changed?
What is a benefit of using an after insert trigger over using a before insert trigger?
What is a dataset and what are the different types of datasets?
Does partitioning ssd reduce performance?
What are the encryption mechanisms in sql server?
What kind of problems occurs if we do not implement proper locking strategy?
What factors you will consider calculating the storage requirement for that view?
How can I check that whether automatic statistic update is enabled or not?
Can you tell me about the concept of ER diagrams?
How many types of keys are there?
How to execute the cursor queries with "open" statements?
