Partitioning in SQL Server

Question

Partitioning in SQL Server

RaviShankar1234

SSCommitted

Points: 1647
More actions
June 13, 2011 at 2:39 pm

#252818

Hi
what would be best thing to do when you are partioning a table.
a. create the Index first then do Partitioning
b. partition the table First and then create Indexes
Which of these would be the best thing and has performance benefits.
Thanks,
Ravi

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 1

Partition the table first then Indexes,performance will be increase if table have heavy DML operation and huge data in the table but dont forget to define the Lock Escalation to AUTO,when you partition the table and Lock Escalation to AUTO then SQL Server lock the particular Partition not the whole table then other DML can perform on other partitions of a table at the same time

My Blog
www.aureus-salah.com

RaviShankar1234 SSCommitted Points: 1647 More actions · Answer 2

Thanks, Mr.Hasan, another thing that hover in my mind is, I have big table that has indexes created earler and if I were to create the partition for this table then the first thing I would be doing is dropping the indexes and partition the table or leave the indexes as it is and partition the table what would you suggest.

Thanks,

Ravi

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

I will suggest you to recreate those indexes after applied the partitioning

My Blog
www.aureus-salah.com