SQL Server Performance Forum – Threads Archive

Seperating Index and Table.

This article http://www.sql-server-performance.com/filegroups.asp suggests
"For VLDB, tables and their related indexes should be separated onto separate files and physical disks for optimum performance running queries, but not separate filegroups. If they are in separate filegroups, then you cannot back up and restore them as a single unit. [7.0, 2000]" I can’t figure out how this is possible. Creating tables and indexes only seem to allow specification of a filegroup, not a physical file. So, how do I specify different files but the same filegroup? Thanks

I’m not expert with VLDB, but you can define other file in other disk using same filegroup.
Still your question is valid, how to separate index from tables in same filegroup.
I suggest wait for others opinions. I’ll learn with you. Luis Martin
Moderator
SQL-Server-Performance.com

Hi ya, I’m pretty sure that this is not possible… files within a filegroup are always striped, you cannot determine which file in the filegroup to get SQL to write to… Cheers
Twan

Also, how do they figure that you can’t back them up as a single unit ? BACKUP database backups up all filegroups. We are planning to move out LDB (only 200 gb, so not Vldb yet) to a seperate index filegroup on a seperate SAN alloacted drive, on it’s own controller, when we can get the massive change through our environments ( > 1000 indexes on 740 tables – takes a while when the code base is changing on a weekly basis <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />. HTH Panic, Chaos, Disorder … my work here is done –unknown

I think its the wording in the quote from the site. I had to read it a couple of times, go back to BOL and a couple of other texts, but this is what I get from it. If you have a VLDB, its a good idea to create a filegroup that is separated onto separate files and physical disks for optimum performance. Then place your tables (with their indexes) onto these file groups as is appropriate. This fits in with what BOL says on the advantage of a single filegroup spread over multiple disks
If the filegroup comprises multiple files spread across various physical disks, each with its own disk controller, then queries for data from the table will be spread across the disks, thereby improving performance. The same effect can be accomplished by creating a single file on a RAID (redundant array of independent disks) level 0, 1, or 5 device.
"If the filegroup comprises multiple files spread across various physical disks, each with its own disk controller, then queries for data from the table will be spread across the disks, thereby improving performance. The same effect can be accomplished by creating a single file on a RAID (redundant array of independent disks) level 0, 1, or 5 device. "
"It is advantageous to get as much data spread across as many physical drives as possible in order to improve throughput through parallel data access using multiple files. To spread data evenly across all disks, first set up hardware-based disk striping, and then use filegroups to spread data across multiple hardware stripe sets if needed." Also in BOL, on placing indexes on filegroups…..you ‘can’ place non-clustered indexes in different filegroups, hence different files, but its not recommended from an ease of backup/restore (it only affects the ease)
"If the indexes of a table span multiple filegroups, all filegroups containing the table and its indexes must be backed up together, after which a transaction log backup must be created. Otherwise, only some of the indexes may be backed up, preventing the index from being recovered if the backup is restored later. "
"By creating the index on a different filegroup, you can realize performance gains if the filegroups make use of different physical drives with their own controllers. Data and index information can then be read in parallel by multiple disk heads. For example if Table_A on filegroup f1 and Index_A on filegroup f2 are both being used by the same query, performance gains can be achieved because both filegroups are being fully used with no contention. However, if Table_A is scanned by the query but Index_A is not referenced, only filegroup f1 is used, resulting in no performance gain." Hope that helped more than it confused. Chris

quote:"If the indexes of a table span multiple filegroups, all filegroups containing the table and its indexes must be backed up together, after which a transaction log backup must be created. Otherwise, only some of the indexes may be backed up, preventing the index from being recovered if the backup is restored later. "

Surely this is only applicable if one is doing filegroup backups ? when you do a full database backup, it will backup all filegroups, index and data. In fact, one might theorize that full backups may gain a performance advantage from having this filegroups split on different drives, in much the same way as the query which wanted to use a table on drive FG1 on driveA, and an index on FGB on driveB … i.e. hopefully the backup might obtain parallel access to the 2 drives … would be interesting to test.
Panic, Chaos, Disorder … my work here is done –unknown

Another interesting point is that, for VLDB there are cases where some of the data is not updated very frequently and hence does not need to be backed up so often. In this case, the less volatile data can be placed on a different file group which can be backed up less often. I think this is why there is a mention of having filegroup backups in the original statement. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

]]>