Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting Rid of Excessive Files and Filegroups in SQL Server Expand / Collapse
Author
Message
Posted Saturday, February 8, 2003 12:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:02 PM
Points: 259, Visits: 716
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cmiller/gettingridofexcessfilesandfilegroups.asp


Post #9856
Posted Saturday, February 15, 2003 9:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 17, 2007 7:24 AM
Points: 5, Visits: 1
There can still be performance gains with multiple filegroups even if they are on the same volume. SQL Server will use 1 I/O thread per physical file for accessing data. So multiple logical file groups (each with at least one file) and file groups with multiple files (data striped across the files) can yield performance enhancements versus having fewer physical data files. Do some research and check it out. I would like to see an in depth article on this topic.




Post #53785
Posted Wednesday, March 5, 2003 11:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 21, 2006 7:24 AM
Points: 3, Visits: 1
Here's another, potentially simpler, way to move everything into one filegroup. I've used this method on databases up to about 800 Mb.

I found out (by accident, of course) that when using DTS to copy a database to another server, it puts all of the objects into the default filegroup. I had a database that had originally been set up with 3 filegroups, and used this "feature" of DTS to successfully relocate all of the objects to a single filegroup.

Here's what I did:
1. Created a new database of appropriate size, etc. (As I am often copying databases to different servers, I have gotten in the habit of creating the database and associating the logins to it by hand, to avoid any potential issues with orphaned users -- this is obviously just my personal preference.)
2. Right-clicked on it, selected All Tasks -> Import Data... to bring up the DTS Import/Export Wizard.
3. Indicated the source and destination databases.
4. Clicked "Copy objects and data between SQL Server databases."
5. Selected "Create destination objects" and "Copy data." (Deselected the security options included in "Default Options" since I had already set up the logins.)
At this point, I ran the DTS job, which creates a series of files including ones named:

[server name].[database name].TAB (ddl for tables)
[server name].[database name].ID1 (ddl for clustered indexes)
[server name].[database name].ID2 (ddl for nonclustered indexes)
[server name].[database name].DR1 (ddl for clustered primary keys)
[server name].[database name].DR2 (ddl for nonclustered primary keys)

While the PK and index DDL does include the Fill Factor, none of these scripts indicate the filegroup, so they all take the default value, which would be PRIMARY unless set otherwise.

This method won't copy the statistics, but SQL Server should regenerate them. I, too, prefer to use scripts rather than Enterprise Manager; in this case, I feel that using DTS is a reasonable compromise. I get the ease of use of the GUI interface, but all of the action is captured in the scripts that DTS creates.

Eileen Gaisser
Database Administrator
Eastman Kodak Co.



Post #53786
Posted Friday, March 5, 2004 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 8:45 AM
Points: 49, Visits: 43

Regarding the often underemphasized I/O benefit that comes with using separate files, the following comes from BOL "Using Files and Filegroups"  ---> "Recommendations":

Place different tables used in the same join queries in different filegroups. This will improve performance, due to parallel disk I/O searching for joined data.




Post #104609
Posted Wednesday, March 10, 2004 5:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 3, 2009 2:26 PM
Points: 53, Visits: 19

I am glad that I got rid of those stats tables...so I can removed all the empty filegroups!

Thanks!



DBA/OLAP Developer -
San Francisco, CA
Post #105428
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse