SQL Server Large Tables with Data Partitioning

Partitioning is primarily planned to make managing large tables easier for database administrators. However, partitioned objects can also improve performance of SELECT, UPDATE and DELETE queries.

It is just like one large table made up of multiple little tables. Each chunk, or partition, has the same columns– just a different range of rows.

Prior to SQL Server 2016 SP1, partitioned tables and indexes were not available in every edition of SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and Supported Features for SQL Server 2016.

 Benefits of Partitioning

Partitioning can provide tremendous benefit to a wide variety of applications by improving manageability, availability and performance. It is not unusual for partitioning to greatly improve the performance of certain queries or maintenance operations. Moreover, partitioning can greatly simplify common administration tasks.

Partitioning can enhance query performance, but there is no guarantee, The reason for this is you can only partition on a single column (the partitioning key) and this is what will be used for partition elimination.” And, “In summary, partitioning is mostly for improved maintenance, fast loads, fast deletes and the ability to spread a table across multiple filegroups; it is not primarily for query performance.

Creating a Partitioned Table

To create a partitioned table there are a few steps that need to be done:

  1. Create additional filegroups if you want to spread the partition over multiple filegroups.
  2. Create a Partition Function
  3. Create a Partition Scheme
  4. Create the table using the Partition SchemeStep 1To start with, let’s create a sample database which contains several filegroups for our demo purposes.
    –Create the Database
    CREATE DATABASE SE_DEMO;
    –Now Create Five Filegroups
    ALTER DATABASE SE_DEMO ADD filegroup FGDayRange01;
    ALTER DATABASE SE_DEMO ADD filegroup FGDayRange02;
    ALTER DATABASE SE_DEMO ADD filegroup FGDayRange03;
    ALTER DATABASE SE_DEMO ADD filegroup FGDayRange04;
    ALTER DATABASE SE_DEMO ADD filegroup FGDayRange05;

    Step 2
    Now let’s create the files for each filegroup.

    –Now Add One File to Each Filegroup
    ALTER DATABASE SE_DEMO
    ADD FILE
    (
    NAME = FGDayRange_01, FILENAME = ‘C:\FGDayRange_01.NDF’, SIZE = 5MB, FILEGROWTH 1MB
    )
    TO FILEGROUP FGDayRange01;ALTER DATABASE SE_DEMO
    ADD FILE
    (
    NAME = FGDayRange_02, FILENAME ‘C:\FGDayRange_02.NDF’, SIZE 5MB, FILEGROWTH 1MB
    )
    TO FILEGROUP FGDayRange02;
    ALTER DATABASE SE_DEMO
    ADD FILE
    (
    NAME FGDayRange_03, FILENAME = ‘C:\FGDayRange_03.NDF’, SIZE = 5MB, FILEGROWTH = 1MB
    )
    TO FILEGROUP FGDayRange03;ALTER DATABASE SE_DEMO
    ADD FILE
    (
    NAME = FGDayRange_04, FILENAME = ‘C:\FGDayRange_04.NDF’, SIZE = 5MB, FILEGROWTH = 1MB
    )
    TO FILEGROUP FGDayRange04;

    ALTER DATABASE SE_DEMO
    ADD FILE
    (
    NAME FGDayRange_05, FILENAME = ‘C:\FGDayRange_05.NDF’, SIZE = 5MB, FILEGROWTH = 1MB

    )
    TO FILEGROUP FGDayRange05;

    USE SE_DEMO
    GO

    Step 3 – Create Partition Function
    This creates a range of values for the partition. This will create six partitions:

    –Now Create a Partition Function
    CREATE Partition FUNCTION pfFiveDayRange (Datetime)
    AS RANGE RIGHT FOR VALUES
    (‘2017-10-01’, ‘2017-10-02’, ‘2017-10-03’, ‘2017-10-04’, ‘2017-10-05’)
    GO

    Step 4 – Create partition scheme
    Then we create our partition scheme.
    This creates the partition scheme to determine where each of the partitions will reside.

    –Now create a Partition Scheme to Hold Five Filegroups
    –These Five Filegroups will hold the data for the five dates defined in Partition Function.
    CREATE PARTITION SCHEME psFiveDayRange
    AS PARTITION pfFiveDayRange
    TO ([PRIMARY], [FGDayRange01], [FGDayRange02], [FGDayRange03], [FGDayRange04],[FGDayRange05]);

    Step 5 – Create a new partitioned table
    This creates the table using the partition scheme psFiveDayRange that was created in step 3.  The column businessDate is used to determine what data gets placed in which partition/filegroup.

    –Now create a Partitioned Table and clustered index on partition key.
    CREATE TABLE SE_DEMO_Partitions
    (ID INT IDENTITY,
    businessDate DATETIME NOT NULL)
    ON psFiveDayRange (businessDate);
    GO
    CREATE CLUSTERED INDEX IDXC_businessDate ON SE_DEMO_Partitions(businessDate)

    Step 6
    Adding Data to Partitioned Table

    After the table has been setup as a partitioned table, when you enter data into the table SQL Server will handle the placement of the data into the correct partition automatically for you.

    So, based on the above setup if we run the below commands the data will be placed in the appropriate partition as shown below.

    SET NOCOUNT ON
    –Load 50 records with each date
    INSERT INTO SE_DEMO_Partitions (businessDate)
    VALUES (‘2017-10-01’)
    GO 50
    INSERT INTO SE_DEMO_Partitions (businessDate)
    VALUES (‘2017-10-02‘)
    GO 50
    INSERT INTO SE_DEMO_Partitions (businessDate)
    VALUES (‘2017-10-03’)
    GO 50
    INSERT INTO SE_DEMO_Partitions (businessDate)
    VALUES (‘2017-10-04’)
    GO 50
    INSERT INTO SE_DEMO_Partitions (businessDate)
    VALUES (‘2017-10-05’)
    GO 50

    To determine what exists in each partition you can run the following command:

    — By Now you have 250 records (50 Each Date) sitting on each Filegroup.
    — Let’s check this out.
    SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName
    ,OBJECT_NAME(i.object_id) AS ObjectName,
    ISNULL(QUOTENAME(i.name),’Heap’) AS IndexName,p.partition_number AS PartitionNumber
    ,fg.name AS Filegroup_Name
    ,rows AS ‘Rows’
    ,au.total_pages AS ‘TotalDataPages’
    ,CASE boundary_value_on_right
    WHEN 1 THEN ‘less than’
    ELSE ‘less than or equal to’
    END AS ‘Comparison’
    ,value AS ‘ComparisonValue’
    ,p.data_compression_desc AS ‘DataCompression’
    ,p.partition_id
    ,ps.name AS PartitionScheme
    ,f.name AS PartitionFunction
    FROM sys.partitions p
    JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
    JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
    JOIN sys.partition_functions f ON f.function_id = ps.function_id
    LEFT JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id
    JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
    JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
    JOIN (SELECT container_id, sum(total_pages) as total_pages
    FROM sys.allocation_units
    GROUP BY container_id) AS au ON au.container_id = p.partition_id
    JOIN sys.tables t ON p.object_id = t.object_id
    WHERE OBJECT_NAME(i.object_id) =‘SE_DEMO_Partitions’
    ORDER BY ObjectName,i.name,p.partition_number;
    GO

    Output of above query Something like below:

    In addition to determining the number of rows that are in each of the partitions we can also see how fragmented each of these partitions are.  By using the DMV sys.dm_db_index_physical_stats we can get this information.

    SELECT object_id, partition_number,
    avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N’dbo.SE_DEMO_Partitions’),
    NULL , NULL, NULL);

    Output of above query Something like below:

    Based on this results from sys.dm_db_index_physical_stats, you can rebuild an index for a partition.  Here is an example of the code that could be used to rebuild index IDXC_businessDate only on partition #5.

    ALTER INDEX IDXC_businessDate
    ON dbo.SE_DEMO_Partitions
    REBUILD Partition = 5;
    GO

    As you can see this is a great enhancement to SQL Server.

    I hope this gives you an idea of how helpful data partitioning can be to move sets of data and maintain large datasets.  Although this is very simple on the outside there are several things that need to be thought about before implementing a data partitioning scheme.

By |2019-01-27T15:07:37+00:00December 20th, 2018|Uncategorized|

About the Author:

SCALABILITY ENGINEERS PVT. LTD.

WE ARE ONE-STOP SOLUTION FOR YOUR MICROSOFT SQL SERVER MANAGEMENT.

LET’S TALK

CONTACT US

 Adrian Clayborn Authentic Jersey