question about performance of partition tables

1. When I partition a table ( 90 million rows ) into
five partitions. (create view as select * from table1
union select * from table 2 union ...)
I get a 2 x times query time increase, but by the time
#partitions = 10, the query time is same as the large
table. when #partitions = 15 query time > large table.
Ideally I would want to partition it into 50 states.
Why is it that parallel query execution does not speedup
the process.
The indexes are virtually the same ( most cases covering
non-clustered indexes.)
Is it because of merging the results and sorting them
( because of the group by/order by clauses )? however
the query plan says those are 0%.
You can ask me any question about the query statement,
table format and query plan execution.
I thought about this for a week and don't have an answer.There will always come a point where the resources will be overwhelmed byt
he requests. The more queries you attempt to do simultaneously the less
chances are they will each perform as well as running by them selves. At
some point the cpu or disk queues will start playing a factor. You also
have more overhead when trying to put all the results together and have more
chance of tempdb being a factor.
Andrew J. Kelly
SQL Server MVP
"Ramesh" <anonymous@.discussions.microsoft.com> wrote in message
news:119d201c3f56c$fba82640$a001280a@.phx
.gbl...
> 1. When I partition a table ( 90 million rows ) into
> five partitions. (create view as select * from table1
> union select * from table 2 union ...)
> I get a 2 x times query time increase, but by the time
> #partitions = 10, the query time is same as the large
> table. when #partitions = 15 query time > large table.
> Ideally I would want to partition it into 50 states.
> Why is it that parallel query execution does not speedup
> the process.
> The indexes are virtually the same ( most cases covering
> non-clustered indexes.)
> Is it because of merging the results and sorting them
> ( because of the group by/order by clauses )? however
> the query plan says those are 0%.
> You can ask me any question about the query statement,
> table format and query plan execution.
> I thought about this for a week and don't have an answer.|||cpu utilization seems low : < 40 %
disk I/O queues < 15 on RAID-5 containing tables
(6 disks) and < 3 on RAID-10 containing indexes
(6 disks)
tempdb and log on seperate RAID-10 ( disk queues < 2)
(4 disks)
It is true that queries which only use indexes (covering)
work well upto 10 partitions
queries which would need to use leaf keys and then use
clustering keys work well upto 5 partitions.
Is there something I should know on creating the
clustered/non-clustered indexes different on the
partitioned tables?
Is there some material I can read on?
Is the Windows 2000 server be part of the bottleneck?
Is there any specific parameters I should target and
resolve. ( I am aiming for 60 partitions : each with
1-2 million rows each to reduce skewness). I thought this
would speed up the queries 2 orders of magnitude. Sadly
this does not work the way I calculated.

>--Original Message--
>There will always come a point where the resources will
be overwhelmed byt
>he requests. The more queries you attempt to do
simultaneously the less
>chances are they will each perform as well as running by
them selves. At
>some point the cpu or disk queues will start playing a
factor. You also
>have more overhead when trying to put all the results
together and have more
>chance of tempdb being a factor.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
message
> news:119d201c3f56c$fba82640$a001280a@.phx
.gbl...
time
speedup
covering
answer.
>
>.
>|||Disk queues of 15 or so on a 6 disk Raid 5 are still high enough to warrant
paying attention to them. That means you are in fact waiting on disk I/O.
You might get better results from combining all 12 disks from the indexes
and data into one Raid 10. As for the partitioning that is tough to say.
Most partitioning requires a lot of testing under your specific conditions
to determine which is best for you. Are you sure you need to partition that
table at all? 50 partitions is a lot and you only have 90 million rows.
What are the typical queries like against this table? Maybe just adjusting
the clustered index will do.
Andrew J. Kelly
SQL Server MVP
"Ramesh Krishnan" <anonymous@.discussions.microsoft.com> wrote in message
news:11d6201c3f62b$ea334100$a301280a@.phx
.gbl...
> cpu utilization seems low : < 40 %
> disk I/O queues < 15 on RAID-5 containing tables
> (6 disks) and < 3 on RAID-10 containing indexes
> (6 disks)
> tempdb and log on seperate RAID-10 ( disk queues < 2)
> (4 disks)
> It is true that queries which only use indexes (covering)
> work well upto 10 partitions
> queries which would need to use leaf keys and then use
> clustering keys work well upto 5 partitions.
> Is there something I should know on creating the
> clustered/non-clustered indexes different on the
> partitioned tables?
> Is there some material I can read on?
> Is the Windows 2000 server be part of the bottleneck?
> Is there any specific parameters I should target and
> resolve. ( I am aiming for 60 partitions : each with
> 1-2 million rows each to reduce skewness). I thought this
> would speed up the queries 2 orders of magnitude. Sadly
> this does not work the way I calculated.
>
> be overwhelmed byt
> simultaneously the less
> them selves. At
> factor. You also
> together and have more
> message
> time
> speedup
> covering
> answer.