SQL Partitions, query analyzer and performance

I'm partitioning off a table. From what I've read, SQL will use the partitions as needed based on the query predicates.

For example, if I have a p开发者_如何学JAVAartition for records between 2005 - 2009 and another for records 2010 - current and I select for all records = 2010, SQL will ignore the first partition because of the partition function. If I search for record between 2008-2010 SQL will use both partitions but do so in a parallel manner. Is it correct to assume that if I query for ALL records with no predicate, that SQL will (to the best of its abilities) use all partitions in a parallel manner?

When do table partitions cause performance bottle necks?

All your points are correct. Parallelism is not directly related to partitioning, although it is more likely to break the job among the partition alignment.

For the last question, there are no real cases that I know of when partitioning will cause performance bottle necks. If parallelism is giving you performance problems then the issue is with parallelism, not partitioning - I restate that they are not directly related. The usual issues with incorrectly parallelised queries will occur equally with or without partitioning and the usual methods to resolve it applies.

Related reading:

• http://blogs.msdn.com/b/sqlcat/archive/2005/11/30/498415.aspx
• http://channel9.msdn.com/Blogs/Joe+Longtin/Demo-SQL-Server-2008-Partitioned-Table-Parallelism
• http://msdn.microsoft.com/en-us/library/ms188706.aspx
• http://msdn.microsoft.com/en-us/library/ms191174.aspx
• http://msdn.microsoft.com/en-us/library/ms345599.aspx
• http://technet.microsoft.com/en-us/library/dd578580(SQL.100).aspx