Home / server / Questions / 291452
Accepted
David George
Asked: 2011-07-19 06:34:00 +0800 CST

Validate SQL Server partitions and filegroups

  • 772

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!

sql-server database-administration database-performance

1 Answers

  1. Best Answer

    SELECT * from sys.partition_schemes

    • 0