partitioning a huge table

  • Hi folks

    I would like to get your views about  partitioning  a huge table in sql server .Around on size of  100 gb.Beofre i present this idea to upper mnmgt i would like to have your inputs .I am of view that since there is only 1 dedicated disk for data files (mdf/ndf) there is not point in partitioning a table .If i had more then 1 disk available for  data files then partitioning the high table would had made sense. I am thinking right  ?????  or number of disk/storage doesnt plays part in deciding partitioning



  • What is the purpose for partitioning the data?  If the goal is to improve performance - then partitioning is not the way to go, rather - make sure you have appropriate indexes to support the queries being run.

    If the goal is to make it easier to archive/purge data - then partitioning *might* be an option.  Partitioning does not require separate files and filegroups - you can setup a partition in the same file/filegroup, and that has some added benefits if used.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • If you can't guarantee, 100%, that most (read that as 98% or more) of the queries will be able to filter on the partitioning column(s), then whatever you do, don't partition your data. You'll end up scanning across partitions and performance will grow horribly poor. Partitions are all about data management, not performance.

    And yeah, I'm largely just reframing what @Jeffrey already said here, but reinforcement of this message is important.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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