2012年10月30日星期二

表分区的阴暗面

表分区的阴暗面

    本篇文章是我在: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看到了，如我们所知，大家在介绍表分区的时候一直在歌颂其好处。但一句古谚语说的好，每个人都有其阴暗面，表分区也会在特定情况下反而降低其性能。

 

例子

    首先建立测试表,并在其上建立聚集索引:

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

    代码1，创建测试表

    然后插入测试数据:

    

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


   代码2.插入测试数据

 

    插入测试数据的代码貌似复杂，其实只是通过递归CTE的办法生成自1开始的数字，然后为每一个行插入略微递增的日期。对于modifyDate列，每10个记录插入一个略微大的值。此时执行如下查询:

    3
    图1.没有分区的查询计划，看起来不错

 

    对应的，得到的统计信息:

(100 行受影响)
表 'Orders'。扫描计数 1，逻辑读取 310 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
 SQL Server 执行时间:
   CPU 时间 = 15 毫秒，占用时间 = 756 毫秒。

 

    我们DROP掉上面的索引后，重新进行表分区,如代码3所示:

--drop索引DROP INDEX IDX_Orders_DateModified_Id ON dbo.Orders;DROP INDEX IDX_Orders_Id ON dbo.Orders;GO--分区函数CREATE PARTITION FUNCTION pfOrders(DATETIME)AS RANGE RIGHT FOR VALUES ('2012-02-01', '2012-03-01','2012-04-01','2012-05-01','2012-06-01','2012-07-01','2012-08-01');GO--分区方案CREATE PARTITION SCHEME psOrders AS PARTITION pfOrdersALL TO ([primary]);GO--再次创建聚集索引CREATE UNIQUE CLUSTERED INDEX IDX_Orders_OrderDate_IdON dbo.Orders(OrderDate,ID)ON psOrders(OrderDate);GO--再次创建非聚集索引CREATE UNIQUE INDEX IDX_Data_DateModified_Id_OrderDateON dbo.Orders(DateModified, ID, OrderDate)ON psOrders(OrderDate);GO

    代码3.进行分区

 

    然后，我们通过代码2中的代码，再次插入测试数据。然后再次运行图1中所示查询，得到的结果如图2所示。

    4

  图2.对表分区后，性能直线下降

 

    由执行计划可以看出，查询完全忽视了非聚集索引的存在，进行了表扫描。因此产生了巨大的消耗。

    对应的统计信息，如下:

(100 行受影响)表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。表'Orders'。扫描计数2，逻辑读取10071 次，物理读取0 次，预读2 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 SQL Server 执行时间:   CPU 时间= 219 毫秒，占用时间= 783 毫秒。

 

   不难看出，性能下降的十分明显。

 

    因此，不要在生产环境中数据量一大就想到表分区。在进行表分区之前，首先考虑一下对分区计划进行测试，否则在生产环境中出现上面的情况就悲剧了。


TAG:
发帖者 时间：