SQL Server Performance Forum – Threads Archive
tables and indexes on different filegroups
We have an end user desktop application with an MSDE 2000 database.We have a database update routine that make updates to tables and recreate all constraints/indexes and functions/procedures/views.
I am planning to split the tables and indexes on different filegroups. The reason for this is to gain little more performance and to decrease the backup size by only backup the ‘tables’ filegroup. Can anyone comment on this about my expected results, the (dis)advantages / pitfalls of splitting the database in different filegroups? Thanks in advance!
I recommend differentiating indexes and tables seperately on seperate filegroups.
Additional administration is required to physically segment files and filegroups. The additional effort may prove worthwhile when segmenting for the purposes of isolating and improving access to very active tables or indexes. Few pointers I would like to mention: – File and filegroups may be used to maintain the sequential placement of data on disk, thus reducing or eliminating nonsequential I/O activity. – Physically segmenting files and filegroups may be appropriate during database development and benchmarking so database I/O information can be gathered and applied to capacity planning for the production database server environment. – Multiple parallel scans can be executed for a single table if the table is in a filegroup that contains multiple files. Whenever a table is accessed sequentially, a separate thread is created to read each file in parallel. – Creating more files for each filegroup can help increase performance because a separate thread is used to scan each file in parallel. – 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. http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=42&rl=1 for information and also search for similar topic posts in this forums. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
For a heavily accessed table, place this table in one filegroup and place the table’s indexes in a different filegroup on different physical disks. This will improve performance, because separate threads will be created for the table’s and index’s data in parallel. http://www.sql-server-performance.com/ac_filegroup_performance.asp says —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka
]]>