Partitioning Deck, Demos, and Recording Live!

Hello Dear Reader!  This week finds me up in Jacksonville at Pragmatic Works HQ.  I'm a little behind in getting the blog up for last week Webinar on Partitioning in SQL Server 2012.  We covered a lot of great things in the webinar and I wanted to recap some of them.  This was the first time I’d given this presentation and over 300 people tuned in to watch!

I’d like to say a quick Thank You to all the people that spent their hard earned time with me.  If you would like to download anything from the presentation Click Here for the Deck, Click Here for the Demo Scripts, and click here to watch the video recording of the presentation.  All of this is also up on my Resource page.

I had a hiccup on the Piecemeal Restore Demo that I did and I wanted to review it.   Unfortunately I was playing around with the script and just to be on the safe side had backed up my data base.  At the header of the script I inserted a restore command.  I got an error running the script and wanted to fit a couple other demos in and skipped over it.  So now we can tackle it.

“So Balls”, you say, “What’s a Piecemeal Restore and WHY would I need to use one?”

Excellent question Dear Reader! The Piecemeal restore was introduced in SQL 2005.  It gave us a sequence of steps we could take in order to recover a portion of a database online at a time starting with the Primary Filegroup.  This allows us to bring critical portions of the database online for quick access.

If you had a very large database with a lot of historical data you wouldn’t want to make the business stay offline in a critical outage while all the historical data is restored.  If you have TB’s of data that could take hours!

A Piecemeal restore gives us the ability to bring a segment of the database online at a time.  A very easy way to demonstrate this is using partitioning.

DEMO TAKE II

Make sure to use 02 Demo_a Set Up demoInternals_Partition.sql to set up our demoInternals_Partition Database in the scripts above.  

First let’s take a look at our table.  We will use sys.partitions and sys.indexes to see how the data is distributed across filegroups.  

This will also let us see the Clustered Index and Non-Clustered Index we created.

SELECT

     OBJECT_NAME(sp.object_id) AS tableName,

     si.name AS indexName,

     sp.partition_number,

     sp.rows

FROM

     sys.partitions sp

     JOIN sys.indexes si

     ON si.object_id=sp.object_id AND si.index_id =sp.index_id

WHERE OBJECT_NAME(sp.object_id) ='myTable1';

You can see from the count we have 18,000 rows in our table. Now that we’ve looked at our data the next thing I need to do is backup my database.  I’m going to perform 3 types of backups.  First a Full backup, secondly I’m going to perform a Log Backup, third we’ll perform a tail of the log backup and leave our database in a restoring state. 

*NOTE* You can use a Piecemeal restore with all recovery models however I’m running in Full recovery for the sake of the demos today.

USE master

go

BACKUP DATABASE demoInternals_Partition TO DISK=N'C:\Backups\demoInternals_Partition2.bak' WITH INIT

GO

BACKUP LOG demoInternals_Partition TO DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH INIT

GO

BACKUP LOG [demoInternals_Partition] TO  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn' WITH  NO_TRUNCATE , INIT, NORECOVERY

GO

Looking at our database I can see that the commands have completed and we are in the Restoring state.  Our database is completely inaccessible, I know I know snapshots…. But that’s not the point so stick with me Dear Reader.  First let’s restore our primary data file.

USE master

GO

RESTORE DATABASE demoInternals_Partition FILEGroup='primary'

     FROM DISK='C:\Backups\demoInternals_Partition2.bak'

WITH PARTIAL, NORECOVERY

GO

RESTORE LOG demoInternals_Partition  FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery

RESTORE LOG [demoInternals_Partition] FROM  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10

GO

If I refresh my SSMS Object Explorer Window it looks like the database is back online.  However, I know better.  The only filegroup online is the Primary Filegroup.  I like to keep this filegroup small with only the metadata that is there when the database is created.  If I try to query the table dbo.mytable1 it should fail.  Let’s do that real quick.  We’ll query one of our DMV’s about our table that we cannot acces, let’s say sys.indexes.  Then we’ll do a very simple query against the database to get record 1.  Remember our 1^st partition had 2000 rows in it.

use demoInternals_Partition

go

select

     object_name(si.object_id)

     ,si.name

     ,si.type_desc

     ,si.name

from

     sys.indexes si

where

     object_name(si.object_id)='mytable1'

select

     *

from

     dbo.myTable1

where

     myid=1

As you can see we got results from our DMV, but we couldn’t even access row 1 in our table.  Now let’s bring FG1 online.

use master

go

RESTORE DATABASE demoInternals_Partition FILEGroup='FG1'

     FROM DISK='C:\Backups\demoInternals_Partition2.bak'

WITH norecovery

GO

RESTORE LOG demoInternals_Partition  FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery

RESTORE LOG [demoInternals_Partition] FROM  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10

GO

Now let’s try our query again.

USE demoInternals_Partition

GO

select

     *

from

     dbo.myTable1

where

     myid=1

Success. We can get all 2000 rows in the FG1 partition. If you want to get the same error as before for FG2, just change the 1 to a 2001.  This is a very flexible process that allows you to assign Business level SLA’s to different segments of your Database.  You do not need to use partitioning to do a piecemeal restore.  You could just use separate FG’s and segment tables by business segment.

Let’s bring online FG2 and FG6, leaving FG3, FG4, and FG5 still offline.

USE master

go

RESTORE DATABASE demoInternals_Partition FILEGroup='FG2'

     FROM DISK='C:\Backups\demoInternals_Partition2.bak'

WITH norecovery

RESTORE DATABASE demoInternals_Partition FILEGroup='FG6'

     FROM DISK='C:\Backups\demoInternals_Partition2.bak'

WITH norecovery

RESTORE LOG demoInternals_Partition  FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery

RESTORE LOG [demoInternals_Partition] FROM  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10

GO

Now’ let’s execute the following queries:

use demoInternals_Partition

go

select * from dbo.mytable1 as FG2 Where FG2.myid=2500

select * from dbo.mytable1 as FG6 Where FG6.myid=12001

go

select * from dbo.mytable1 as FG3 Where FG3.myid=4500

select * from dbo.mytable1 as FG5 Where FG5.myid=8000

The Queries against FG2, and FG 6 Succeed.  The Queries against FG3 and FG5 failed.  Okay now let’s bring all the tables online.

USE master

go

RESTORE DATABASE demoInternals_Partition FILEGroup='FG3'

     FROM DISK='C:\Backups\demoInternals_Partition2.bak'

WITH norecovery

RESTORE DATABASE demoInternals_Partition FILEGroup='FG4'

     FROM DISK='C:\Backups\demoInternals_Partition2.bak'

WITH norecovery

RESTORE DATABASE demoInternals_Partition FILEGroup='FG5'

     FROM DISK='C:\Backups\demoInternals_Partition2.bak'

WITH norecovery

RESTORE LOG demoInternals_Partition  FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery

RESTORE LOG [demoInternals_Partition] FROM  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10

GO

WRAP IT UP

We can now query from start to finish with the entire database online.  This is a pretty simplistic demo.  It is meant just to convey the different architectural options that are available for a Piecemeal restore.  As you can imagine this is something that could be utilized in a DR scenario to meet SLA’s and RTO.

This is a very powerful tool in the arsenal of the DBA.  You want to test this, and make sure that it meets your business needs before implementing it.  Don't forget to get the demo's and scripts from the presentation.  

As always Dear Reader, Thanks for stopping by!

Thanks,

Brad

Partitioning a Heap

http://www.flickr.com/photos/wouter28mm/3151414412/

During a busy day sometimes I don’t have a chance to check Twitter.  I’ve written about how Twitter is a wonderful place to learn, Top 5 Reasons You Should Be On Twitter, and I happened to glance down at just the right moment. 

Grant Fritchey (@GFritchey | Blog) asked a question on #SQLHelp. And I thought I knew the answer, YAY, but it turned out I was wrong.   Grant had asked the question if you needed a primary key in order to Partition a table.  Originally I had replied to Grant that I thought you had to have a Clustered Index in order to partition a table.

Some of the top minds in the SQL field quickly set in to give Grant the answer that he needed.  And like any time when you get something wrong this was a wonderful opportunity to learn.  The exchange went like this (You have to read this from the bottom up):

WOW!  That is a lot of information over a very short space.  Whenever I have used partitioning I’ve always used it on a Primary Key Clustered Index. 

It’s a technology that I’ve used that I’m familiar with, but not one that I’ve played with just to see what could happen.  Amit Banerjee(@banerjeeamit | Blog), Aaron Nelson (@SQLVariant|Blog), Paul White (@SQL_Kiwi|Blog), and Dave Levy (@Dave_Levy|Blog) are some pretty ridiculously smart guys.  If they said it could be done I believed them.  However, that's not enough.  You shouldn't just take it on faith alone.  You should go out there and do it yourself.

So with that Dear Reader I figured I’d work up some code we could both play with that demonstrates what the experts where talking about and lets us prove it out at the same time.

CREATE OUR DEMO_PARTITION DATABASE

Let’s Create our Database

/*

Create our Database

That we will use for the

Demo

*/

USE master;

Go

IF EXISTS(SELECT name FROM sys.databases WHERE Name=N'demo_Partition')

     BEGIN

          DROP Database demo_Partition

     END

    

CREATE DATABASE demo_Partition

GO

Then we will add some Logical File Groups

/*

Add Filegroups

*/

ALTER DATABASE demo_Partition

ADD FILEGROUP FG2

GO

ALTER DATABASE demo_Partition

ADD FILEGROUP FG3

GO

ALTER DATABASE demo_Partition

ADD FILEGROUP FG4

GO

ALTER DATABASE demo_Partition

ADD FILEGROUP FG5

GO

Then we will associate those Logical File Groups with some Physical Data Files

/*

Add Files and

associate to filegroups

*/

ALTER DATABASE demo_Partition

ADD FILE

(

     NAME=data_FG2,

     FILENAME='<Insert directory Path>\demo_Partition_FG2.ndf'

) TO FILEGROUP FG2;

GO

ALTER DATABASE demo_Partition

ADD FILE

(

     NAME=data_FG3,

     FILENAME='<Insert directory Path>\demo_Partition_FG3.ndf'

) TO FILEGROUP FG3;

GO

ALTER DATABASE demo_Partition

ADD FILE

(

     NAME=data_FG4,

     FILENAME='<Insert directory Path>\demo_Partition_FG4.ndf'

) TO FILEGROUP FG4;

GO

ALTER DATABASE demo_Partition

ADD FILE

(

     NAME=data_FG5,

     FILENAME='<Insert directory Path>\demo_Partition_FG5.ndf'

) TO FILEGROUP FG5;

GO

USE demo_Partition

GO

CREATE OUR PARTITION FUNCTION & SCHEMA

Now that we’ve got our Database we need to create our Partition Function and our Partition Schema.  Our partition Function is a range of values going right or left for a particular data type.  In this example we’ll be using an integer data type and we will be doing a left range for values 2000, 4000, and 6000.

This means that anything <=2000 will go in the Filegroup that we associate with this range of the function, <=4000, and >=6000.

So we should end up with 4 partitions ranging from 1 to 2000, 2001 to 4000, 4001 to 6000, and 6000 & up.

/*

Create Partition Function

*/

CREATE PARTITION FUNCTION compDemoPartionFunc(INT)

AS RANGE LEFT FOR VALUES(2000, 4000, 6000)

GO

Now we will create our Partition Schema which we will use to associate our Partition Function to our Logical File Groups

/*

Create Partition Scheme

*/

CREATE PARTITION SCHEME compDemoPartitionPS

AS PARTITION compDemoPartionFunc

TO(fg2, fg3,fg4, fg5);

PARTITION A PRIMARY KEY CLUSTERED INDEX

http://www.flickr.com/photos/21684795@N05/4406836491/

Now that we’ve done all this, let’s start with the trusted standby of partitioning a clustered Primary Key.  First let's create our table, including our primary key and clustered index.  We’ll drive to the basket and slam dunk this.

IF EXISTS(SELECT NAME FROM sys.tables WHERE name=N'myTable1')

BEGIN

     DROP TABLE dbo.myTable1

END

CREATE TABLE myTable1(

     myID INT IDENTITY(1,1),

     productName char(800) DEFAULT 'some product',

     productSKU varCHAR(500) DEFAULT 'Product SKU',

     productDescription varCHAR(max) DEFAULT 'Here is a Generic Production Description',

     Comments TEXT DEFAULT 'here are some genric comments',   

     CONSTRAINT PK_myTable1_myID

     PRIMARY KEY CLUSTERED(myID)

) ON compDemoPartitionPS(myID);  

Now let's populate our data with 10,000 rows.

/*

Let's populate our Primary Key

Clustered Index with some data

*/

DECLARE @i INT

SET @i=0

BEGIN TRAN

WHILE (@i<10000)

     BEGIN

          INSERT INTO myTable1 DEFAULT VALUES;

          SET @i = @i +1

     END

COMMIT TRAN

Now let's take a look at see our breakdown.  We don’t want to just scratch the surface here we want to dig deep and make sure that all of our rows are on the Filegroups that we selected for them.  To do this we’ll be looking at sys.destination_data_spaces and joining sys.partition_schemes as this table holds the pointers for our partition schema.

select

          so.name AS [Table Name]

          ,si.name AS [Index Name]

          ,ds.name AS [Filegroup Name]

          ,dds.data_space_id AS [partition number]

          ,sp.rows

          ,dds.partition_scheme_id

          ,ps.name as [partition scheme]

from

     sys.data_spaces ds

     LEFT join sys.destination_data_spaces dds

     on (ds.data_space_id = dds.data_space_id)

     left join sys.partition_schemes ps

     on (ps.data_space_id = dds.partition_scheme_id)

     LEFT JOIN sys.partitions sp

     ON dds.destination_id=sp.partition_number

     LEFT JOIN sys.objects so

     ON sp.object_id=so.object_id

     LEFT JOIN sys.indexes si

     ON so.object_id=si.object_id AND sp.index_id=si.index_id

WHERE

     so.type='U'

order by

     ds.name

     ,ps.name ASC

As you can tell from above our partitioned table went on the Logical Filegroups we had created earlier.  Because we associated them with physical Filegroups we can be confident that our data is going to the correct location.

PARTITION A CLUSTERED INDEX (NO PRIMARY KEY)

http://www.flickr.com/photos/39083668@N06/4347173049/

 

So now let’s drive the lane.  We'll do the exact same exercise, but this time with a Clustered Index with no primary key.

Start by creating our table.

/*

Let's create a Heap and then

we will create a Clustered Index

With no Primary Key Constraint

*/

IF EXISTS(SELECT NAME FROM sys.tables WHERE name=N'myCLX')

BEGIN

     DROP TABLE dbo.myCLX

END

CREATE TABLE myCLX(

     myID INT IDENTITY(1,1),

     productName char(800) DEFAULT 'some product',

     productSKU varCHAR(500) DEFAULT 'Product SKU',

     productDescription varCHAR(max) DEFAULT 'Here is a Generic Production Description',

     Comments TEXT DEFAULT 'here are some genric comments'    

) ;

Now let’s create our index and associate it with our partition schema.

CREATE CLUSTERED INDEX clx_Index1 ON dbo.myCLX(myID)

ON compDemoPartitionPS(myID);

go

Now let’s populate our table and look at the results.

/*

let's insert some data

into our Clustered Index

*/

DECLARE @i INT

SET @i=0

BEGIN TRAN

WHILE (@i<10000)

     BEGIN

          INSERT INTO dbo.myCLX DEFAULT VALUES;

          SET @i = @i +1

     END

COMMIT TRAN

We will use the same query and set of DMV’s that we used before. 

 We see that we didn’t need a primary key to achieve the exact same results as above.

PARTITION A HEAP (NO CLUSTERED INDEX)

http://www.flickr.com/photos/lscan/2225285352/

Now we’ve hit 2 for 2, let’s see if we can go for three.  Same exercise this time we are using a Heap.   Remember a Heap is simply a table with no Clustered Index on it. We’ll start by creating our Heap.

IF EXISTS(SELECT NAME FROM sys.tables WHERE name=N'myHeapNCI')

BEGIN

     DROP TABLE dbo.myHeapNCI

END

CREATE TABLE dbo.myHeapNCI(

     myID INT IDENTITY(1,1),

     productName char(800) DEFAULT 'some product',

     productSKU varCHAR(500) DEFAULT 'Product SKU',

     productDescription varCHAR(max) DEFAULT 'Here is a Generic Production Description',

     Comments TEXT DEFAULT 'here are some genric comments'    

) ON compDemoPartitionPS(myID);  

Then we will populate it with some data.

/*

let's insert some data

into our Heap

*/

DECLARE @i INT

SET @i=0

BEGIN TRAN

WHILE (@i<10000)

     BEGIN

          INSERT INTO dbo.myHeapNCI DEFAULT VALUES;

          SET @i = @i +1

     END

COMMIT TRAN

Now let’s run the same query as before to see how our data is spread out.

Now this is interesting.  Look at the null in the column for the Index name.  It makes sense we do not have an index on a Heap.  However our Heap is still partitioned.  The home team wins!  And the crowd goes wild!

WRAP UP

http://www.flickr.com/photos/joao/2198486114/

So once again Twitter had found a way to get information from the best and the brightest in the SQL Community.  If you would have asked me before all this I would have told you, that you had to have a Clustered Index on a table in order to partition it.  I’m not sure where I even got that bit of information, but when I picked it up along the way it was wrong.  

I’m glad during a very busy day that I looked at Twitter just in time to learn something new. 

But with new knowledge comes new questions, why would you want to create a partitioned heap, why would you create a partitioned clustered index without a primary key? 

The heap question I could take a stab at, but I wouldn’t want to do it without proofing out a couple theories.  So there we go Dear Reader more questions that will hopefully lead to more learning.

Thanks Again,

Brad