Lesson Learned #60: Using Partition Option in Azure SQL Managed Instance

[Jose_Manuel_Jurado]

First published on MSDN on Jan 19, 2019
Hello,

Time ago, we discussed in this post about partitioning option in Azure SQL Database.

One of the main differences creating this partition in Azure SQL Database is all partition will use the same filegroup. In Azure SQL Managed Instance we could create different filegroups and files for each partition having a better performance having multiple files per database.

Let me show you an example:

[code language="SQL"]
CREATE DATABASE dbPartitionP15
go
USE dbPartitionP15
GO
-- ====================================
-- How many filegroups/files that we have in this database
-- ====================================
SELECT * FROM SYS.sysFILES
-- ====================================
-- Create the filegroups
-- ====================================
ALTER DATABASE dbPartitionP15
ADD FILEGROUP Data01;
GO
ALTER DATABASE dbPartitionP15
ADD FILEGROUP Data02;
GO
ALTER DATABASE dbPartitionP15
ADD FILEGROUP Data03;
GO
ALTER DATABASE dbPartitionP15
ADD FILEGROUP Data04;
GO
ALTER DATABASE dbPartitionP15
ADD FILEGROUP Data05;
GO
ALTER DATABASE dbPartitionP15
ADD FILEGROUP Data06;
GO
ALTER DATABASE dbPartitionP15
ADD FILEGROUP Data07;
GO
ALTER DATABASE dbPartitionP15
ADD FILEGROUP Data08;
GO
ALTER DATABASE dbPartitionP15
ADD FILEGROUP Data09;
GO
ALTER DATABASE dbPartitionP15
ADD FILEGROUP Data10;
GO

-- ====================================
-- Define a file per filegroup as an example
-- ====================================

ALTER DATABASE dbPartitionP15
ADD FILE
(
NAME = Data01_01,
SIZE = 50MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
TO FILEGROUP Data01;

ALTER DATABASE dbPartitionP15
ADD FILE
(
NAME = Data02_01,
SIZE = 50MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
TO FILEGROUP Data02;

ALTER DATABASE dbPartitionP15
ADD FILE
(
NAME = Data03_01,
SIZE = 50MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
TO FILEGROUP Data03;

ALTER DATABASE dbPartitionP15
ADD FILE
(
NAME = Data04_01,
SIZE = 50MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
TO FILEGROUP Data04;

ALTER DATABASE dbPartitionP15
ADD FILE
(
NAME = Data05_01,
SIZE = 50MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
TO FILEGROUP Data05;

ALTER DATABASE dbPartitionP15
ADD FILE
(
NAME = Data06_01,
SIZE = 50MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
TO FILEGROUP Data06;

ALTER DATABASE dbPartitionP15
ADD FILE
(
NAME = Data07_01,
SIZE = 50MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
TO FILEGROUP Data07;

ALTER DATABASE dbPartitionP15
ADD FILE
(
NAME = Data08_01,
SIZE = 50MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
TO FILEGROUP Data08;

ALTER DATABASE dbPartitionP15
ADD FILE
(
NAME = Data09_01,
SIZE = 50MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
TO FILEGROUP Data09;

ALTER DATABASE dbPartitionP15
ADD FILE
(
NAME = Data10_01,
SIZE = 50MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
TO FILEGROUP Data10;


-- ====================================
-- Create the partition function.
-- ====================================
CREATE PARTITION FUNCTION PF_HASH_BY_VALUE (INT) AS RANGE LEFT
FOR VALUES (100000, 200000, 300000, 400000, 500000, 600000, 700000, 800000, 900000)
SELECT * FROM sys.partition_functions
-- ====================================
-- Create the schema partition.
-- ====================================
CREATE PARTITION SCHEME PS_HASH_BY_VALUE
AS PARTITION PF_HASH_BY_VALUE
TO (Data01,Data02,Data03,Data04,Data05,Data06,Data07,Data08,Data09,Data10);
GO
SELECT * FROM sys.partition_schemes
-- ====================================
-- Configure the Distribution data.
-- ====================================
SELECT
MY_VALUE,
$PARTITION.PF_HASH_BY_VALUE(MY_VALUE) AS HASH_IDX
FROM
(
VALUES
(1),
(100001),
(200001),
(300001),
(400001),
(500001),
(600001),
(700001),
(800001),
(900001)
) AS TEST (MY_VALUE);
GO
-- ====================================
-- Create the table.
-- ====================================
CREATE TABLE [TBL_PARTITION]
( [MY_VALUE] [int] NOT NULL, Age float ,
CONSTRAINT [PK_TBL_PARTITION] PRIMARY KEY CLUSTERED ([MY_VALUE] ASC)
) ON PS_HASH_BY_VALUE ([MY_VALUE])

-- ====================================
-- Add some rows
-- ====================================

DECLARE @nValues AS INTEGER = 0
DECLARE @nFormat AS INTEGER = 0

begin transaction
WHILE @nFormat <10
BEGIN
SET @nFormat = @nFormat +1
SET @nValues = 0
WHILE @nValues<=90000
BEGIN
SET @nValues =@nValues+1
insert into [TBL_PARTITION] (my_value,Age) values(CONVERT(int,CONVERT(varchar(20),@nformat)+CONVERT(varchar(20),@nValues)),RAND())
END
END
commit transaction

-- ====================================
-- Review the partition distribution.
-- ====================================
SELECT object_name(object_id),* FROM sys.dm_db_partition_stats where object_name(object_id)='TBL_PARTITION'
[/code]

Enjoy!