Answer / 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.

What is logon trigger?

0 Answers  

---

Does the unique constraint create an index?

0 Answers  

---

what are triggers? : Sql server database administration

0 Answers  

---

A successfully created SSIS package in SQL Server 2005 runs fine in MS BIDS and Integration Services. But gives error when run through an SQL-Job. What are the possible reasons?

2 Answers   Sapient,

---

What is difference between primary key and foreign key?

0 Answers  

---

---

Describe how to use linked server?

0 Answers  

---

Write a SQL query to delete a table?

0 Answers   Amdocs,

---

How should i optimize the time for execution of stored procedure having single input and many output from the different tables?

0 Answers  

---

List out some of the requirements to set up a sql server failover cluster?

0 Answers  

---

Tell me what is the difference between locking and multi-versioning?

0 Answers  

---

What is Deadlock?

4 Answers   Satyam,

---

Create and insert into temp table in sql server?

0 Answers  

---