locked
Table partition RRS feed

  • Question

  • I have a table with a few indexing for optimisation. I create another table which is identical same but added with table partition.

    I use the execution plan and querying 1 records at both table.

    The query cost same and did not see any difirence within both tables.

    Please advise what else I can done in order to optimise the peformance of this table.

    thanks.

    Wednesday, April 15, 2015 11:06 AM

Answers

All replies

  • What is your expectation here? Why do you want table partitioning? Is it a huge table with data in different ranges?

    Read this document - http://www.brentozar.com/archive/2012/03/how-decide-if-should-use-table-partitioning/


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, April 15, 2015 11:12 AM
  • Table partitioning may improve performance if you are able to work within the limits of how the partition works.

    There are two factors that affect if partition elimination can occur and how well it will perform:
    Partition Key - Partitioning can only occur on a single column and your query must include that column. For example, if your table is partitioned on date and your query uses that date column, then partition elimination should occur. However, if you don't include the partition key within the query predicate, the engine can not perform elimination.
    Granularity - If your partitions are to big, you won't gain any benefit from elimination because it will still pull back more data than it needs to. However, make it to small and it becomes difficult to manage.

      Click Here



    • Edited by SequelMate Wednesday, April 15, 2015 11:20 AM
    Wednesday, April 15, 2015 11:19 AM
  • I create the table partition using the partition key as accountID 

    when I query I also using the partiion key to query .

    each partition size is small.

    eg : select *  from dbo.Customer_Test where accountID  = 1008701

    I create table partition because I want to improve the performance.

    Existing table  good but I want to make the performance become best.

    Wednesday, April 15, 2015 11:38 AM
  • Table partitioning is not used to improve the performance of normal small tables.

    It is particularly usefull in big tables which have range of values . It is also particularly good where there is a huge amount of incoming data and outgoing data.

    Read the link I shared.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, April 15, 2015 12:46 PM
  • I use the execution plan and querying 1 records at both table.

    You used a query hint to use the same execution plan? Then of course you get the same.

    Is the column you queried on part of the partitioning function?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, April 15, 2015 2:20 PM