Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
john.burns
Posting Yak Master
100 Posts |
 Posted - 2008-02-27 : 08:56:24
|
| 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 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-27 : 09:23:48
|
| How many drives you spread data across? Is there clustered index on Partition key and non clustered index on where clause or join.? |
 |
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2008-02-27 : 09:25:57
|
| -9 drives-no.. clustered index is on three cols... the first of which is the partition key-yes non clustered used in where clause of join |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-27 : 09:52:21
|
| Sometime if you put three columns as clustered, Performance might go down. Does all tables have clustered and non clustered index as well?you have to test it. |
|
|
|
|
|
|
Partition table