Friday, July 19, 2013

2 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;

2 comments:

  1. Longines watches best replica watcheshave been a landmark of the industry since their brand began in 1832 in Saint-Imier. Today,replica longines the brand offers a wide variety of watches for men and women. Watches manufactured by Longines have been utilized throughout history for everything from exploration ventures to the expression of elegance. In addition to being famous for their ties to the racing and equestrian worlds, Longines watches are globally recognized for their timeless chic sophistication. we have a variety of Longines watches for sale, all new, beautiful, and guaranteed to be genuine. Even better, when shopping our Longines watches online, you know you are getting the best value. Browse our selection to find the Internet’s best Longines watch price.

    ReplyDelete
  2. Découvrez les lunettes Discounted ray bans sunglasses : qualité unique, protection et style ! Achetez des verres Premium en ligne et profitez de la livraison gratuite sur vos commandes. Livraison gratuite.

    ReplyDelete

 

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