Information: I took over a SQL Server 2005 that the previous DBA had configured three filegroups to segregate the data onto unique LUNs:
G: Data (Primary)
However, the developers are not certain that these Filegroups have been maintained over the last few years. For example, Audit tables or Indexes might be on the primary filegroup and not their respective FG. Also, while I began to try and look into this I don't see any evidence that the server has partitioning setup for these filegroups? Using system tables:
select distinct [partition_number] from sys.partitions select * from sys.data_spaces
The first query only returns "1" which leads me to believe there are no partitions configured. And the second query returns three FG's (Primary, Audit, and Index).
Question: Does anyone know how I can determine that no partitions exists and/or validate that the data is being stored on it's proper filegroup? Am I wrong in thinking that these filegroups aren't really providing much performance increase without the use of partitioning?
Currently reading through http://msdn.microsoft.com/en-us/library/ms345146.aspx for more information on partitioning, but any advice is welcome!