Using File Groups

Question

Post reply

Using File Groups

brokenrulz

SSCrazy

Points: 2261
More actions
September 19, 2003 at 5:41 am

#63351

We have a SQl server 2000 production Database installed on NT4.
From last few days the database is going very slow.
Can I achieve some performance benefits by moving certain related tables on different
Filegroups.
He who knows others is learned but the wise one is one who knows himself.

He who knows others is learned but the wise one is one who knows himself.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

quote:
Can I achieve some performance benefits by moving certain related tables on different
Filegroups.

if these filegroups are on the same physical disk, I don't think you get a boost in performance, if at all.

However, placed on separate disks there should be an increase.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

brokenrulz SSCrazy Points: 2261 More actions · Answer 2

If I need to move existing tables to other filegroups, How do I do them?

I mean do I have to do it explicitly?

Like Create a table on other file group then transferring data from the old table and so on.

or is there any simpler method?

He who knows others is learned but the wise one is one who knows himself.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

quote:
or is there any simpler method?

EM?

No, serious, look at BOL for files and file groups

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

brokenrulz SSCrazy Points: 2261 More actions · Answer 4

I am not sure if it can be done using EM

I think I am missing something obvious.

Can u guide me frank?

He who knows others is learned but the wise one is one who knows himself.

brokenrulz SSCrazy Points: 2261 More actions · Answer 5

One way I found is using the database diagrams.

There one can change the file groups.

He who knows others is learned but the wise one is one who knows himself.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

quote:
I am not sure if it can be done using EM
I think I am missing something obvious.
Can u guide me frank?
He who knows others is learned but the wise one is one who knows himself.

In EM right click on the table in question-Design Table->right click ->properties-> Table filegroups...

however, I donot only use filegroups for performance reasons.

It's the only way in SQL 7 and SQL2k to backup a only parts of a db.

One might consider this reaon also.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 7

quote:
From last few days the database is going very slow.
Can I achieve some performance benefits by moving certain related tables on different
Filegroups.

Are you sure it was caused by I/O? You need monitor the system using performance monitor and SQL Server Profiler to find out where are the exactly the slowness cause.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 8

I'll go with Allen_Cui, first try and determine what is causing the problem

Reminder: Make sure your backups have been done regularly, just in case.

Jeff W SSCertifiable Points: 5207 More actions · Answer 9

Another benefit of seperating your tables/indexes to different file groups is the possibility of getting multiple threads to the disks. Like Frank said, if they are all on the same disk chances are your performance is not going to improve, but your disk configuration could be a factor as well.

"Keep Your Stick On the Ice" ..Red Green