December 13, 2004 at 12:04 pm
Here's the deal. Our production database is almost 8 Gigs. I inhereted this database when I took my current position and the filegroup setup is a bit klugey. I'm looking to simplify it and at the same time, make sure I handle an unwieldy table that takes up a good 99% of the entire database's size.
There are 10 filegroups, each containing 1 datafile, all of which are on a 136 Gig RAID 5 drive (let's call it "D:\"). The server is a Dell PowerEdge 2650 with . The "Primary" datafile contains the tbEmail table which stores all emails coming into our mail server, along with some other tables whose total sizes pale in comparison. Because of the nature of our business, we're on a few distribution lists, so on a good day, we'll get 5000 individual emails. We need to make the text of the incoming emails searchable, so the the body goes into a column of type TEXT which is actively Fulltext Indexed. There are probably a few dozen fulltext searches per day compared to 2000 to 5000 inserts into that table. tbEmail has about 300K rows right now and is currently 7.5 Gigs.
The way I see it:
On D:\ --
Consolidate all tables except tbEmail into Primary filegroup and keep it here.
Keep the fulltext catalog here.
On E:\ -- (the new high capacity harddrive I'm considering)
Give tbEmail it's own Filegroup and put it here.
Put all non-clustered indexes in a filegroup and keep it here.
It seems to me that if I have it set up this way, I'm addressing the growth rate of the tbEmail table. By moving the nonclustered indexes off the drive that also has the data they're looking up, will I increase read performance? By the same rationale, will I have better fulltext performance having the catalog on D:\ and the table data that it references on E:\?
Have I provided enough info? Posted to the right forum? I'll be vigilantly watching this thread so questions will be answered quickly.
thanks,
greg
December 13, 2004 at 12:30 pm
Greg,
I think your questions may be answered by the Book Online article "Full-Text Search Recommendations" that is accessed by the Index -> Full-Text Catalogs ->Best Practices. Here is what they say about file placement. Please note they say not just a separate hard drive, but separate controller. Read the whole article. It contains more recommendations on what hardware to use, how to configure Windows and SQL Server for the best performance if your database contains millions of rows.
You suggestion is what they recommend in the second and the third paragraph below:
Full-Text Indexing and Catalog Considerations
Regards,Yelena Varsha
December 14, 2004 at 1:55 am
Hm, I would rather use one single filegroup for all tables and indexes and spread this over as much disks as possible. That way you are more likely to get better performance than have your "big" table on a separate filegroup on a single disk.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply