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);
- partitioning large existing non-partitioned tables
- measuring performance impacts of partitioned aligned indexes
- measuring performance impacts of DML triggers for enforcing partitioned unique indexes
- rebuilding tables that are already partitioned (ie. apply a new partitioning scheme)
- implementing partial backups and restores (via leveraging partitions)
- implementing partition aware index optimisation procedures
- 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;
- If the table is a HEAP then create a clustered index on the partition scheme
- 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?
- Don’t partition the table – back away slowly without eye contact
- Understand the NC index rebuild impacts and wear it with a smile
- 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