SQL Server 2008 Partitioned Table and Parallelism
Detailed Article for "Partitioned Table and Parallelism" is available at :
http://sqlserver-training.com/sql-ser...
Follow us at more Updates at
-----------------------------------------
Website : http://sqlserver-training.com/
Twitter : / dbatag
Facebook : / dbatag
Linked In : / dbatag
================================
SQL Server 2008 has removed the bottleneck of single-thread per partition over partitioned tables, which was found in SQL Server 2005. The previous bottleneck can cause performance problems under certain circumstances:
On systems with many CPUs, if the table schema has less partitions than there are CPUs, then not all the CPUs will be used to process the query. Some examples:
On a 32-way box, a query over a 12-partition table (e.g. a sales table partitioned by month) will only use 12 threads (one on each of 12 CPUs). This means 20 CPUs are potentially idle.
On a 4-way box, a query over a 12-partition table only accesses a single partition so will only use one thread (on one CPU). This means 3 CPUs are potentially idle.
On tables that have skewed data such that one partition is much larger than another, the length of time the query takes to complete will be bounded by the single thread processing the largest partition.
As part of the set of improvements in SQL Server 2008 for data warehousing there will be an option to change the threading behavior for queries over partitioned tables. The new, alternative model is that all available threads process part of each partition and then move into the next partition. This allows all available CPUs to take part in processing the query, which should lead to a drop in the query completion time.
The Following video shows, SQL Server 2008 -- Partitioned Table & Parallelism feature and shows graphically, how SQL Server 2008 has improved performance of Partitioned Tables.
Visit the following link for more details and SQL Server Tutorials
http://sqlserver-training.com/sql-ser...
