asked
can a non-partitioned table exist in multiple files in the one filegroup?
I've been creating new filegroups, and adding multiple files to them (spread across multiple disks). My (high level) question is: if I create a table in a filegroup which has multiple files in it, is the table randomly spread across those files? or, more specifically, my (low level) question: Can an individual Allocation Unit in a partition exist across multiple files? And if so, what sys table or DMV reveals this?
The answer to the first question (is the table spread across multiple files), is YES. I proved this by creating a table in a filegroup with 2 files, and then populated the table with a million rows, and each file grew by the same amount. As to question 2 and 3 - not sure, still investigating. cheers Nigel
>Can an individual Allocation Unit in a partition exist across multiple files? answer - yes. I created a table in a filegroup with 2 files, populated the table, and then ran this query: select * from ( select u.allocation_unit_id "UniqueAllocUnitID", u.type_desc "AllocUnitType", u.container_id "StorageContainerID", u.data_space_id "FileGroup", u.total_pages, u.used_pages, u.data_pages, p.partition_id, o.name, o.object_id, o.type_desc, p.rows from sys.allocation_units u, sys.partitions p, sys.objects o where u.container_id = p.hobt_id and p.object_id = o.object_id and u.type in (1,3) and o.type_desc not in ('SYSTEM_TABLE','INTERNAL_TABLE') union all select u.allocation_unit_id "UniqueAllocUnitID",u.type_desc "AllocUnitType", u.container_id "StorageContainerID", u.data_space_id "FileGroup", u.total_pages, u.used_pages, u.data_pages, p.partition_id, o.name, o.object_id, o.type_desc, p.rows from sys.allocation_units u, sys.partitions p, sys.objects o where u.container_id = p.partition_id and p.object_id = o.object_id and u.type in (2) and o.type_desc not in ('SYSTEM_TABLE','INTERNAL_TABLE') ) nf order by name which shows that the table only has one allocation unit. > what sys table or DMV reveals this? sys.allocation_units shows that there is only one (data) allocation unit per table, regardless of number of files. As to the existence of a DMV that shows which pages are in which files (similar to oracle DBA_EXTENTS etc), I have not been able to determine that. cheers Nigel
>And if so, what sys table or DMV reveals this? DBCC ind('mydatabase', 'mytable',0) Nigel
The short answer is yes, as you already know, and you will gain performance in parallel execution plans when The tables and/or indexes are spread an multiple files if they exists on different disks.
