Validating What is Stored in a Partition/Filegroup

sarahhen — Mon, 23 Jul 2007 13:13:00 GMT

How do you validate what is stored in a specific partition and the filegroup the partition resides on? I have found this handy when designing a partition strategy and to double check it is behaving as I expect and my data is stored correctly for the requirements. It also can help if you document as part of your disaster recovery planning, you can then quickly refer to your documentation and identify the specific filegroup you need to restore to recover specific partitions.

For example, you have a data warehouse that stores previous fiscal year data by quarter for Fiscal Year 2005 through 2006. Your partition function and scheme are defined as follows (see end of notes if you want the filegroup creation so you can step through these commands):

create partition function pf_MyFyQuarters (datetime) as

range right for values

(

'2004-10-01 00:00:00','2005-01-01 00:00:00','2005-04-01 00:00:00','2005-07-01 00:00:00', -- 2005

'2005-10-01 00:00:00','2006-01-01 00:00:00','2006-04-01 00:00:00','2006-07-01 00:00:00') -- 2006

create partition scheme ps_MyFyQuarters

as partition pf_MyFyQuarters

(fgBefore2005, -- partition 1

fg2005Quarter1,fg2005Quarter2,fg2005Quarter3,fg2005Quarter4, -- partition 2, 3, 4, 5

fg2006Quarter1,fg2006Quarter2,fg2006Quarter3,fg2006Quarter4, -- 6, 7, 8, 9

fgAfter2006) -- partition 10

When SQL creates the partition scheme, it maps in order each partition to the filegroup(s) listed and each are numbered in order as the comments demonstrate above. In this example, because the partition function is defined with a 'range right' - partition 1 (assigned to file group fgBefore2005) stores all values less than 10/1/2004. Partition 2 (assigned to file group fg2005Quarter1) stores values >= 10/1/2004 and < 01/01/2005 and so on through partition 10 (assigned to fgAfter2006) that stores values >= 07/01/2006.

Next, to validate the scenario, create a table on the partition scheme with some data:

CREATE TABLE [dbo].[PartitionTable]

(

[DateColumn] [datetime] NULL

)

ON ps_MyFyQuarters (DateColumn)

insert PartitionTable

Values('09/30/2004') -- This will go on partition 1 (file group fgBefore2000)

insert PartitionTable

Values('09/30/2005') -- partition 5 (file group fg2005Quarter4)

insert PartitionTable

Values('10/01/2005') -- partition 6 (file group fg2006Quarter1)

insert PartitionTable

Values('03/31/2006') -- partition 7 (file group fg2006Quarter2)

insert PartitionTable

Values('07/04/2006') -- partition 9 (file group fg2006Quarter4)

You can then use the $PARTITION function to validate what is actually stored by the partition number and the corresponding filegroup based on what you defined in your partition scheme earlier:

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 1

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 5

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 6

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 7

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 9

You can also query from the DMV to list out the partition/scheme and validate what is stored on your filegroups. For example, another administrator created another partition and scheme - but accidentally stores it on the same filegroups. Note what the DMV query returns after you create another partition function and scheme:

create partition function pf_MyFyQuarters_B (datetime) as

range right for values

(

'2002-10-01 00:00:00','2003-01-01 00:00:00','2003-04-01 00:00:00','2003-07-01 00:00:00', -- 2003

'2003-10-01 00:00:00','2004-01-01 00:00:00','2005-04-01 00:00:00','2005-07-01 00:00:00') -- 2004

create partition scheme ps_MyFyQuarters_B

as partition pf_MyFyQuarters_B

(fgBefore2005, -- partition 1

fg2005Quarter1,fg2005Quarter2,fg2005Quarter3,fg2005Quarter4, -- partition 2, 3, 4, 5:

fg2006Quarter1,fg2006Quarter2,fg2006Quarter3,fg2006Quarter4, -- 6, 7, 8, 9:

fgAfter2006) -- partition 10:

-- this DMV query will list out what schemes and partition numbers are stored on your filegroups

-- note the different scheme and partition on the same filegroups

select ds.name AS [Filegroup Name], ds.type, destination_id AS [partition number], dds.partition_scheme_id, ps.name as [partition scheme]

from sys.data_spaces ds

join sys.destination_data_spaces dds

on (ds.data_space_id = dds.data_space_id)

join sys.partition_schemes ps

on (ps.data_space_id = dds.partition_scheme_id)

order by ds.name, ps.name ASC

--------- File/Filegroup Script For Examples Above --------------

create database my_db

-- Add filegroups that will contains partitioned values

alter database my_db add filegroup fgBefore2005;

alter database my_db add filegroup fg2005Quarter1;

alter database my_db add filegroup fg2005Quarter2;

alter database my_db add filegroup fg2005Quarter3;

alter database my_db add filegroup fg2005Quarter4;

alter database my_db add filegroup fg2006Quarter1;

alter database my_db add filegroup fg2006Quarter2;

alter database my_db add filegroup fg2006Quarter3;

alter database my_db add filegroup fg2006Quarter4;

alter database my_db add filegroup fgAfter2006;

-- Add files to filegroups

alter database my_db add file (name = 'fF05Q1', filename = 'C:\fF05Q1.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter1;

alter database my_db add file (name = 'fF05Q2', filename = 'C:\fF05Q2.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter2;

alter database my_db add file (name = 'fF05Q3', filename = 'C:\fF05Q3.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter3;

alter database my_db add file (name = 'fF05Q4', filename = 'C:\fF05Q4.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter4;

alter database my_db add file (name = 'fF06Q1', filename = 'C:\fF06Q1.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter1;

alter database my_db add file (name = 'fF06Q2', filename = 'C:\fF06Q2.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter2;

alter database my_db add file (name = 'fF06Q3', filename = 'C:\fF06Q3.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter3;

alter database my_db add file (name = 'fF06Q4', filename = 'C:\fF06Q4.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter4;

alter database my_db add file (name = 'fBefore05', filename = 'C:\fBefore05.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fgBefore2005;

alter database my_db add file (name = 'fAfter06', filename = 'C:\fAfter06.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fgAfter2006;

Sarah Henwood : disaster recovery, partition, multiple filegroups, partitioning

Validating What is Stored in a Partition/Filegroup