Archive

Archive for the ‘Partitioning’ Category

SQL Server – sys.dm_db_persisted_sku_features tells you about edition-specific features

August 22, 2011 Leave a comment

sys.dm_db_persisted_sku_features lists all features which are utilized by the database. Features specific to Enterprise/Developer edition are:

- Compression,
- Partitioning,
- TDE and CDC

These features are available only on Enterprise/Developer editions of SQL Server. You cannot attach or restore databases utilizing these features to a "lower" edition, such as Standard or Express edition.

For example, You can check features utilized by your database using:

USE SqlAndMe

 

SELECT      *

FROM        sys.dm_db_persisted_sku_features

Result Set:

feature_name  feature_id

Compression   100

Partitioning  200

 

(2 row(s) affected)

 

Since this database uses partition and compression it cannot be attached to an instance of "lower" edition,

If you try to attach database to a "lower" edition, you will run into below error:

Msg 909, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started in this edition of SQL Server because part or all of object 'PageCompressionTest' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

 

Msg 905, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started in this edition of SQL Server because it contains a partition function 'PartFunc01'. Only Enterprise edition of SQL Server supports partitioning.

 

Msg 933, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

These error messages tell you exactly why the database cannot be started. 1st error is due to compression feature, and 2nd is for partitioning feature. Notice the last message, it says, "Database cannot be started.", the database does attach to server, but it cannot be started.

In case of restore also, you will receive same error messages, the database will be restored but, it cannot be started:

20 percent processed.

40 percent processed.

60 percent processed.

80 percent processed.

100 percent processed.

Processed 29976 pages for database 'SqlAndMe', file 'SqlAndMe' on file 1.

Processed 3 pages for database 'SqlAndMe', file 'SqlAndMe_log' on file 1.

 

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'SqlAndMe'.

 

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

 

Msg 909, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started in this edition of SQL Server because part or all of object 'PageCompressionTest' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

 

Msg 905, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started in this edition of SQL Server because it contains a partition function 'PartFunc01'. Only Enterprise edition of SQL Server supports partitioning.

 

Msg 933, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

In order to avoid this issue, it must be planned ahead. Before using any edition-specific features ask – "Will the database need to be moved between editions?"

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – How to partition an existing table…

August 9, 2011 5 comments

It’s fairly simple, create a CLUSTERED INDEX on the table. While creating a clustered index you need to specify the partition scheme to be used in ON clause of CREATE INDEX statement.

 

We still need to create a partition function and a partition scheme, I have posted earlier about how to create the same here. Step 1 – Creating a partition function and Step 2 – Creating a partition scheme are same, only Step 3 changes as we are partitioning an existing table.

 

For example, I have a table dbo.Table_Orders which is not partitioned:

SELECT      OrderID, OrderDate, Quantity, Amount

FROM        dbo.Table_Orders

 

SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

Result Set:

OrderID     OrderDate  Quantity    Amount

———– ———- ———– ———————

1           2011-07-22 34          3200.00

2           2010-06-22 98          9800.00

3           2009-05-22 65          6500.00

4           2011-07-24 73          7300.00

 

(4 row(s) affected)

 

partition_id         object_id   partition_number rows

——————– ———– —————- ——————–

72057594039631872    5575058     1                4

 

(1 row(s) affected)

 

Now, if we want to partition this table, we need to create a clustered index ON a partition scheme. I have a partition scheme in my database ordersPartScheme. So the clustered index should be created using:

CREATE CLUSTERED INDEX [Clust_Orders] ON [dbo].[Table_Orders]

(

      [OrderID] ASC

) ON ordersPartScheme(OrderDate)

 

– Check for new partitions

SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

Result Set:

partition_id         object_id   partition_number rows

——————– ———– —————- ——————–

72057594039762944    5575058     1                1

72057594039828480    5575058     2                3

 

(2 row(s) affected)

From the result set, we can see that new partition is added to the table, and required rows are also moved to new partition.

 

However, if you already have a clustered index created on a table, then you can use DROP_EXISTING clause to re-create the clustered index:

CREATE CLUSTERED INDEX [Clust_Orders] ON [dbo].[Table_Orders]

(

      [OrderID] ASC

) WITH (DROP_EXISTING = ON)

ON ordersPartScheme(OrderDate)

Using DROP_EXISTING = ON specifies that the index is dropped and rebuilt.

For more information, check Horizontally partitioning a SQL Server Database Table.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Categories: Partitioning, SQLServer

SQL Server – Horizontally partitioning a SQL Server Database Table

July 22, 2011 6 comments

Partitioning was introduced in SQL Server 2005, it allows you to different database files, which can be located on different disks to improve performance. In earlier versions this was achieved by creating separate tables on different file groups of database, and then creating a view by UNIONing these tables to make it look like a single table.

First, let us create a Test database for partitioning with more than one file groups. I have created [PartitionDB] with two file groups – [PRIMARY] and [FileGroup2], both of these groups contain a data file:

CREATE DATABASE [PartitionDB] ON

PRIMARY

      (     NAME = N'PartitionDB',

            FILENAME = N'C:\PartitionDB\PartitionDB.mdf' ,

            SIZE = 3072KB , FILEGROWTH = 1024KB

      ),

FILEGROUP [FileGroup2]

      (     NAME = N'PartitionDB_2',

            FILENAME = N'C:\PartitionDB\PartitionDB_2.ndf' ,

            SIZE = 3072KB , FILEGROWTH = 1024KB

      )

LOG ON

      (     NAME = N'PartitionDB_log',

            FILENAME = N'C:\PartitionDB\PartitionDB_log.ldf' ,

            SIZE = 9216KB , FILEGROWTH = 10%

      )

image

Note: I have created both files on the same disk here, it is recommended to create files on separate disks for optimal performance.

Now we have a database to work with, let’s start with table partitioning, Partitioning a Table involves three steps:

 

Step 1: Creating a Partition Function – This is the first step in creating a partitioned table, a partition function tells SQL Server “how” to split the data. A partition function is not related to any particular table, it is a “technique” that SQL Server will use to split the data rows.

A partition function defines the range of values for a partition. For example, customer order data can be partitioned according to order date. I.e. data for current year will be in [PRIMARY] file group, while older data will be in [FileGroup2] file group. To achieve this we can create partition function as:

– Step 1. Creating a Partition Function

 

CREATE PARTITION FUNCTION ordersPartFunc (DATE)

AS RANGE LEFT

FOR VALUES ('2010-01-01')

The VALUES(<list of values>) define a list of boundary values for each partition, By specifying VALUES (2010) we have created two partitions.

1. For Values < 2010 and,
2. For Values > 2010

RANGE defines to which partition the boundary values will go. It can either be specified as RANGE LEFT or RANGE RIGHT, depending on this the placement of boundary values is decided. Here, we have used RANGE LEFT that means ’2010′ will go to LEFT partition, so the partitions will contain below data:

1. Values <= 2010 and
2. Values > 2010

 

Step 2: Creating a Partition Scheme – Once the partition function is created, SQL Server knows “how” to split data, but it does not know where to put this partitioned data, this is defined by partition scheme. Partition scheme need to be linked to a partition function, to specify where each partition will be stored. We can create a partition scheme linked to ordersPartFunc as:

– Step 2. Creating a Partition Scheme

 

CREATE PARTITION SCHEME ordersPartScheme

AS PARTITION ordersPartFunc

TO ([FileGroup2], [PRIMARY])

 

Note: A Partition scheme can only be created for file groups, and not individual data files.

Each file group defined in TO(<list of file groups>) corresponds to VALUES(<list of values>) in partition function. This scheme defines that:

1. [FileGroup2] will contain data for Values <= 2010 and,
2. [PRIMARY] will contain data for Values > 2010

We have now created the partition function and partition scheme, but we haven’t linked these to any tables yet. This can be done while creating the Table. A partition scheme can be applied to more than one table.

 

Step 3: Creating a Partitioned Table – Once he partition scheme is defined, a table can be created using the partition scheme. It is done by specifying the partition scheme in the “ON” clause while creating the table. Only partition scheme need to be specified, you don not need to specify a partition function as this is already defined in partition scheme:

– © 2011 – Vishal (http://SqlAndMe.com)

 

– Step 3. Creating a Partitioned Table

 

CREATE TABLE Table_Orders

(

      OrderID     INT,

      Name        VARCHAR(20),

      OrderDate   DATE

)

ON ordersPartScheme (OrderDate)

That’s all folks, data inserted to ‘Table_Orders’ will be partitioned according to ordersPartFunc, and will be stored according to ordersPartScheme.

Let’s insert some data to check if the partitions are created successfully.

INSERT INTO Table_Orders (OrderID, Name, OrderDate)

VALUES (1, 'Vishal', '2011-07-22')

– Will go to Partition 2 -> [PRIMARY] File Group

 

INSERT INTO Table_Orders (OrderID, Name, OrderDate)

VALUES (2, 'Vishal', '2010-06-22')

– Will go to Partition 1 -> [FileGroup2] File Group

 

INSERT INTO Table_Orders (OrderID, Name, OrderDate)

VALUES (3, 'Vishal', '2009-05-22')

– Will go to Partition1 -> [FileGroup2] File Group

 

SELECT      OrderID, Name, OrderDate

FROM        Table_Orders

Result Set:

OrderID     Name                 OrderDate

———– ——————– ———-

3           Vishal               2009-05-22

1           Vishal               2011-07-22

2           Vishal               2010-06-22

 

(3 row(s) affected)

You can use sys.partitions catalog view to check number of partitions and rows for a table as below:

SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

Result Set:

partition_id         object_id   partition_number rows

——————– ———– —————- ——————–

72057594038910976    5575058     1                1

72057594038976512    5575058     2                2

 

(2 row(s) affected)

sys.partitions can tell how many rows are present in each partition, you can use $PARTITION function to identify a partition for a row. $PARTITION can be used $PARTITION.partition_function_name(expression):

SELECT      OrderID, Name, OrderDate,

            $PARTITION.ordersPartFunc(OrderDate)

            AS 'Partition'

FROM  Table_Orders

Result Set:

OrderID     Name                 OrderDate  Partition

———– ——————– ———- ———–

3           Vishal               2009-05-22 1

1           Vishal               2011-07-22 2

2           Vishal               2010-06-22 2

 

(3 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Follow

Get every new post delivered to your Inbox.

Join 263 other followers