SQL Server Performance

Partitioned table question

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by hooperdba, Feb 27, 2008.

  1. hooperdba New Member

    All,
    I have a partitioned table (1.7 billion rows) that is across 97 partions. The table is partitioned on datetime field.
    When I query only this table with datetime field in where clause I see that cleary it outperforms the unpartitioned table.
    When this table is joined to 3 tables it performs no better than
    the non-partitioned table... it seems that the query does a partition scan.
    The part. table has a 3 part composite clustered index and
    a NC index on the partion field value.
    Would appreciate any feedback.
    thanks
  2. ndinakar Member

    [quote user="hooperdba"]When this table is joined to 3 tables it performs no better than
    the non-partitioned table... it seems that the query does a partition scan.
    [/quote]
    I think thats the major drawback with partitioning. When you run queries against an individual partition you get the best benefit out of it but if you go across parititions you lose the benefit. Also I have read that in 2008 the partitioning has been improved. In 2005, the scan/seek over multiple partitions is in serial order, where as in 2008 multiple procs go after multiple partitions and you get your data quickly.

Share This Page