[read this post on Mr. Fox SQL blog]

I have been working on a SQL DW database and revisiting an existing SQL table partitioning scheme.  The main reasons we’re going to all this trouble is to (a) implement a full partition based optimisation process and (b) implement a partial database backup scheme. (I will blog about these later!)

The SQL DW database has grown significantly and some of the tables are large (2.1+ billion rows) so nearly everything you do with it takes time, needs to be considered and tested.  The data is not a candidate for archive and therefore the partition sliding window technique is not being considered.

The partitioning includes several major components of work (and can be linked below);

1. partitioning large existing non-partitioned tables
2. measuring performance impacts of partitioned aligned indexes
3. measuring performance impacts of DML triggers for enforcing partitioned unique indexes
4. rebuilding tables that are already partitioned (ie. apply a new partitioning scheme)
5. implementing partial backups and restores (via leveraging partitions)
6. implementing partition aware index optimisation procedures
7. Calculating table partition sizes in advance

This first blog post deals with partitioning large existing non-partitioned tables. I will blog about the other steps later.

And so, lets get into the nitty gritty of the partitioning details!

Partitioning large existing non-partitioned tables

For partitioning tables a single column partitioning key is used that needs to be part of the cluster key definition.  It does not have to be the first segment in the key, but it has to be there.

There’s 2 methods to partition an existing table – unfortunately despite what SQL BOL says BOTH methods will have an impact on any existing NC indexes;

1. If the table is a HEAP then create a clustered index on the partition scheme
2. If the table already has a clustered index then rebuild it using the partition scheme. The best method to apply the partition scheme to the table is via the SQL command CREATE INDEX  WITH DROP_EXISTING

From the SQL BOL;

The existing index is dropped and rebuilt. The index name specified must be the same as a currently existing index; however, the index definition can be modified. For example, you can specify different columns, sort order, partition scheme, or index options

If the index enforces a UNIQUE or PRIMARY KEY constraint then DROP_EXISTING keeps that constraint intact during the rebuild (which is awesome).  Also DROP_EXISTING has another sweet benefit – it will rebuild NC indexes only once when rebuilding the clustered index (as opposed to 2 times for a DROP / CREATE).

HOWEVER if you change the index definition in any way (ie change keys) then the statement will fail.  You cannot change a constraint index definition using DROP_EXISTING, you must DROP / CREATE the index. 

Msg 1907, Level 16, State 2, Line 70
 Cannot recreate index 'pk_myorderid'. The new index definition does not match the constraint being enforced by the existing index.

Interestingly you can assign a partition scheme to an existing unpartitioned clustered PRIMARY KEY and this does not qualify as an index definition change and thus no error.  Go figure?

The SQL BOL is not clear – it states if you don’t change the index definition then the NC indexes are not rebuilt HOWEVER if you apply a partition scheme on an existing clustered index then it WILL rebuild the NC indexes! 

SQL does not have a command to rebuild a table to a partition scheme and leave the NC indexes unchanged “in place”.

This can be an extremely costly exercise for disk space, locking, elapsed time and IO.  But there are some “get out of jail free” cards you can play in your rebuild WITH clause;

• Use ONLINE = ON, but it will take longer and potentially cause other impacts, such as greater tempdb usage.  This article can help you understand the impacts https://msdn.microsoft.com/en-us/library/ms190981.aspx
• If you have a high performing tempdb then always use SORT_IN_TEMPDB = ON, but beware on how much space you need to sort the keys.  You will need space approx equivalent to your largest index (which is probably the clustered one). This article can help you work through those calculations https://msdn.microsoft.com/en-us/library/ms191183.aspx

If you have a large table then do some back-of-matchbox calculations, you don’t want to run out of tempdb space 14 hours into an ONLINE 500GB table rebuild!

Lets have a look at the process.

Build our test table with a non-partitioned PK clustered index

CREATE TABLE dbo.myorders
(
     myorder_id        INT NOT NULL
     , myorder_key     INT NOT NULL
     , myorder_date    DATETIME NOT NULL
     , myorder_amt     MONEY NOT NULL
     , myorder_details NVARCHAR(4000) DEFAULT '' NULL
     , CONSTRAINT pk_myorderid PRIMARY KEY CLUSTERED (myorder_id ASC)
)
GO

Build 2x non-clustered indexes

CREATE UNIQUE NONCLUSTERED INDEX myorders_unique_nonaligned1
    ON dbo.myorders (myorder_key)
    ON [PRIMARY] -- Explit filegorup
GO
CREATE NONCLUSTERED INDEX myorders_not_unique_nonaligned2
    ON dbo.myorders (myorder_date)
    ON [PRIMARY] -- Explit filegorup
GO

Put in some test data

begin
    INSERT INTO dbo.myorders
    SELECT
        TOP 70000
        ROW_NUMBER() OVER (ORDER BY o1.object_id)
        , CAST((ROW_NUMBER() OVER (ORDER BY o1.object_id) * RAND(1) * 2) AS INT)
        , DATEADD(hh, (ROW_NUMBER() OVER (ORDER BY o1.object_id)) / 5, DATEADD(dd, -730, GETDATE()))
        , RAND(ROW_NUMBER() OVER (ORDER BY o1.object_id)) * RAND(ROW_NUMBER() OVER (ORDER BY o2.object_id)) * 730
        , REPLICATE('X', RAND(o1.object_id) * 1000)
    FROM   master.sys.objects o1
           CROSS JOIN master.sys.objects o2
           CROSS JOIN master.sys.objects o3
    ORDER  BY 1
end
GO

Create our future use partition scheme

CREATE PARTITION FUNCTION pf_myorders (int) AS RANGE RIGHT FOR VALUES(0, 10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000, 90000, 100000)
GO
CREATE PARTITION SCHEME ps_myorders AS PARTITION pf_myorders ALL TO ([PRIMARY])
GO

NEXT – lets have a look at what our table structures look like pre-partitioning.

SELECT
    s.NAME AS 'schema'
    , o.NAME AS 'table'
    , CASE o.type
          WHEN 'v' THEN 'View'
          WHEN 'u' THEN 'Table'
          ELSE o.type
      END AS objecttype
    , i.NAME AS indexname
    , i.type_desc
    , p.data_compression_desc
    , ds.type_desc AS DataSpaceTypeDesc
    , p.partition_number
    , pf.NAME AS pf_name
    , ps.NAME AS ps_name
    , partitionds.NAME AS partition_fg
    , i.is_primary_key
    , p.rows
    , pst.UsedMB
FROM   sys.indexes i
       INNER JOIN sys.objects o
               ON o.object_id = i.object_id
       INNER JOIN sys.data_spaces ds
               ON DS.data_space_id = i.data_space_id
       LEFT JOIN sys.schemas s
              ON o.schema_id = s.schema_id
       LEFT JOIN sys.partitions p
              ON i.index_id = p.index_id
             AND i.object_id = p.object_id
       LEFT JOIN sys.destination_data_spaces dds
              ON i.data_space_id = dds.partition_scheme_id
             AND p.partition_number = dds.destination_id
       LEFT JOIN sys.data_spaces partitionds
              ON dds.data_space_id = partitionds.data_space_id
       LEFT JOIN sys.partition_schemes AS ps
              ON dds.partition_scheme_id = ps.data_space_id
       LEFT JOIN sys.partition_functions AS pf
              ON ps.function_id = pf.function_id
       INNER JOIN (SELECT
                       object_id
                       , index_id
                       , partition_id
                       , SUM(used_page_count) AS UsedPages
                       , CAST(((SUM(used_page_count) * 8.0) / 1024.0) AS NUMERIC(18, 2)) AS UsedMB
                   FROM   sys.dm_db_partition_stats
                   GROUP  BY
                      object_id
                      , index_id
                      , partition_id) AS pst
               ON p.index_id = pst.index_id
              AND p.object_id = pst.object_id
              AND p.partition_id = pst.partition_id
WHERE
    s.NAME NOT IN ('sys', 'cdc')
    AND o.NAME = 'myorders'
ORDER  BY
    s.NAME
    , o.NAME
    , i.NAME
    , p.partition_number
GO

OK FINALLY, we have everything in place to rebuild our existing clustered index into a partitioned index.  I am not changing any index keys or definition so we can use DROP_EXISTING and apply the partition scheme created earlier.

SET statistics io ON
GO
CREATE UNIQUE CLUSTERED INDEX pk_myorderid
    ON dbo.myorders (myorder_id)
    WITH (DROP_EXISTING = ON)
    ON ps_myorders(myorder_id)
GO

As you run this keep an eye on your system waits and check the drive where your target partitioned filegroups are located.

If you used ONLINE or SORT_IN_TEMPDB then as the rebuild progresses you will see a large sort operation in tempdb, and then the target filegroups slowly fill up with data.

What did SQL do under the covers?

The SHOW STATISTICS IO shows 3 lines, one for each index on the table.  To be sure if you selected to show the graphical execution plan you can see each index being rebuilt.

Table 'myorders'. Scan count 5, logical reads 14052, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Table 'myorders'. Scan count 5, logical reads 14030, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Table 'myorders'. Scan count 5, logical reads 14030, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Lets revisit what our index structure looks like now

You can see that the PRIMARY KEY CONSTRAINT has been retained (is_primary_key) and the clustered index has been created against the partition scheme.

You can also see that the existing NC indexes have not been aligned to the partition scheme despite being rebuilt, instead they remain standalone.  So its a lot of additional work SQL has done for the NC indexes where in effect no cluster key changes have occurred and we have not aligned the NC indexes.  Ouch!

And so, What Are the Final Options?

1. Don’t partition the table – back away slowly without eye contact
2. Understand the NC index rebuild impacts and wear it with a smile
3. If you are planning to align the NC indexes later, then agree on a big outage, drop the NC indexes, rebuild the standard table to a partitioned table, and recreate the NC indexes again against the partitioned table. (Devil is in the detail, one for another blog!)

If your tables have data compression enabled then the IO work needed to support the partitioning rebuild can be significantly reduced. (Again, one for another blog!)

AND of course, as I always say, please test this yourself as your results may vary!

Disclaimer: all content on Mr. Fox SQL blog is subject to the disclaimer found here