Partitioning FACT Table with 664 Million Records

I had recently completed Creating Partitioning  and adding new index for table with 664 million records to improve query performance for Group finanance Report generation. 

 --PART1

-- Creating a Partition Function

CREATE PARTITION FUNCTION IntegerPartitionFunction (INT)

AS RANGE LEFT FOR VALUES (2019, 2020, 2021, 2022, 2023, 2024, 2025, 2026, 2027, 2028, 2029 ); 

--The result for this RANGE LEFT assignment is

CREATE PARTITION SCHEME IntegerPartitionScheme

AS PARTITION  IntegerPartitionFunction

ALL TO ([PRIMARY]) -- Because all data currently in One File group

GO

/*

CREATE PARTITION SCHEME IntegerPartitionScheme

AS PARTITION IntegerPartitionFunction

TO (

[PARTITION_FG1], [PARTITION_FG2], [PARTITION_FG3], [PARTITION_FG4],

[PARTITION_FG5], [PARTITION_FG6], [PARTITION_FG7], [PARTITION_FG9],

[PARTITION_FG9], [PARTITION_FG10], [PARTITION_FG11] ,

[PRIMARY])

 */

-- Check Partition schemas

SELECT ps.name, pf.name, boundary_id,value

FROM sys.partition_schemes ps

INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id

INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id

GO

 

--PART2

--Index #1

CREATE NONCLUSTERED INDEX IX_FACT_1

ON dbo.FACT_SALES_DATA 

(

[YearId]

)

  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

  ON IntegerPartitionScheme(YearId)

GO

 

--Index #2

CREATE NONCLUSTERED INDEX IX_FACT_2

ON dbo.FACT_SALES_DATA 

(

 [SId] ASC , 

 [YearId] ASC , 

 [MonthId] ASC 

)

  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

  ON IntegerPartitionScheme(YearId)

GO

 

How to add more partitions to existing table

 --STEP 1 : Find File group

SELECT name AS AvailableFilegroups
FROM sys.filegroups WHERE type = 'FG'

--AvailableFilegroups
-------------------
--PRIMARY


--- STEP 2 : Create Partition Range Function & Schema

SELECT * FROM sys.partition_functions WHERE [name] = N'PF_CREATED_DATE'  
SELECT * FROM sys.partition_schemes   WHERE [name] = N'PS_PURCHASE_ORDER' 
  

CREATE PARTITION FUNCTION [PF_CREATED_DATE](Date) 
AS RANGE RIGHT FOR VALUES (
N'2019-08-01',
N'2019-08-02',
N'2019-08-03',
N'2019-08-04',
N'2019-08-05',
N'2019-08-06',
N'2019-08-07',
N'2019-08-08',
N'2019-08-09',
N'2019-08-10',
N'2019-08-11',
N'2019-08-12',
N'2019-08-13',
N'2019-08-14',
N'2019-08-15')

CREATE PARTITION SCHEME [PS_PURCHASE_ORDER] AS PARTITION [PF_CREATED_DATE]  TO  ( 
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ) 


--- STEP 3 : Create TABLE & Index

 SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PURCHASE_ORDER](
[PURCHASE_ORDER_ID] [bigint] NOT NULL,
[CREATED_DATE] [date]  NOT NULL,
[LOAD_ID] [int] NOT NULL
) ON [PS_PURCHASE_ORDER]([CREATED_DATE])
GO


CREATE CLUSTERED INDEX [CI_CREATED_DATE] ON [dbo].[PURCHASE_ORDER]
(
[CREATED_DATE] ASC,
[PURCHASE_ORDER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PS_PURCHASE_ORDER]([CREATED_DATE])
GO


--- STEP 4 : Add new partitions

 -- Alter PS to add File group
 ALTER PARTITION SCHEME [PS_PURCHASE_ORDER]  NEXT USED  [PRIMARY]

 --ALTER  PF to add new partition
 ALTER PARTITION FUNCTION [PF_CREATED_DATE]()  SPLIT RANGE (N'2019-08-16')

  -- Alter PS to add File group
 ALTER PARTITION SCHEME [PS_PURCHASE_ORDER]  NEXT USED  [PRIMARY]

 --ALTER  PF to add new partition
 ALTER PARTITION FUNCTION [PF_CREATED_DATE]()  SPLIT RANGE (N'2019-08-17')

Partitioning Large Table for 1120 millions Rows with SQL Server 2008R2

Create partitioned for existing table with more than 1120 millions (1120962253) rows today. It was amazing to see the query performance within few seconds with my client database.

Make sure to create a files groups ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4] for partitioned table 
Also you can create Separate file group for Non-Clustered Index  on different drive NCINDEX_FG5

USE [TestDB]
GO

--Step 1. Creating a Partition Function
CREATE PARTITION FUNCTION PFSvrId_Left (numeric (10,0))
AS RANGE LEFT FOR VALUES (399, 499, 699, 799);

--The result for this RANGE LEFT assignment is:
--{min … 399}, {400 … 499}, {500 … 699}, {799 … max}

--Step 2. Creating a Partition Scheme
CREATE PARTITION SCHEME SvrIdScheme
AS PARTITION  PFSvrId_Left
TO ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4],[PRIMARY])

--Step 3. CREATE CLUSTERED INDEX 1

-- Now create a Partitioned using clustered index based Scheme
CREATE CLUSTERED INDEX [idx_LoadID] ON [dbo].[MyTable]
(
      [M_ID] ASC
) ON SvrIdScheme(s_id)

-- Step 4.  CREATE NONCLUSTERED INDEX 2 on Separate file group  
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
(  [t_id] ASC,  [s_id] ASC
)ON [NCINDEX_FG5] 
GO

--– Check for new partitions
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('MyTable')
 GO