0

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)
F: Audit
J: Index

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!

David George
  • 888
  • 1
  • 9
  • 21

1 Answers1

0

SELECT * from sys.partition_schemes

jl.
  • 1,076
  • 8
  • 10
  • I suspect the DBA set up filegroups and directed specific database objects to them, but did actually partition any tables. – jl. Jul 18 '11 at 17:37
  • Yes, you're correct. Is there any benefit to having these filegroups without using a partition scheme? – David George Jul 20 '11 at 12:03
  • Any benefit? "Your mileage may vary" :-) . In my experience, I identified high use tables and specifically moved them to their own file group on a separate drive. Then compared query results with a set of base queries and perfmon output to determine if there was improvement. This was usually with a third-party app where we didn't control the schema and code. – jl. Jul 20 '11 at 13:23