Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

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 ?
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

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%'
Avatar of marrowyung
marrowyung

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of miron
"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 :)
"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 :)
Barry is good !