October 26, 2005 at 12:23 pm
I've scoured Books Online (all three topics) regarding placing tables into filegroups (ALTER DATABASE, yada yada yada). I've set up a secondary filegroup, located a file within it, and have even begun reassigning tables to the filegroup.
My question is, when does the actual migration of data from the original (Primary) filegroup to the new (Secondary) take place?
Is it something that happens only when new data is placed in the table? Do I have to backup/restore to affect the move?
I am playing with this on a copy database to try and determine some strategies for improving performance on some of our core systems (30+ active user connections at any given time).
If both filegroups (each with one file) are located on the same RAID array, the fact that they are separate files should still net a performance gain, right? (One virtual, three+ physical drives.) Or do they have to be entirely unrelated drives before any gain is achieved.
Essentially, I know HOW to accomplish what I want, but I need some insight into the practical application of the technology.
Thanks in advance
Warren
October 26, 2005 at 12:43 pm
There will be no performance gain if both files are on the same raid array (the data is striped accross all drives in array).
October 26, 2005 at 12:59 pm
There is no raw performance gain from having multiple filegroups on the same physical array.
However, you can manage backups on different schedules to shrink backup times by placing tables that don't change much in one filegroup, and the more dynamic tables in the other, then backing up the active filegroup more often than the static one.
You will also be more prepared to move to a second array should one get installed.
To move the contents of a table from one filegroup to another, re-create the clustered index in the target filegroup.
-Eddie
Eddie Wuerch
MCM: SQL
October 27, 2005 at 7:52 am
There can be a perfomrance gain, even with the files on the same RAID array. If you have multiple CPUs, SQL Server will/may spawn one thread per file for DiSK i/o. If you only have one file, you get one thread, max.
October 27, 2005 at 9:13 am
We do run multiple CPUs on the servers in question.
Most of the tables do not have clustered indexes. Is there another way to trigger the physical move of the data? I would think that backup/restore should certainly handle this.
October 27, 2005 at 9:30 am
I don't think that a backup and restore will do it. You'll probably have to rename the existing tables, create a new table on the new filegroup, then copy the data from the old table to the new.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply