Dark Side of table partitions

This article is in: http://www.simple-talk.com/ SQL /database-administration/partitioned-tables,-indexes-and-execution-plans-a-cautionary-tale? Utm_source = simpletalk & utm_medium = Email-Main & utm_content = partitionedtables-20121029 & utm_campaign = SQL, as we know, we have been praising the benefits of Table Partitioning. But as an old saying goes, everyone has a dark side, and Table Partitioning also reduces its performance under certain circumstances.

 

Example

First, create a test table and create a clustered index on it:

CREATE TABLE dbo.Orders    (      Id INT NOT NULL ,      OrderDate DATETIME NOT NULL ,      DateModified DATETIME NOT NULL ,      Placeholder CHAR(500)        NOT NULL        CONSTRAINT Def_Data_Placeholder DEFAULT 'Placeholder',    );GOCREATE UNIQUE CLUSTERED INDEX IDX_Orders_IdON dbo.Orders(ID);GO 

Code 1: Create a test table

Insert the test data:

WITH    N1 ( C )          AS ( SELECT   0               UNION ALL               SELECT   0             )-- 2 rows,       N2 ( C )          AS ( SELECT   0               FROM     N1 AS T1                        CROSS JOIN N1 AS T2             )-- 4 rows,       N3 ( C )          AS ( SELECT   0               FROM     N2 AS T1                        CROSS JOIN N2 AS T2             )-- 16 rows,       N4 ( C )          AS ( SELECT   0               FROM     N3 AS T1                        CROSS JOIN N3 AS T2             )-- 256 rows,       N5 ( C )          AS ( SELECT   0               FROM     N4 AS T1                        CROSS JOIN N4 AS T2             )-- 65,536 rows,       N6 ( C )          AS ( SELECT   0               FROM     N5 AS T1                        CROSS JOIN N2 AS T2                        CROSS JOIN N1 AS T3             )-- 524,288 rows,       IDs ( ID )          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL                                                     ) )               FROM     N6             )             SELECT * FROM IDs    INSERT  INTO dbo.Orders            ( ID ,              OrderDate ,              DateModified            )            SELECT  ID ,                    DATEADD(second, 35 * ID, @StartDate) ,                    CASE WHEN ID % 10 = 0                     THEN DATEADD(second,                                  24 * 60 * 60 * ( ID % 31 ) + 11200 + ID                                      % 59 + 35 * ID, @StartDate)                         ELSE DATEADD(second, 35 * ID, @StartDate)                    END            FROM    IDs;GO  

Code 2. Insert Test Data

 

The code for inserting test data seems complicated. In fact, it only generates numbers starting from 1 through recursive CTE, and inserts a slightly increasing date for each row. For the modifydate column, a slightly larger value is inserted for each 10 records. Execute the following query:

Figure 1. No partition query plan, looks good

 

Corresponding statistical information:

(Row 3 is affected)
Table 'Orders '. Scan count 1, logical reads 310, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and LOB pre-reads 0.
SQL Server execution time:
CPU time = 15 ms, occupied time = 756 Ms.

 

After dropping the index, we can re-partition the table, as shown in code 3:

-- Drop index idx_orders_datemodified_id on DBO. orders; drop index idx_orders_id on DBO. orders; go -- partition function create partition function pforders (datetime) as range right for values ('2017-02-01 ', '2017-03-01', '2017-04-01 ', '2017-05-01 ', '2017-06-01', '2017-07-01 ', '2017-08-01 '); go -- create partition scheme psorders as partition pfordersall to ([primary]); go -- create a clustered index again create unique clustered index idx_orders_orderdate_idon DBO. orders (orderdate, ID) on psorders (orderdate); go -- create a non-clustered index again create unique index idx_data_datemodified_id_orderdateon DBO. orders (datemodified, ID, orderdate) on psorders (orderdate); go

Code 3. Partition

 

Then, we use the code in Code 2 to insert the test data again. Run the query shown in Figure 1 again. Expected result 2 is displayed.

Figure 2. Performance decreases linearly After partitioning a table

 

From the execution plan, we can see that the existence of non-clustered indexes is ignored in queries, and table scanning is performed. This results in a huge consumption.

The corresponding statistics are as follows:

(100 rows affected) 'worktable '. Scan count 0, logical read 0, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. Table 'Orders '. Scan count 2, logical reads 10071, physical reads 0, pre-reads 2, lob logic reads 0, lob physical reads 0, and LOB pre-reads 0. SQL Server execution time: CPU time = 219 Ms, occupied time = 783 Ms.

 

It is not difficult to see that the performance decline is very obvious.

 

Therefore, do not think of table partitions when there is a large amount of data in the production environment. Before partitioning a table, consider testing the partition plan. Otherwise, the above situation may be disastrous in the production environment.