Post reply

Query on converting the non Partitiontable to Partion table

  • February 28, 2008 at 11:27 pm

    #783610

    Wht can't you do that?

    What is the error you are getting?

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • February 28, 2008 at 11:42 pm

    #783615

    I partitioned the table based on your presentation. It helps a lot about partitioning in 2005.

    Thanks for your presentation and sample codes.

    Few more queries on that

    1.

    I partitioned the table based on the column (ID) datatype is int and clustered primary key. after partitioned the table the performance of the below is slow comparing the non partition table.

    Use ArchiveSyDB - - partition table

    Select * from tablea where field1 = 'S07288_0006_010' - Partition table Query cost - 76

    use ArchiveManagementSystemDB - - Non partition table

    Select * from table a where field1 = 'S07288_0006_010' - Non partition table Query Cost = 24

    The performance is really good apart from the Field1 even though I created the Field1 as non_clustered index

    2. In production area - should i convert the huge tables as partition table instead of keep non partition table? is it cause any problem, if we convert the table from non-partition to partition table.

    thanks & regards

    Saravanakumar.R

  • February 29, 2008 at 12:26 am

    #783625

    Hi,

    1. The actual benefit of the partitioned table will be achieved only when you place the partitions in different file groups and each file group in different disks.

    SQL Server creates a Primary key root for each partition. Whe you use them in multiple disks and SQL server can process them in parallel. Otherwise the query will take additional cost, where it needs to search all partitions and then merge them into one. As the tables are partitions, each partition will require atleast one IO operation, and merge will take some more (either IO operation or memory operations)

    2. Actually in SQL Server 2005, there is nothing called non-partitioned table. All tables are "partitioned" into a single partition. But the do not have partition function and partition scheme attached to it. All the queries will work in the same way irrespective of the fact whether you have single or multiple partitions.

    On the other side, if you dont take much usage of the hisk susbsystems partitions do not going to help you.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

Viewing 3 posts - 16 through 17 (of 17 total)

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