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!

Improve this question

1 Answer 1

Reset to default
0

SELECT * from sys.partition_schemes

Improve this answer
3
  • I suspect the DBA set up filegroups and directed specific database objects to them, but did actually partition any tables. Commented Jul 18, 2011 at 17:37
  • Yes, you're correct. Is there any benefit to having these filegroups without using a partition scheme? Commented Jul 20, 2011 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. Commented Jul 20, 2011 at 13:23

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.