Sql Server Programming :: Parallel I/O for partitioned table


	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... - Hide quoted text - > 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 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 - Hide quoted text - "Martin Poon [MVP]" wrote: > 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... > > 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 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... - Hide quoted text - > 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 > "Martin Poon [MVP]" wrote: >> 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... >> > 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 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... - Hide quoted text - > 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