John W. Colby
jwcolby at colbyconsulting.com
Sun Jan 23 12:47:45 CST 2005
On the same note, is it possible to cause specific tables to be placed in specific files? I started this database with two 250g hard drives for the data and one 250g hard drive for the log file. As I ran short of room in the data files (hard drives) I added a new drive and another file, then a fourth drive and file. I have at this time 4 data drives each with a single data file, and a log drive with a log file. Each of these drives has nothing but the SQL Server file on it. I started out with the "big table" of 64 million records, 650 fields. Since the business requires using almost every field in where clauses, I added a PK field that is an Int - Identity - Increment 1. I then started creating smaller tables with anywhere from a handful to several dozen fields of related data. I copied the PK and these fields (data) from the Big Table into the smaller tables, then set indexes on each field. This work is not complete yet, but I have almost finished creating tables to hold all the sets of related data. Just as an aside, it takes a few hours to pull the PK and data out of BigTable and drop it into the smaller table. I can run many of these at the same time (do several of the smaller tables at the same time). Setting up the indexes takes a few minutes to create the indexes in design view, then a LONG time (many hours) for SQL Server to actually build the indexes. The more fields I index at once the longer it takes of course. I can just imagine what the data looks like inside the four data files though. I was thinking that if I could "copy" a specific table from the database into a new file, that the copy might "defrag" that table (get all the data and indexes for it in one place and contiguous). Additionally it would be good from a performance perspective to get each of these smaller tables into a single drive (file) so that later when I started joining the tables for analysis (where clauses) I would spread the i/o over more drives. Any thoughts on this stuff? Is it possible to determine placement of tables within a multi-file database? Ditto indexes? John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John W. Colby Sent: Sunday, January 23, 2005 1:22 PM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] Placing indexes in a specific file I believe I have seen references to placing indexes in a specific file for SQL Server in order to speed up the database. Does anyone know how this might be accomplished (if possible)? For my nVLDB I now have 685 gb of data in 4 files, each file on a dedicated hard drive, i.e. nothing on that drive except that one database file. I also have a dedicated drive for the log file. I am reaching the point where I need to add a couple of more drives (and files) because I will run out of room in a few weeks. It seems like now might be the time to figure out the index file thing if it is possible. John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com