Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Parallel I/O for partitioned table


Hi,
Imagine that I have a partitioned table, half of rows on one disk and the
other half on other disk.
when I query like this:

SELECT * FROM MyTable

Can I expect to get the result faster comparing with the situation that
whole of table resides on one disk? Will these two partitions be scanned in
parallel?
Thanks in advance,
Leila

Whether a parallel plan can be used on a partitioned table depends on the
number of CPUs, query cost, available memory, current workload, and the
sp_configure'd value of Maximum Degree of Parallelism (MAXDOP).

===== For more information =====
~ Partitioned tables, parallelism & performance considerations
http://blogs.msdn.com/sqlcat/archive/2005/11/30/498415.aspx

--
Martin Poon | Microsoft MVP (SQL Server)
http://msmvps.com/blogs/martinpoon
--

"Leila" <Lei@hotpop.com> wrote in message

news:uA0kcY7pHHA.3512@TK2MSFTNGP06.phx.gbl...

Just to rephrase Martin's reply: If the optimizer chooses a parallel
query plan you have a good chance of parallel I/O, but no guarantee. If
your server or query is configured to not use parallellism (or is not an
SMP system), or if the optimizer simply does not choose the parallel
query plan (when it thinks it is not worth it), then the standard
sequential query plan will be used. In that case there will definitely
not be parallel I/O.

Gert-Jan

Thanks everybody!
Assuming that I have more than one processor and SQL Server configuration
allows parallelism, will there be difference between a partitioned table
(like mine) and non-partitioned situation?
My focus is on experimenting the advantage of partitioned table vs.
non-partitioned table.

"Gert-Jan Strik" <s@toomuchspamalready.nl> wrote in message

news:4666F6DD.11919E0D@toomuchspamalready.nl...

Leila wrote:

> Thanks everybody!
> Assuming that I have more than one processor and SQL Server configuration
> allows parallelism, will there be difference between a partitioned table
> (like mine) and non-partitioned situation?

If you have a partitioned table with 2 partitiones where each partition
is on a separate disk and you compare it with a non-partitioned table
that is on one disk, then, if the parallel plan is used, there will be
parallel I/O, and the query could be up to 2 times faster.

However, if you compare it to a non-partitioned table that is on two
disks (RAID0), then it would be equally fast for the parallel plan, and
slower for the non-parallel plan. In other words: you would have to tune
your system very intensely (and often) to get better performance than
the same hardware in a striped configuration.

> My focus is on experimenting the advantage of partitioned table vs.
> non-partitioned table.

Partitioning was not primarily created as a performance enhancement. It
was created to make the table more manageble. You can backup (and
restore) per partition, easily add tables and remove partitions, etc.

Gert-Jan

-----------------------------------------------Reply-----------------------------------------------

I would have thought one of a partitioned table's primary benefits would be
enhancing performance.  Take 10M rows of 'old' data and have them in an
'archive' partition and the last month's 10K rows in the 'current' partition
and now queries against this table hitting only the last month's data will
have far fewer I/Os (whether index seeks due to less index depth or full
scans for non-index hits) than hitting the entire dataset.

--
TheSQLGuru
President
Indicium Resources, Inc.

"Gert-Jan Strik" <s@toomuchspamalready.nl> wrote in message

news:466723BD.C6A5FCCF@toomuchspamalready.nl...

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc