MS SQL table partitioning

Dear all,

Right now studying how to make use of the parallel executing by table partitioning, any suggested link on who this works and any step by step guild on how to create it. convert the existing table to partition one ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
Hi marrowyung,
The following are some high level steps on how to convert an existing table to a partitioned table:

1. Make a good backup of your database

2. Do some baselining before partitioning - e.g. run some typical queries against the relevant table and record execution times etc.

3. Decide on the column that you wish to partition by

4. Create a partition function for the relevant values for the relevant column - CREATE PARTITION FUNCTION ....

5. Create a partition scheme which references the partion function created in step 4 above - CREATE PARTITION SCHEME ...

6. If the table already has a clustered index, then drop this clustered index

7. Create a clustered index on the table on the partition column referencing the partition scheme

   CREATE CLUSTERED INDEX IX_YOUR_TABLE_partitioncol ON dbo.YOUR_TABLE (partitioncol)
   ON yourPartitionScheme(partitioncol)


Note: you can use the following query to get the internal details of your partitioning:
SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%YOUR_TABLE%'
0
marrowyungSenior Technical architecture (Data)Author Commented:
any much formal step so that I can easily learn the detail ? then command ?

" Decide on the column that you wish to partition by"

e.g. , what kind of logic make me select that column ? etc.
0
Barry CunneyCommented:
Hi marrowyung,
A typical column that you may want to partition by is a date column.
So for example in a data warehouse you may have a fact table which contains all sales to customers and this table may have a Sale_Date column to record the date the sale was made.
Then you may decide that you wan to partition this sale fact table and that you want each  months sales in separate partitions - so a partition with June 2015 sales, another partition with July 2015 sales etc. so you would use the Sale_Dale column as the partition column and in the partition function you would specify boundary values which dictate each partition/month similar to the following

CREATE PARTITION FUNCTION myMonthPartitionDateRangePF (datetime)
AS RANGE RIGHT FOR VALUES ('20150601', '20150701','20150801',............)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mironCommented:
"studying how to make use of the parallel executing by table partitioning"

Makes for an interesting prelude... :)
I wonder what you have in mind regarding parallelization.

My understanding there are various patterns which allow partition table and execute tasks in parallel specific for a give usage. However, most general case is an application, be that background and / or nightly job or even on - line application working with rows of a single table.

Since parallel execution is optimization technique, it kind of tries to cling to a specific usage, approach, or goal regarding what to speed up.

If you could please share a bit more, perhaps a less general but a well fitting suggestion can be created.

Hope it helps :)
0
marrowyungSenior Technical architecture (Data)Author Commented:
"My understanding there are various patterns which allow partition table and execute tasks in parallel specific for a give usage. However, most general case is an application, be that background and / or nightly job or even on - line application working with rows of a single table."

totally don't know what you are talking about !!  lovely ! :):)

"Since parallel execution is optimization technique, it kind of tries to cling to a specific usage, approach, or goal regarding what to speed up.
"
I am reading B.S. !!

"If you could please share a bit more, perhaps a less general but a well fitting suggestion can be created.

 Hope it helps :) "

I see nothing useful here!

Barry you are good but I really can't understand what the hello is the last post :)
0
marrowyungSenior Technical architecture (Data)Author Commented:
Barry is good !
0
marrowyungSenior Technical architecture (Data)Author Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.