Friday, July 19, 2013

0 Partition Index for MSSQL

Recently, I got confuse about the partition index, so I decided to do some testing about it.

Create the sample database

use master
CREATE DATABASE [PARTITION_DEMO] ON  PRIMARY 
( NAME = N'PARTITION2011_DATA', FILENAME = N'C:\DATA\PARTITION2011_DATA.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP FG2012
(NAME = 'PARTITION2012_DATA',FILENAME ='C:\DATA\PARTITION2012_DATA.ndf',SIZE =4096KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB )
 LOG ON 
( NAME = N'PARTITION_LOG', FILENAME = N'C:\DATA\PARTITION_LOG.ldf' , SIZE = 2048KB  , FILEGROWTH = 10%);

 

Create partition function and scheme

CREATE PARTITION FUNCTION partiton_demo_f (datetime)
AS RANGE RIGHT FOR VALUES ('9/01/2011','10/01/2011', '11/01/2011', '12/01/2011',
               '1/01/2012', '2/01/2012', '3/01/2012', '4/01/2012',
               '5/01/2012', '6/01/2012', '7/01/2012', '8/01/2012');
GO
 
CREATE PARTITION SCHEME partiton_demo_s
AS PARTITION partiton_demo_f
TO ([PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [FG2012], [FG2012]
, [FG2012], [FG2012], [FG2012]
, [FG2012], [FG2012], [FG2012]
, [FG2012], [FG2012]);
GO

 

create the test table and insert some data

Create Table dbo.orders
(
      order_id  int Identity(1,1)   Not Null
    ,  product_name char(1000) Not Null 
    , orderDate datetime            Not Null
    , orderData smalldatetime       Not Null
 
) On partiton_demo_s(orderDate);
Go
 
 
Set NoCount On;
 
Declare @endDate datetime = '2011-08-01';
Declare @randomString varchar(255);
 
While @endDate < '2012-09-01'
Begin
    SELECT @randomString = CONVERT(varchar(255), NEWID());
    Insert Into dbo.orders 
    Select @randomString,@endDate, @endDate
 
    Set @endDate = DATEADD(minute, 3, @endDate);
 
End;

 

Check the data distribution

use PARTITION_DEMO:
 
SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('dbo.orders');

 

image

Select i.name
    , i.index_id
    , p.partition_number
    , p.rows
From sys.partitions As p
Join sys.indexes As i
    On p.object_id = i.object_id 
   And p.index_id = i.index_id
Where p.object_id = object_id('orders')
Order By i.index_id, p.partition_number;

Because there are no indexes, so the index_id is 0 , meaning it is heap.

image

Create the partition index

 

CREATE NONCLUSTERED INDEX ix_orders_partitioned
    ON dbo.orders(order_id)
     ON partiton_demo_s(orderDate);

 

Now check the index size again

SELECT i.name
    , i.index_id
   ,p.rows
   ,p.partition_id
   ,a.used_pages*8 AS 'Used space(KB)'
FROM sys.partitions AS p
JOIN sys.indexes AS i
    ON p.object_id = i.object_id 
   AND p.index_id = i.index_id
   JOIN sys.allocation_units a ON p.partition_id=a.container_id
WHERE p.object_id = OBJECT_ID('orders')
-- group by i.index_id,i.name
ORDER BY i.index_id

image

Create non partition index

CREATE NONCLUSTERED INDEX ix_orders_unpartitioned
    ON dbo.orders(order_id)
    ON [PRIMARY];

The index id 3 is non partition index which contains all the rows.

image

 

Compare the performance between partition index and non partition index

Case1 with specific query

Select order_id, orderDate
From dbo.orders With (Index(ix_orders_partitioned))
Where order_id = 300;
 
Select order_id, orderDate
From dbo.orders With (Index(ix_orders_unpartitioned))
Where order_id = 300;

 

With specific query, the partition index is more expensive than the non partitioned index.

image

 

Case 2 with Range

Select order_id, orderDate
From dbo.orders With (Index(ix_orders_partitioned))
where orderdate between '2011-12-27' and '2012-01-11'
 
Select order_id, orderDate
From dbo.orders With (Index(ix_orders_unpartitioned))
where orderdate between '2011-12-27' and '2012-01-11'

Partition index is better than non partition index in this case, because the query is searching for particular date therefore the query optimizer can utilize the partition elimination.

 

image

Check the index fragmentation

SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'dbo.orders'), NULL , NULL, NULL)
where object_id=OBJECT_ID(N'dbo.orders')
GO

image

Rebuild the partition index

ALTER INDEX ix_orders_partitioned
ON dbo.orders
REBUILD Partition = 5;

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates